Expression Syntax

A guide for anyone typing an expression into Simplebim. No programming needed — if you have written a formula in Excel, this will feel familiar. The differences from Excel are collected in If you know Excel, and they are worth reading before you start.

Contents

• What an expression does
• Referring to a property
• Writing values
• Separating arguments — always use ;
• Operators
• Functions
• How each property type behaves
• Empty and missing values
• Text comparison is case-sensitive
• Recipes
• If you know Excel
• Things that do not work
• Troubleshooting

What an expression does

An expression is evaluated once for every object. Each object gets its own answer, because the properties in the expression are read from that object.

There are two ways an expression is used, and they expect different kinds of answer:

When an expression is used to select objects, only the objects whose answer is true are picked. An answer of 1, or of a non-empty text, does not count — the answer has to be genuinely true/false:

[Height] > 3000                selects the tall ones
[Load Bearing]                 selects the load bearing ones — a yes/no property is already true/false
[Name]                         selects nothing at all — a name is text, not true/false
[Name] = "Basic Wall"          this is what you meant

The last pair is the common mistake: to select on a text property you have to compare it with something.

Referring to a property

Put the property name in square brackets:

[Name]
[Load Bearing]
[Gross Floor Area]

A few conveniences:

• Capitalisation of the property name does not matter. [name], [Name] and [NAME] all find the same property.
• Extra spaces just inside the brackets are ignored. [ Name ] is the same as [Name].
• Spaces inside the name are part of the name, so [Load Bearing] needs its space.

Asking for a different unit

If the property is a measurement, you can ask for it in the unit you want by adding the unit symbol in curly brackets inside the square brackets:

[Length {m}]          the length in metres
[Gross Floor Area {m2}]

This is the safest way to work with units, because you know exactly which unit the number is in. Without it, you get the property in whatever unit the model is using.

If the unit symbol is not one Simplebim recognises, it is quietly ignored and you get the unconverted value. Check your result if you are unsure.

Writing values

Numbers

12
3.5
-40

Write the decimal point as a dot. A comma between two digits also works (3,5), so keyboards and habits from comma-decimal countries are fine — but see the warning under Separating arguments, because commas and decimals can collide badly.

Text

Text goes in double quotes:

"Basic Wall"
"Concrete"
""                    empty text

To put a double quote inside text, put a backslash in front of it:

"a \" inside the text"

This is one of the places Simplebim differs from Excel. Excel doubles the quote ("") — that does not work here and will make the expression fail. Use \".

Yes and no

TRUE
FALSE

Capitalisation does not matter — True, true and TRUE are all fine. TRUE() and FALSE() with empty brackets also work, if you prefer the Excel spelling.

Dates

A date property is a real date, so it compares and sorts like one. There are three ways to write a date, and the one you pick decides whether the expression means the same thing on somebody else's computer.

1. The DATE function — always safe

[Created] = DATE(2024; 3; 4)          year; month; day
[Created] > DATE(2023; 12; 31)

2. An ISO date in quotes — also safe

YYYY-MM-DD, year first, with dashes. This is read the same way on every computer:

[Created] = "2024-03-04"
[Created] > "2023-12-31"
[Stamp]   > "2024-03-04 13:45:00"     with a time, when the property has one

3. A date the way you normally write it — convenient, but only for your own use

[Created] = "4.3.2024"                read using your Windows date settings

⚠ This one is not portable. A date written this way is read using your computer's Windows regional settings. On a computer set up differently, 04/03/2024 may be read as 4 March instead of 3 April. The expression will not report an error — it will simply select different objects. If the expression will be shared, or saved into a template that others use, stick to DATE(...) or the ISO form.

Dates compare properly in every direction, including across a year boundary:

[Created] > "2023-12-31"              true for any date in 2024
[Created] >= DATE(2024; 1; 1)
[Created] < TODAY()

And you can pull a date apart:

YEAR([Created]) = 2024
MONTH([Created]) = 3
HOUR([Stamp]) >= 12                   for a property that has a time

Separating arguments — always use ;

When a function takes more than one argument, separate the arguments with a semicolon:

IF([Height] > 3000; "tall"; "short")
ROUND([Area]; 2)
MAX([Width]; [Height])

A comma also separates arguments, but do not use it. Here is why:

IF([Height] > 1,5,6)

You probably meant "if height is greater than 1.5, then 5, else 6". But the two commas are ambiguous — the first one is read as a decimal point and the second as an argument separator, so this becomes IF([Height] > 1.5, 6), which is a different formula with a missing argument. With semicolons there is no ambiguity:

IF([Height] > 1,5; 5; 6)        clear: compare with 1.5, then 5, else 6
IF([Height] > 1.5; 5; 6)        clearer still

Rule of thumb: semicolons between arguments, dots for decimals. Then nothing can go wrong.

Semicolons and dots inside quoted text are left completely alone, so [Name] = "Wall;Type" and [Code] = "1.5" behave exactly as you would expect.

Operators

& happily joins a number to a text: [Height] & " mm" gives 3000 mm.

Careful with ^ after a plain number. 2^3 fails, because the ^3 is mistaken for a unit attached to the 2. Put the number in brackets: (2)^3 works. After a property this is not a problem — [Side]^2 is fine.

There is no AND or OR written between two things. [A] = 1 AND [B] = 2 does not work. Use the functions instead — see below.

Functions

Function names are not case-sensitive: IF, If and if all work.

Logic

The else in IF may be left out — IF([Height] > 1; "yes") is accepted.

Text

TEXT cannot be used on text that looks like a date. TEXT("03/04/2024"; "yyyy") is refused with an error, and so is TEXT([Some Text Property]; ...) when that property happens to hold something like 2024-03-04. TEXT also cannot reformat a real date — it ignores the format you give it. To get at the parts of a date use YEAR, MONTH and DAY instead.

Numbers

Dates

The function library is the Excel-style one, and there are more functions than are listed here. A function name that does not exist simply makes the expression fail, so try it and see.

How each property type behaves

This is what a property turns into when the expression is worked out. It explains why some comparisons look the way they do.

Note the yes/no row. Because the value arrives as 1 or 0, comparing it with the text "TRUE" never matches:

[Load Bearing] = "TRUE"        never true — this compares with the word TRUE
[Load Bearing] = TRUE          correct
[Load Bearing] = 1             correct
[Load Bearing]                 correct on its own, it is already a yes/no

Empty and missing values

This is the single most common surprise, so it gets its own section.

If a property has no value on an object — or the object does not have that property at all — the object drops out of the result entirely. It does not come back as false, or as zero, or as an error you can catch with IFERROR. The object is simply not there.

IF([Width] > 1; "wide"; "narrow")

Objects with no Width get no answer at all — not "narrow". If you are filtering, they are not selected; if you are calculating a property, they get no value.

There is one exception:

• Empty text is a real value. A text property with nothing in it becomes "", and you can test it: [Comment] = "", LEN([Comment]) = 0 or ISBLANK([Comment]) all work, and the object stays in the result.

For numbers, dates and yes/no properties there is no way to test for emptiness inside an expression — the object is gone before your test runs. To find the objects that are missing a value, select the ones that have it and invert the selection in the user interface.

Text comparison is case-sensitive

[Name] = "Basic Wall"          true for "Basic Wall"
[Name] = "BASIC WALL"          FALSE for "Basic Wall"

This is different from Excel, and it catches people out constantly. If you want to ignore capitalisation, force both sides to the same case:

UPPER([Name]) = "BASIC WALL"
LOWER([Name]) = "basic wall"

(Property names in brackets are a separate matter — those are always matched ignoring capitalisation. It is only the comparison of text values that is strict.)

Recipes

Exact match, ignoring capitalisation

UPPER([Name]) = "BASIC WALL"

Contains some text — SEARCH ignores capitalisation, and ISNUMBER turns "not found" into a clean false instead of a failure:

ISNUMBER(SEARCH("wall"; [Name]))

Does not contain some text

NOT(ISNUMBER(SEARCH("wall"; [Name])))

Starts with / ends with

LEFT([Name]; 5) = "Basic"
RIGHT([Name]; 4) = "Wall"

Two conditions at once

AND([Height] > 2000; [Load Bearing] = 1)

One of several values

OR([Type] = "A"; [Type] = "B"; [Type] = "C")

Convert millimetres to metres

[Height] / 1000

...or better, let Simplebim do it and avoid the arithmetic altogether:

[Height {m}]

Classify into bands

IF([Height] < 2000; "low"; IF([Height] < 3000; "medium"; "high"))

Build a label

[Type] & " - " & [Name]

Round to two decimals

ROUND([Gross Floor Area {m2}]; 2)

Guard against dividing by zero

IFERROR([Area] / [Count]; 0)

Objects created before 2020

[Created] < DATE(2020; 1; 1)

Objects created in a given year

YEAR([Created]) = 2024

Objects created between two dates

AND([Created] >= "2024-01-01"; [Created] <= "2024-06-30")

Objects created in the last 30 days

[Created] > TODAY() - 30

If you know Excel

Most of what you know carries over. These are the differences that will bite you:

Things that do not work

Verified traps, so you do not have to discover them yourself:

Troubleshooting

Some objects are missing from my result. They are almost certainly missing a value for one of the properties you used. See Empty and missing values. Try the expression with just one property at a time to find which one.

My text comparison never matches. Check the capitalisation — comparison is case-sensitive. Wrap both sides in UPPER(). Also check for stray spaces in the data: TRIM([Name]) = "Basic Wall".

My yes/no filter never matches. You are probably comparing with "TRUE" in quotes. Drop the quotes, or compare with 1.

The whole expression fails. Work through these in order:

1. Are all the arguments separated with ;?
2. Is every [ matched by a ], and every " matched by another "?
3. Is there a bare number followed by ^ or by letters? Put the number in brackets.
4. Is the property name spelled correctly? Capitalisation does not matter, but the spelling and the spaces do.
5. Does the function exist? Check the Functions tables.

My date filter picks the wrong objects, or works for me but not for a colleague. You have probably written the date the way you normally write it, for example 4.3.2024, which is read using your Windows regional settings. Use "2024-03-04" or DATE(2024; 3; 4) instead — those mean the same date on every computer.

My number came out in the wrong unit. Ask for the unit explicitly: [Length {m}] rather than [Length].

The result is a number where I wanted true/false. IF(...; 1; 0) gives a number. For selecting objects you want true/false — either compare something ([Height] > 3000) or use IF(...; TRUE; FALSE).