Calculated Columns

Creating calculations in columns is a powerful and versatile way for using data from other columns, tables or header fields to derive new values or to create rules and validations. The MX Deposit formula editor is simple and intuitive to use; you do not need to be an expert programmer to define even complex calculations.

This topic describes how to set up calculated columns in tables and use the formula editor. Other related topics discuss calculated column functionality in greater detail.

The rest of this topic is divided into:

Creating a Calculation

When you add a numeric, text or date column to a table, there is an option for making it a calculated column. Toggle the Calculated switch to Yes, then click in the Formula box to open the formula editor:

A screenshot of a computer Description automatically generated

Using the Formula Editor

You can either type the formula expression directly or build it from the Inputs and Functions panel to the right of the formula expression.

A screenshot of a computer Description automatically generated

Inputs are items that are available for you to use. These can include:

  • Internal inputs. These are columns within the table you are editing
  • External inputs. These include referencing project and activity names, header fields, and columns from external tables other than the one you are editing.

The Functions list contains mathematical operators and other calculation elements that can be used.

Click Edit Inputs to see the columns being used in what you have already entered.

Formula Building Basics

  • Use single quotes ' ' to identify text strings
  • Functions and inputs are case sensitive
  • Parentheses/round brackets ( ) are used as part of the BEDMAS standard order of operation
  • Curly brackets { } are used to reference an input
  • Square brackets [ ] are used as nested parentheses for the ifThen conditional function

Tips for Building a Calculation

You may find the following useful when building a calculation:

Choosing the Column Type

Consider the return output when selecting the column type for your calculated column: The column type you set should match the return output of the formula.

For example, here the return output of this validation formula is the text Check Weight and so a Text column type is selected:

When the return output of the calculation is numeric, such as RQD %, then select the Numeric column type.

When any of the date functions are used in a formula, the column type can vary. See the Date Functions in the catalogue for a detailed breakdown of which column type to choose when using date functions.

You can't change the column type after a formula is added to a column. The column would first need to be deleted and a new one then added.

Precision Values for Numeric Columns

For a numeric column type, you can enter a Precision value. If no precision is entered, the result will, by default, be a whole number. Here we have entered 2; the resulting value will be to two decimal places.

Required Setting for Edit Column

When you add a new column, you have the option to make the column required.

  • If Required is set to yes, the logger will be alerted that this is a column that requires an input.
  • If Required is set to no, it is possible to leave values empty when logging.

Since calculated columns rely on formulas to derive information, it is not necessary to select this option if you are going to add a formula.

Required Setting for Edit Inputs

When editing a formula, you can specify whether each of the inputs is required or not required. To do this, click Edit Inputs, then enable Required for any column that must have a value for each row.

  • If Required is set to yes, all rows in the column must have values before the formula will run.
  • If Required is set to no, the calculation will use the default value when calculating that row. If no value is provided, that row will not produce a result and will be left blank.

For a detailed example of how this setting can be used in a formula, see the example for calculating True Azimuth in the Formula Gallery.

Entering Names of Inputs and Functions

In the formula editor, you can use the search function in the sidebar to easily find inputs and functions.

When typing in the formula editor, possible inputs are suggested. For example, when the input selected is a list, the value column for that list will display by default. However, if you would like to use another column within the list, such as using the description column, then replace Value with Description in the Formula Expression by typing it directly. Columns within that list are suggested as you type.

You can also select Edit Inputs in the formula editor. Here the Lithology.Description is selected from the drop down list instead of the default Lithology.Value.

Finding and Replacing in Calculations

To bring up the Find and Replace window, use the keyboard shortcut Ctrl+F for PCs or Command+F for Macs.

Using Header Fields

When building a calculation, the header fields that are displayed are those that are assigned to the same activities as the table you working in. This is because when you reference a header field you would normally want to use one that is related to that table.

In this example, we have a validation formula in a Survey Table. The Target depth field from the Header is used in the formula so that a warning appears if the depth exceeds the target depth.

If a header field does not display as a suggested input, it could be because the header isn't part of the same activity as the table you are in.

Using External Column Inputs

To reference an external text or numeric column from a table, other than the one you are editing, select External Input Column from the inputs and complete the parameters for that column in the window that appears.

Open the formula editor and select External input column from the side panel.

Complete the Input name field, and select the source Table and Column. Here we have chosen to add the Comment column from the GeotechComment table to the Lithology table.

Complete any sorting and filtering parameters to display the correct information from the external source table's row to the corresponding destination table's row.

Here we are displaying the First value in the Geotech Comment table that is sorted by the To column, and is equal to the To column in the Lithology table, so the To columns are used to line up and display the correct corresponding information.

After you have selected Add filter and chosen your column to filter on, you can choose to either enter a simple value directly into the field, or it can be a destination table column. To do this, hover your cursor over the blank area next to the filtering column. An overflow menu will appear from which you can choose to Use destination columns:

Click Apply to add it to your formula expression.

See Obtain Recovery % From RQD to Samples Table When Intervals are Mismatched in the Formula Gallery for an example of selecting an Aggregate Function, setting Sort By options and defining Filters.

Scrolling and Line Breaks

When you have a long formula, you may need to scroll across your screen to see the complete formula, as is the case here:

To avoid this, you can enter a new line for ease of viewing at logical separation points in the formula expression. However, doing so is not required

Editing an Existing Calculated Column

To edit a calculated column, click on the column name:

A screenshot of a computer Description automatically generated

You can then edit the calculation by clicking the formula. This opens the formula editor.

Useful Keyboard Shortcuts

Keys - PC Keys - Mac Action
Ctrl+F Command+F Find - brings up the Find and Replace window
Ctrl+G Command+G Find next
Shift+Ctr+G Shift+Command+G Find previous
Shift+Ctrl+F Command+Alt+F Replace
Shift+Ctrl+R Shift+Command+Alt+F Replace all
Ctrl+A Command+A Selects all content of the editor
Ctrl+C Command+C Copy selection
Ctrl+V Command+V Paste
Ctrl+D Command+D Deletes the whole line under the cursor including the new line at the end
Ctrl+Z Command+Z Undo the last change
Ctrl+Y Command+Y Redo the last undone change
Ctrl+Home Command+Home Move the cursor to the start of the formula expression
Ctrl+End Command+End Move the cursor to the end of the formula expression
Ctrl+Backspace Alt+Backspace Delete to the left of the group before the cursor
Ctrl+Delete Ctrl+Alt+Backspace Delete to the start of the group after the cursor
Tab Tab Insert a tab at the cursor