Expression Editor

The Expression Editor is used in several places in the application including Custom Fields, Gantt Charts, Constraints, Spreadsheets and Range Filters. There are several types of items that can be used in an Expression (and don't forgot the Search filter bar that can be very useful in helping you find what you're looking for!):

  • Functions: Functions are very similar to what you use in Excel such as IF, RIGHT and YEAR. Also includes commonly used application functions such as GETVALUE and TABLEVALUE.

  • Operators: Operators are a symbol or function denoting an operation (e.g. ×, +) and also include logical functions (e.g. And/&& and Or/||)

  • Constants: Constants are things that never change. They include Equipment, Processes and relevant Table Positions and Fields. The most common Constants are True and False but also includes things like Pi and options for the Between Function.

  • Table (includes Source/Destination/Calendar when relevant): Table variables include Fields and Positions. Nomenclature to note in Scenarios is that the Table type (Source/Destination/Calendar) is prepended to the variable name eg. ImportedWasteVolume becomes SourceImportedWasteVolume.

  • Scheduling: Only appearing on Scenario Expressions, the variables available here vary significantly depending on where the Expression is being used. Equipment Expressions only have access to the Current Date, Custom Field Expressions have access to everything and others fit somewhere in between.

  • Equipment and Processes: Only appearing on Scenario Expressions in specific locations, these allow Expressions to make use of comparisons using the Code Name nomenclature discussed below e.g Equipment=EquipmentBlasting. You can also draw the name off of a current Equipment or Process using the Text() function.

Clicking on each Function, Constant or Variable provides a description of the use of it and double-clicking will enter it into the Expression.

 

Return Types

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)

Search

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.

Expression Editor (Top)

Referencing the Current Table & Current Node

The following variables expand the utility of the TableValue and TableParameterValue functions (described in the next section).

CurrentTable

CurrentCalendarTable

CurrentSourceTable

CurrentDestinationTable

CurrentSourceNode

CurrentDestinationNode

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:

GetValue(SourceImportedCoalVolume)

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

GetText(SourceImportedCoalVolume)

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.

Text(GetText(SourceImportedCoalVolume),GetFormatString(SourceImportedWasteVolume)

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.

TableValue("Deposit","ImportedCoalVolume","Alpha/S1/B1/D/100")

A GetFieldName function can be used to returns the name of the current field and expands the utility of the TableValue function:

TableValue(CurrentSourceTable, GetFieldName(Field), CurrentSourceNode)

Expression Editor (Top)

Filter vs Value Expressions

Many areas in the application 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 Logic Functions.

Logic Functions

(If, Between, In)

Similar to their Excel counterparts, If(), Between() and In() are all useful in Expressions. Expanding on the example earlier

If(GetValue(SourceImportedCoalVolume)>5000,"Large","Small")

would return a "Large" or "Small" Text string.

If(Between(GetValue(SourceImportedCoalVolume),5000,10000),"Medium",
If(GetValue(SourceImportedCoalVolume)>10000,"Large","Small"))

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.

Constants that use Code Names

A common example of Constants in practice are the Equipment and Processes you set up in your Scenario. These type of Constants use Code Names.

In your project, you might have a piece of Equipment named Blasting, but you might also have a Process called Blasting. You can't use Blasting as the name for each as they have to be unique, so the application places their location before the name, so the piece of Equipment called Blasting has the Code Name EquipmentBlasting (no spaces) and the Process called Blasting has the Code Name ProcessBlasting (no spaces).

Positions, Equipment & 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)

Expression Editor (Top)

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:

CInt(Substring(Text(SourceStrip),LastIndexOf(Text(SourceStrip),"Strip" )))

Operators

  • And: And, &, &&
  • Or: Or, |, ||
  • Greater Than: >
  • Less Than: <
  • Equals: =, ==
  • Not Equals: !=, <>

For more information, see: Contextual Functions & Variables

Options

The Options that are available in the Expression Editor will depend on the (Field, Attribute) type of node selected or the function selected. See: Options

Expression Editor (Top)

Example Expressions

Format Strings