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

  Applies To 
  Product(s):

WaterGEMS, SewerGEMS, CivilStorm, StormCAD, PondPack, SewerCAD, HAMMER, WaterCAD

  Version(s): CONNECT Edition, V8i
  Area:  Modeling
  Original Author: Mark Pachlhofer, Bentley Technical Support Group

Problem

How do you create an if...then...else conditional statement using the Real(formula) type for a user data extension?

Solution

The conditional if statement that can be entered using the "iif" button in the formula query manager accepts information in the following format:

iif({condition},{then-return-value}, {else-return-this-value})

Note:
you can also use "Like", "And" and "Or" within your IIF statement, if needed.

When you click the iif button the statement format is preset for you so you only need to enter the arguments between the commas. An example of an iif statement is below:

iif( [Physical_NodeElevation; Feet] > 800,1,0)

After the model is run this statement will return 1 to the user data extension field created if the physical elevation of the junction elevation is greater than 800 ft., otherwise it will return 0.

NOTE:

- Avoid using the equals sign, as small variations can result in inequality, which you might not see in the program interface due to decision precision or in some cases unit conversion - data is always stored in US units and then converted back to the display units, which can introduce a small error.

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)

What if I want to return a non-numeric result such as True/False or text?

Since the Real(formula) field is of type real, which is a numeric field, you can only return numbers to it. Some possible workarounds:

  1. Have the formula return certain number codes, like 1 or 0 for true and false, then set up queries based on that. For example one query might be called “Good condition” and the query could be <your UDX> = 1
  2. Set up Color coding based on the number codes returned by the formula UDX

  3. If you really need to see non-numeric values in the properties, set up an Excel spreadsheet and use Modelbuilder to update.

    First, set up your spreadsheet in Excel with empty columns for the input fields (the fields in the model), and columns with the appropriate formulas to return the desired non-numeric result.

    Next, create regular User Data Extensions (such as text or boolean) to store the output result of your formulas.

    Next, set up a connection to the spreadsheet in Modelbuilder and map the input and output columns. In the last step, choose No when asked if you'd like to build the model. The connection will then be saved to the Modelbuilder manager.

    Once its saved to the Modelbuilder Manager, all you need to do is click the sync out button (to send the model data columns to the spreadsheet), open the file in Excel to refresh the functions (and output columns), save the file, then click the “build model” button in Modelbuilder to import the calculated data into the regular UDX fields in the model. When something changes, it’s only a few clicks to update.

Related Enhancement # 720515 (Text-based fields for formula UDX)


See Also

Query, alert or formula UDX not working correctly with "=" (equals) 

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

Creating Formula-Based User Data Extensions

Recommended
Related