Creating Formula-Based User Data Extensions

Product(s): WaterGEMS, WaterCAD, StormCAD, SewerCAD, SewerGEMS, CivilStorm, HAMMER, PondPack
Version(s): CONNECT Edition, V8i SELECTseries 2 +
Area: Calculations

Overview

The purpose of this technote is to briefly review the methodology for setting up a formula-derived User Data Extension, also known as Formula UDX. Additional information can be found in the Help section for the program. 

Background

User Data Extensions (UDX) have been a feature available in the OpenFlows products for some time. Starting with V8i SELECTseries 2 (and greater, including CONNECT Edition), a 'Formula' ability was added to give extra flexibility to the user. This feature will allow the user to calculate results that the programs might not otherwise generate and display these results in the element properties or FlexTables.

NOTE: a formula-derived user data extension currently only evaluates at a given time step. Meaning, you currently cannot perform functions against time-series data, such as the maximum, minimum or average value of a result field over the simulation duration.

General Information on User Data Extensions

User Data Extensions allow the user to create new fields for use in reporting, graphing, or data analysis. For example, you can add a field for keeping track of the date of installation for an element or the type of area serviced by a particular element. 

The UDX dialog can be accessed directly by going to Tools > User Data Extensions. Selecting this will open the User Data Extension dialog.

For a general overview of User Data Extensions, see: How to create custom fields using User Data Extensions

There are a number of data types available. These include integers, real (or any fraction decimal number), text, date/time, boolean (true or false), and formula.

There is a label field to allow the user to give the UDX a unique name so that it can be easily viewed in the element properties or FlexTables. With real and formula UDX fields, the user must also enter Dimension, Storage Unit, and Numeric Formatter.

To create a UDX, highlight the element and select the New icon. The section on the right will fill in with default data. As this technote is related to formula-based User Data Extensions, change the Data Type to “Real (Formula)”. When this is completed, the Units section will appear. Enter the appropriate Dimension, Storage Unit, and Numeric Formatter for analysis you will be conducting. You should also change the label to something recognizable for the analysis. Once you have done, select Okay.

Note: Once you select Okay, some fields, such as Data Type, Dimension, and Storage Unit, cannot be changed or edited. The formula itself can still be edited though.

Formula-based User Data Extensions

Bentley's Hydraulics and Hydrology products give a wide range of results that are generated after the computing a model. In most cases, these results are sufficient for analyzing and reporting on a system. However, there are times when a user may want to see a set of results that are not available in the program. It was for cases like this that the formula-based User Data Extension was developed.

Note: You cannot create a formula that uses properties from more than one element type, and the results of a formula UDX are only evaluated at a given timestep (you currently cannot find the max/min/average for example, of a result over time)

The formula-based UDX allow the user to generate new results based on the data already calculated after computing the model. The basic steps below are universal, with only the element types and available properties differing depending on the product. The example below will be using WaterGEMS.

Let us say that you want to display the percentage of pressure head compared with the calculated hydraulic grade for the junctions in a WaterGEMS model. To begin, open the User Data Extension dialog. Create a new UDX by selecting the New icon at the top of the dialog. Change the label to something recognizable to these results and change the Data Type to “Real (Formula)”. Since the result will be a percentage, change the Dimension field to “Percent”, the Storage Unit to “%”, and Numeric Formatter to “Percent”.

Next, click in the Formula cell. An ellipsis (...) will appear.

Click this to open the Formula dialog.

In the upper left is a list of all available properties for the junctions. The list is in alphabetical order. The same properties are accessible through the “>” icon above the list. In the upper right are all of the mathematical functions available. The empty window in the lower part for the dialog is where the formula is built.

To move a property to the lower window, simply double-click the name. It will then appear in the window. Note that the appearance is not the same as the property list; the window shows the Access database table name for that property. To include a simple math function, like multiplication or subtraction, click the corresponding button. For the geometric or trigonometric math functions, double-click the name to include in the formula.

In the example where we want the percentage of the pressure head compared with the calculated hydraulic grade, first select the “(“ icon. Next, find “Pressure Head (ft)” in the properties list and double-click. Next choose the divide icon, then the “Hydraulic Grade (ft)” property, and then the “)” icon. Since we want this as a percent multiply this by 100.

Once completed, select 'OK' to return to the User Data Extension dialog. Click 'OK' again to save the UDX.

Notes:

Many features, including the Data Type, Dimension, and Storage Unit, cannot be changed or edited after selecting 'OK'.

You can also use operators such as OR and AND with your IF statements if checking for multiple conditions for your output. E.g. For multiple conditions, your formula based UDX can be something like this;

IF{(condition1) AND (condition2)}, output if True, output if False)

Creating Unitless UDX Formula - based on other Unitless UDX formulae

You might have come across a question "It is possible to reference a unitless formula UDX in another Unitless formula UDX?". The answer is Yes, We can create a Unitless UDX formula by taking reference of other Unitless UDX formulas. Let us take a small example as seen below -

 As you can see in the right image there are four different unitless formulas (TS rank 1 to 4) were created based on different situations to see the value of the ratio of Pipe Discharge (Liters per second) to Design capacity (Liters per second).

Now if we take the ratio of both values, the units (Liters per second) will cancel out each other and it will become a unitless number. 

But if we wanted to create a new unitless UDX formula with the help of all other unitless UDX formulas and we validate, it gives the  "Inconsistent unit for field......." error message. 

Note - The problem is because the unit has no label, the unit definition in the formula is incomplete and the parser/program doesn’t know how to handle it.

There is a workaround to get over this issue while working on User Data Extensions which is explained as follows -

1. When we try to use a field with a unitless unit, you will get this in the formula:

[Pipe_Field_1]

2.  We just need to modify it by adding the ";UnitlessUnit" value to become the formula as you see below:

[Pipe_Field_1;UnitlessUnit]

Note - All you need to type should be inside the square bracket. 

Let us continue with the above example for a better understanding

As shown below, if we add a ';UnitlessUnit' value after the field (inside the bracket) and we validate the formula then there are no issues found. This is how you can create a Unitless UDX by taking reference of other unitless UDX. 

Note - There will be some future improvement in the next releases that can be made, to automatically assume this “UnitlessUnit” in such cases. 

If you want to create a unitless/dimensionless formula kindly visit the topic in this wiki article - How to create a Unitless or Dimensionless formula (ratio) in UDX? 

Viewing Results

Since the results are saved as user data extensions, you can view the results in either the element properties or the FlexTables.

After computing the model, double click on a junction to open the Properties dialog. Find the  “User Defined” section. This is where the formula UDX field will be located and, since the model was computed, you should see the results.

To see the results in the FlexTables, open the element FlexTable (View > FlexTables, for instance). With the FlexTable open, select the Edit icon. Scroll through the list on the left to find the UDX field. The list is in alphabetical order. Select the 'Add' button and it now appears in the list on the right. Click 'OK' to return to the FlexTable. The statistical analysis results should now be available.

This data is also available for results presentation in graphs and data tables. You will also be able to created color coding and annotation through Element Symbology once the User Data Extension is created.

Note: Formula User Data Extensions are results fields, and thus cannot be viewed in the User Data Extension alternative. You can view the formula UDX results in element symbology, queries, FlexTables, property grids.

Exporting UDX

If you want to use the User Data Extensions that you have created, you can export them to a .XML file for use in another model. To do this, click the Export to XML button in the upper left. This will allow you to save the UDX as an .XML file for later use. To add the exported UDX's into a new model, open the User Extension Manager in the new model. Then click the Import button in the upper left and browse to the .XML file with the User Data Extensions you wish to use. The UDX's will then appear in the model for use.

Can we share Formula-based UDX?

The answer is 'No'. Forumla-based UDX cannot be shared, since they will reference properties or results that are only available for a given element. The other types can be shared across elements. As for added value, it can help with duplication of effort, if there is some user-defined property that you want to include for multiple elements. This allows a user to create a UDX once and share it, rather than create the same UDX over and over again. UDX is created for certain element types for eg. a text field for a Pipe can be shared for use in other elements. Please note that I will share filed definition only and not the actual value it holds in the UDX.

Video demonstration

See Also

How do you create an IF / THEN / ELSE statements using Formula derived user data extensions?

Can a user data extension with an IF, THEN, ELSE (iif) statement assign a value to another field?

User Data Extensions in the Fireflow report

Add custom data to profile with User Data Extensions

How is the “max” operator used with formula-derived user data extensions?

User data extension fields are not showing up in the section they were created in and cannot be graphed.

(Blog) Creating Custom Calculated Results with Formula User Data Extensions

Recommended
Related