The Expression Editor is used in almost every element of Spry to offer intermediate and advanced customisation. Similar to Excel they offer many functions that provide a significant amount of power without scripting. Clicking on each Function, Constant or Variable provides a description of the use of it and double-clicking will enter it into the Expression.
To begin with, it’s important to understand that different Expressions “return” different types of results: Values (numbers), Text, Booleans(True/False) and DateTimes. The simplest Expressions for each type would be:
- Value: 1
- Text: “1”
- Boolean: True
- DateTime: Date(2020,1,1)
Searching the Expression Editor
One of the most useful tools in the Expression Editor is the ability to search the Functions and Variables available using the search bar. If you know roughly what you require start typing in the search and the results will narrow.
Reading Data from a Table
The Get() Functions (GetValue, GetFormat, GetText, GetCoordinate) return Data from a Table using a Field as an input. These Functions are limited in that they only reference contextual Tables (Source, Destination and Calendar on a Scenario for example) and to reference other types of Tables you would use the TableValue() and TableText() Functions instead. GetValue() returns a Value so if being used on a Scenario then
would return the Imported Coal Volume Field from the associated Source Table (e.g 10350.009234).
GetText() will return Text from either strings OR values (including the Format String) so
would return the Imported Coal Volume but instead of 10350.009234 it would return “10,350” (with a default Format String on the field of “#,##0”).
GetFormatString() will return the Format String from the Field Setup so that it can be used as the second parameter (the Format String) in a Text Function.
would return the Imported Coal Volume but instead of 10350.009234 it would return “10,350 bcm” (assuming the Imported Waste Volume Format String was “#,##0 bcm”).
TableValue() and TableText() follow a similar pattern except that they require specific reference not only to the Field but also the Table and Node as well.
Filter vs Value Expressions
Many areas in Spry that use Expressions have both a Filter and Value Expression available. Filter Expressions evaluate and return Booleans, whereas Value Expressions return either Values or Text depending on their context.
If a Filter Expression returns True, then the Value Expression will also be evaluated. If the Filter returns false, the Value Expression will return nothing. If you wish to have a Value Expression that changes depending on certain conditions (if this, else that) then you would use one or more Conditional Functions defined below.
Logic Functions (If, Between, In)
Similar to their Excel counterparts, If(), Between() and In() are all useful in Expressions. Expanding on the example earlier
would return a “Large” or “Small” Text string.
You may notice that in this example there is a line break between the True and False options. Line breaks have no effect in Expressions except to make them easier to read.
You might have a piece of Equipment named Coal, but you might also have a Process called Coal (and a Bench named Coal!). You can’t just use Blasting as the name for each as they have to be unique, so Spry places their location before the name. The piece of Equipment called Coal has the Code Name EquipmentCoal (no spaces) and the Process is called ProcessCoal.
Positions, Equipment and Processes
There are two distinct ways to reference Positions, Equipment and Processes in Expressions. Firstly, the terms themselves are Variables (Equipment, Process and various Levels such as Strip or SourceStrip depending on context). So the following function would return the name of those particular Variables at the time the Expression is evaluated:
Text(Process) + " " + Text(Equipment) + " " + Text(SourceStrip)
You can also use the Code Name form for comparisons in Logic Functions
If(Process=ProcessCoal,1) OR If(In(Process,ProcessDrill,ProcessBlast,ProcessCast),1)
Type Conversion Functions (CInt, Text, Date)
There are times when particular results need to be converted into others (Text that needs to be a Value as an example). In the Process example above the Text() Function is used to convert a Process into Text. An extended example, using several Functions we can take the Strip number out of a string (which comes out as Text by default) and convert it to a number like this:
Operators (And, Or, Greater Than, Less Than, Equals, Not Equals)
- And: And, &, &&
- Or: Or, |, ||
- Greater Than: >
- Less Than: <
- Equals: =, ==
- Not Equals: !=, <>
Contextual Functions and Variables
Certain Functions and Variables are only present in particular parts of Spry which are detailed below. This list is by no means exhaustive so it’s always good to check the relevant documentation for specific examples.
Calculated Fields: Node Functions (GetChild, GetFirstChild, GetLastChild, GetNextSibling, GetParent, GetPreviousSibling) and CurrentNode
These Functions extend the GetValue and GetText to look at records other than the current node (for which the code name is CurrentNode). A “Sibling” is a Node that is at the same Level, a “Parent” is a Node (or several) above and a “Child” is a Node below. “Previous refers to an earlier Index and Next refers to a later one. There are several key points to note when using these Node Functions.
- A GetValue(Field, Node) can return a Value at a higher level than the leaf. When this is the case, for example
, then an aggregation must be set on the Field called in order to return a value.
- You can stack several Node Functions together to move around your Table. See the image below for some examples.
- It is possible for a Node Function to return a “null” result. For example
will return a “null” or empty result which cannot produce a value. There are two ways to manage this: Either don’t prepare for it (default) which will produce an error on your Field rather than a result. The second option is to ignore the error using the SkipErrors boolean overload and the ValueIfError overload for the GetValue. For example
Table Plots: Data Functions (NodesSum, NodesWeightAvg, NodesMin, NodesMax)
Because Table Plots often rely on groups of Leaves to provided aggregated results (e.g. Strip Ratios are a combination of all Waste divided by all Coal in a vertical group of Leaves) GetValue type Functions needs to be wrapped in a Nodes Function to correctly aggregate the results. This is because there will be examples where you want NodesSum(e.g Strip Ratios), NodesWeightAvg(e.g Quality Plots) and NodesMin/Max(e.g. Period Progress Plots)
Custom Fields: Source Percentage Completed
When creating Custom Fields, it’s important that Value Custom Fields that are Aggregated by Sum are also multiplied by the SourcePercentageCompleted Variable to ensure they are not over-represented.