Advanced Formulas
  • 14 Sep 2020
  • 4 Minutes To Read
  • Contributors
  • Print
  • Share
  • Dark

Advanced Formulas

  • Print
  • Share
  • Dark

‘LET’ and ‘$’

The eBMS formula engine extends Excel with something approximating variables but that are immutable and consistent with the functional and predictable nature of formulas.


The LET expression is of the form:

LET $a := 1;
LET $b := 2;
RETURN $a + $b

This allows you to perform a complicated calculation in steps, storing temporary results as you go. This can greatly simplify some formulas, particularly when doing list processing.

Please refer to the following websites for more information:

Callback functions

Some formula functions take an argument described as a ‘lambda’. This is basically a callback function. For example, ARRAY_WHERE takes a lambda that is used to determine which rows to keep and which to ignore. A lambda is defined as follows:
$(a, b, c) <Any expression>

Within the body of the lambda the argument values are defined by the function calling back into it. For example, ARRAY_WHERE passes in two values (the current value, and the current index).

I.e. ARRAY_WHERE($someArray, $(indexn, item) $item > 50) (need a specific explanation, maybe we should have an extra example which verifies or replace the arguments: “someArray”, “item”, “index”.

This would return the values from $someArray that are greater than 50.

Binary Operators

A binary operator has 2 arguments. E.g. A + B, A and B are arguments to the + operator.

OperatorDescriptionData Types
&Add two strings together.E.g. “A” & “B” results in “AB”Strings
+Adds two numbers together.E.g. 1 + 2 results in 3Numbers
Takes the difference of two numbers.Numbers
/Divide the first argument by the second.Numbers
*Multiple the second argument by the first.Numbers
=<> >=


General comparison operators.If arguments are of different data types they will follow the rule:Null < Number < Date < String < Boolean(Like principles in Microsoft Excel, any Boolean is considered to be greater than String, any String is considered to be greater than Date, any Date is considered to be greater than Number and any Number is considered to be greater than Null. For example, The string “1” is considered to be greater than the number 1.)Any
^Exponentiation. I.e. 2^3 is equivalent to POWER(2, 3)Numbers

Unary Operators

A unary operator has a single argument.

OperatorDescriptionData Types
+No effect on numbers. Will coerce arguments to become numbers.Number
Similar to multiplying any value by -1. Makes any positive number negative, and vice versa.Number
%Percentage, in effect divides a number by 100. Turns 87% into a 0.87 so that the expression 87% * 100 returns the expected result of 87. Percentage sign is a trailing operator (appears after).Number

Referencing Fields/Columns/Names

When using a formula within an eForm record, all the columns/fields of an eForm are available. Column names that consist only of letters and numbers can be used exactly as named. For example, [A] is different from [a].

E.g. A + B references columns A and B

More complex column names, most often because of their use of space require the use of square brackets.

E.g. [Sum of Values] + [All Variations]

A good general practice is to always use square brackets

In a multiline eForm you may reference a specific line by using the [Name]#(LineNumber) notation.

Operator Precedence / Order of Operations (So basic, knowledge from High School)

When you are doing maths, the eBMS Formula language behaves like many other languages. Some operators are more tightly binding than others.

For general information on understanding what operator precedence:

In order from tightest to loosest binding:

  1. Unary + –

  2. Unary trailing %

  3. ^ (exponentation)

  4. Multiplication: * /

  5. Addition: + –

  6. Concatenation: &

  7. Comparison: = <> > >= < <=

Thus, the following expression: A + B / D is equivalent to A + (B / D)

More examples:

Original ExpressionExplicit Ordering of Operations
1 + 2 / 31 + (2 / 3)
1 + 2 + 3(1 + 2) + 3
1 * 2 + 3 * 4(1 * 2) + (3 * 4)

How to Hide Blocks through Options
Sometimes it is necessary to make some blocks closed when users select a category or tick a checkbox. When designers access into the Design view of eForm, they can edit the Properties of block to make it closed by following the steps below.

  • Create an eForm named Contract Creation
  • Add two blocks named Contract Details for one and Proposal Status for another
  • Create a label and a dropdown box named Contract Type on the block of Contract Details, and then add values for the dropdown box: Research Contract/ Stand-alone Contract.
  • Click the blank area of the block of Proposal Status
  • Click the Edit button in Advanced area of Properties at left side of interface, there will be a pop up window called Advanced Property Binding
  • Type in formula in Collapsed? area under Layout section. The formula is: IF(TRIM([Contract Type])=”Stand-alone Contract”, True, False). It means the block of Proposal Status will close if users select Stand-alone contract for the Contract Type dropdown box
  • Close the pop up window, save these changes and check results

Link Management in Portal (1Level)
When users get into the Design view of several systems Entry Portal, they can edit the links to manage the Portals.

  • Create a new Web Link by click blank field of the portal and click Web Link in Navigation section of left panel
  • Make the Web Link selected and edit the link Properties such as Width, Height, Text, Back Color, Text Color, Bold, Font Size, etc.
  • Type in the Url of the Link to make the link point to the eForm or Website that users want
Was This Article Helpful?