Math Expressions
Field Squared lets you perform basic math functions within document controls and custom fields.
Field Squared also supports dynamically updating custom field values as you type with calculated values that pull in values from other controls, objects or custom fields.
An example math expression would be:
{document.SLIDER1}
This expression would "look" in the current document and would evaluate to the value stored in SLIDER1 right now.
You can also perform mathematical calculations on the values in document controls or other objects. For example:
{document.SLIDER1} * {document.SMT1}
This would multiply the value in SLIDER1 (a slider), let's say with a value of 2, by the value in SMT1 (a smart text field set to a keypad type of decimal) with a value of 10. The final result would be the number 20.
Math operators we support in math expressions include:
- + which adds values together
- – which subtracts values
- * which multiples values
- / which divides values (be careful not to divide by zero!)
You can also use parentheses ( and ) to group expressions together and perform order of operations calculations on fields:
({document.SLIDER1} + {document.SLIDER2}) * {document.SMT1}
In this case, we replace SLIDER1, SLIDER2 and SMT1 with actual values first
(10 + 20) * 0.5
and then this evaluates to the number 15.
Multiplication and Division by decimals (and floats/doubles)
If you want a math expression to multiple and divide using floating point / double / decimal arithmetic, you need to add a ".0" to the end of the hard coded numbers that are used mathematical expressions (this is required by many programming languages and since we are cross platform is required for all our clients to support these expressions).
For example:
{document.feet} + {document.inches} / 12.0
If put the result as 12 instead of 12.0 the system will evaluate the expression using integer math instead of decimal math.
What can a Math Expression read from?
The bindings in math expressions need to be numbers or text that can be easily converted into a number.
Controls like Slider natively support numbers, while other controls like Smart Text (with a numeric keypad) also store numbers, but in text format.
Document Controls you can use in math calculations are:
- Smart Text – with numeric keypads enabled
- Slider – this control always contains numbers
- Segment – where the values are configured to be numbers
- Text Area – where the value only contains a single number
- Details Tables – special rules apply to details tables – see below*
You can also use math expressions with custom fields if the custom field is configured to store numbers, decimals, or money.
Smart Text controls do not support numeric keyboards.
Using Math Expressions in Math Controls
Math expressions can only be used in Math controls in documents, or as part of a logical numerical expression (covered in our article on Logical Expressions).
By default, the Math control will always automatically and dynamically update its value as you type.
Math Controls populate automatically when the document is first created and will update each time the document is opened, whenever other controls change their values, and any time the Math control scrolls on or off the screen.
Math Controls support rounding to a certain number of decimal places in the calculated value and offer the option to format the result as money with the $ sign in front of it.
Note: While you can type math expressions into other controls and custom fields, they will all treat and evaluate the expressions as text instead of numbers. So an expression like "{document.SLIDER1} + {document.SLIDER2}" for a smart text control would evaluate as the string "100 + 100" instead of giving you the number 200. If you put that same expressions into a Math Control, you would get the answer 200.
Math expressions have full access to the data model from the Document. You are not limited to querying just values inside the current screen you're working on.
For example you can have expressions such as:
{document.SLIDER1} * {task.data.TARGET} / {task.firstAsset.data.EXPECTED}
Details Tables*
Math expressions can apply to Details Tables.
A details table stores data as a list of customizable objects. You can perform math operations on each details table using our aggregate functions.
{document.DTL.Sum('TOT')} * 1.072
The example above will look at all the items in the details table and sum up the TOT property in the details table. The total returned from calculating that value will be multiplied by 1.072.
The available functions are:
- Sum() – adds up all the values in the table for a particular property
- Count() – counts how many rows in the table have a value in a particular property vs. those that don't have a value
- Average() – determines the average value for a property in a list – ignores rows that have no value
- First() – returns the value from a property in the first row of the list
- Last() – returns the value from a property in the last row of the list
Each of these functions takes an input argument in single quotes which lets you know which property of the details table you'd like to total, count, or average.
Sum('ARG')
ARG is the value we are trying to calculate. ARG is actually one of the bindings in the sub-document that's used to setup the details table.
Example: let's say we have a details table that contains a materials list. We want to automatically calculate the total value for each item in the materials list, the total value of all materials, and the average item price.
- We create a sub-document called INVOICE_ITEM with the bindings QTY, DESCRIPTION, ITEM_PRICE and TOTAL
- We configure TOTAL inside the sub-document to be Math control, with an expression of {subdocument.QTY * subdocument.ITEM_PRICE}
- The subdocument is embedded inside a Details Table in the main document, and the binding for that Details Table is called MATERIALS and the sub-document type is set to INVOICE_ITEM.
- We create a Math control in the main document and set the expression to reference the Details Table and calculate the total price using the expression: {document.MATERIALS.Sum('TOTAL')}
- To calculate the average item price, we use the expression: {document.MATERIALS.Average('ITEM_PRICE')}
Math expressions inside sub-documents that want to reference controls inside the sub-document use the {subdocument} object type.
As each row is edited inside the sub-document the math controls will update the total values. Also, back in the parent document, the TOTAL and AVERAGE values will be updated in real-time as you type.
You can also use Math expressions to calculate other values that are lists in the system. This is currently limited to the Count() function. For example:
{asset.children.Count()}
This would return all the child assets of the asset that this document was created against. However, to use the Count() function {asset.children} or a any similar object must be a list.
If you created a document against task and the task was linked to an asset, the expression would look like this:
{task.firstAsset.children.Count()}
You could also a count of all siblings of the asset that the current document is about. For example, how many other wells are at the same pad site?
{asset.parent.children.Count()}
This would navigate to the asset that this Document is about. Then would find it's parent asset, find it's parent assets' children (siblings to the current asset) and count them up. If you wanted to exclude the current asset in the count, you could do a simple math function:
{asset.parent.children.Count()} – 1
Advanced Math (coming soon!)
At this time the self-service math expressions are unable to calculate trigonometric, volumetric functions, any kind of non-linear math, or calculus functions. Future releases will provide such self-service functionality however, non-self service customers can request such features to be added.