‘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.
LET
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:
- http://en.wikipedia.org/wiki/Assignment_(computer_science)#Single_assignment (Information involved: Single assignment)
- http://en.wikipedia.org/wiki/FLWOR (Information involved: FLWOR is the expression where “LET…RETURNS…” originated from)
- http://www.w3schools.com/xquery/xquery_flwor.asp (Information involved: XQuery FLWOR Expressions)
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.
Operator | Description | Data Types |
---|---|---|
& | Add two strings together.E.g. “A” & “B” results in “AB” | Strings |
+ | Adds two numbers together.E.g. 1 + 2 results in 3 | Numbers |
– | 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.
Operator | Description | Data 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: http://en.wikipedia.org/wiki/Operator_precedence
In order from tightest to loosest binding:
- Unary + –
- Unary trailing %
- ^ (exponentation)
- Multiplication: * /
- Addition: + –
- Concatenation: &
- Comparison: = <> > >= < <=
Thus, the following expression: A + B / D is equivalent to A + (B / D)
More examples:
Original Expression | Explicit Ordering of Operations |
---|---|
1 + 2 / 3 | 1 + (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 (1^{st} Level)
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