The eBMS platform has a formula engine designed to be compatible with Microsoft Excel as much as possible. This includes implementing many of the Excel functions in a compatible manner. On top of the Excel compatibility functionality, we have also implemented new language features and functions to make complex transformations easier and allow access to Nimblex specific features like database access.
See http://office.microsoft.com/en-au/excel-help/excel-functions-by-category-HP005204211.aspx for a reference of all the normal Microsoft Excel functions. We have many of these, but not all. In particular, few-none of the finance and statistical functions have been implemented. Within the platform there is a Formula Function Reference (found in the control panel) page for users to see the list of functions that are available for use in any particular version.
The formula language is a functional language, meaning that there are no true variables and no way to return data except for the return value of an expression.
- 1 + 2
- 5 / 7
- “Hello “ & “there”
- SUM(1, 2, 3, 4)
- DB_LOOKUP(“A”, “B”, HASH(“C”, 5))
Usually formulas run in the context of a record, and so column names may be used e.g.: [Full Name], RecordID. Note that for columns which have a space in the name, we need to add square brackets to be able to reference a column in a formula.
How to Put Formulas into a Control
The following steps are to put formulas into a control.
- Click control [Result] in ‘Control’ view
Click the Edit button in the Data field of Properties on the left side of your screen.
- The Referenced Data window will show
- Type in [Alpha]+[Beta] in the Formula text-box.
- Close the window.
- Click the Preview tab to check whether it works
- Put some values such as 1 in [Alpha] and 2 in [Beta] to see the result
- The value 3 should be calculated in control [Result]
Trying Out a Formula
Sometimes you will find it convenient to try out a formula without the putting it in a control, saving, creating a record, repeat- process. This is where the Formula Evaluator comes in.
To access it, open the Control Panel, and then click ‘Formula Evaluator’.
On this page you have a textbox at the top to enter formulas, and a log at the bottom where the results are output.
Note that any [Column Names] you reference in the evaluator will act as if they are blank (because there is no record).
If you want to try out a formula in context of an actual record, you can do this in the ‘Preview’ tab of the eForm Designer:
The structure of this is much the same, but you can use field references. In this case the form fields ‘First Name’ and ‘Last Name’ are concatenated together with a single space in between.
Note that the output is not simply the result, but represented in a way to allow you to see complex structures as text. In this case it is a string of length 10 with ‘John Smith’ inside- but for more complex cases involving arrays, hashes, and even just numbers stored in strings, this can be invaluable in working out what is going on.