Calculated Values and Logical Expressions

When a logical Expression is used on a calculated value, the logical expression is attached to a field. The expression is interpreted and returns a value. For example, you could say that if a user selects a check box in the application, the Logical Expression could return one value, but if the check box

is not selected, then another value is returned.

Using Logical Expressions in Validations

In the Business Entity Designer (BED), you are able to attach a Logical Expression to a field. The result of the Logical Expression can be TRUE or
FALSE. Any condition can be entered in the Condition section, The IF section is used to validate one field against another. These sections are strictly for Field Values. After constructing your Logical Expression, it will return a TRUE, 1, or FALSE, 0; when TRUE the field is valid, If FALSE the field is invalid.

Logical Expressions in the Model Designer (MoD)

This is the third way to use Logical Expressions, and is only possible with the NET architecture. In the Model Designer (MoD), we can get the status of a control. Controls that are updated or executed can be made to return a value as part of the Business Rules. The returned values can be attached to a Logical Expression that can say that if there is a value in a field, set that field to be viewable. This is similar to the status flags that are set in the Govern Security Manager (GSM).

In the OFD there are flags specifically for Logical Expressions. Flags such as, CanAddLogExp, CanExecuteLogExp, CanUpdateLogExp, CanDeleteLogExp allow for User Interface (UI) validation.

For example, inspections with a status of complete, i.e. all data parameters have been completed, a Logical Expression query can be used to say that the status of the inspection is FALSE. If parameters are incomplete, the status can be set to TRUE. In this example, the Logical Expression can be used to enable or disable a UI field.

To access the Logical Expression Editor, in the Govern New Administration (GNA)…

1. Select Setups/Editors > Editors > Logical Expression Editor…

In a Logical Expression, a Temporary Field can be specified that can hold any value. For example, a temporary field can be used to hold a value generated from a query, a database field, or from user input. See Using a Temporary Field on page 199.

Logical Expression Editor Command Buttons

New: Click New to clear the form so that you can enter new data.

Save: Click Save to save a new Logical Expression or any modifications to an existing one. Logical expressions are saved to VT_USR_LOGEXP.
Delete: Click Delete to remove the current record from the database.

Closing the Editor

To close the editor, click the close window button in the upper right hand corner of the form.

Logical Expression Editor – Expression Tab Parameters

Expression Name: Enter a name or code for the logical expression.

English Short Description: Enter a short description. This is used for fast data entry and look-ups if space is limited on forms.
English Long Description: Enter a long description. This is displayed during look-ups and on forms and reports.

Second Language Fields

When there is a 2nd language, or multiple languages, ensure that these description fields are also completed.
Is System: This flag / option is reserved for constants that are designated as Govern.NET system data.

About System Reserved Values

Only users with Super-User access will be able to select and deselect the Is System option. In addition Super Users can also create new values and flag them for Govern.NET system use.

Message: If required, when your logical expression returns an error enter a text string that will be displayed on the error message.
Logical Expression Block: Compose your logical expressions in this edit box. There is no limit to the number of statements you can add.
The following operators can be used:

Insert in Formula group – Add Query

It is under the add query parameter that the types of query to be inserted into the expression block is selected.
Add Query: There are two (2) drop-down menus under the Add Query parameter. The first parameter acts as a filter for the types of queries that will be displayed in the second parameter. The displayed queries can then be added to the Logical Expression Block.
Selection options for the filter are:

• All – Select this option to display all queries that are available.
• Select – When selected, this option will display only selection queries.
• Action – This option display only action queries, i.e. update and delete type queries.

Building Logical Expressions

You can compose your logical expressions in the Logical Expression Block edit box, or copy and paste an expression from another file. Your logical
expression can be made up of an unlimited number of statements, constants, formulas, keywords, queries, database fields, numeric values and strings.

Logical Expression Statements

The logical expression can be built from the following types of statements: IF… THEN… ELSE, DO, RETURN and IN.

IF… THEN… ELSE… Statements

This type of statement executes a group of statements, conditionally, depending on the value of the logical expression.

The following rules apply:
1. IF, THEN and ELSE must be written in uppercase.
2. A space must be entered between the statement and the action.
3. Only Select Queries can be used with the IF statement. For more information on queries, see SQL Query Editor on page 243.

The syntax is as follows:

IF condition THEN
statement
ELSEIF elseifcondition THEN
elseifstatement
ELSE elsestatement
ENDIF

DO Statement

This type of statement executes an action.
The following rules apply:
1. DO must be written in uppercase.
2. A space must be entered between the statement and the action.
3. It must be followed by an action.
4. The action must be followed by a “;” (semi-colon).
5. Only Action Queries can be used with the DO statement. For more information on queries, see SQL Query Editor on page 243. Also refer to the Insert in Formula group – Add Query on page 188 for selection options.

The syntax is as follows:

IF condition THEN
DO action;
ELSEIF elseifcondition THEN
DO action;
ELSE
DO action;
ENDIF

RETURN Statement

This type of statement returns a value.
1. RETURN must be written in uppercase.
2. A space must be entered between the statement and the action.
3. The action must be followed by a “;” (semi-colon).

The syntax is as follows:

IF condition THEN
RETURN value;
ELSEIF elseifcondition THEN
RETURN value;
ELSE
RETURN value;
ENDIF

You can enter any of the following, after the RETURN:

• Select Query: from the Add Query group drop down list. The query name must be entered between braces, { }; Refer to the Insert in Formula group – Add Query on page 188 for selection options.
• Formula: from the Formulas drop down list. A formula must be entered between bar delimiters, “| FORMULA |”.
• String: ‘text’. A string is entered between single quotation marks,” ‘ ‘ ”.
• Date: #2004#
• Value: 999
• Keyword: from the Keywords drop down list. A keyword must be placed between tildes, ” ~ ~”.
• TRUE: in uppercase
• FALSE: in uppercase
• A database field name must be entered, with the table, between square brackets, [TABLE.FIELD]. IN Statement

The following rules apply to the IN Statement:

1. An item list separated with a comma (,) must be placed after the IN operator.
2. The item list must be delimited with quotation marks (“)
3. The item list must contain at least two items.
4. The items in the list must be of the same type; i.e., numeric OR string
5. If the list contains items of different types, the items must be delimited with single quotation marks, noting that the type will be converted to a string.
6. All lists must be terminated with a semicolon in order to be valid.

Logical Expression Comparison Operators

<TABLE>

Logical Operators

<TABLE>

Adding Formulas, Database Fields, Queries, Keywords and Constants
To include a value from a formula, database field, query, keyword or constant, in a logical expression, select the item from the drop-down list. You can include as many of these values as needed, by making multiple selections. The item is added where the cursor is placed in the Logical Expression
Block edit box. For example, you could make a comparison of two values, retrieved through Selection Queries.

The following restriction applies:

All fields, selected in the Fields list, must be associated with the same function.

To create a new formula, query or constant, from the Logical Expression Block Editor, click the drop-down list for the item.
Add Formula: To include a value calculated through a formula, select the formula from the drop-down list. Formulas are displayed between bar delimiters, ex. |FORMULA_NAME|.

IF |IMP_VALUE| > 0 THEN
RETURN |IMP_VALUE|;
ELSE
RETURN 0;
ENDIF

To create a new formula, click “…” to display the Formula Editor. See Formula Editor on page 162 for more information.

Add Keyword: Select a keyword from the drop-down list to include it in the logical expression. Keywords are used to retrieve a value currently in memory; such as ~ parcel id ~ to retrieve the parcel id of the current record. Keywords are displayed in lowercase, between tildes, ~ ~.

For example, to include the building sequence for the current building record, in the logical expression, select the ~building sequence~ keyword.

For example,
IF ~parcel id~ > 0 THEN
RETURN ~parcel id~;
ELSE
RETURN 0;
ENDIF

You can also include the keyword, ~textbox value~, in a logical expression, to include a value entered by the user in a text box:

For example,
IF ~textbox value~ > 100000 THEN
RETURN False;
ELSE
RETURN TRUE;
ENDIF

The following table lists and describes available keywords.

<TABLE>

For example,
IF ~textbox value~ > 100000 THEN
RETURN False;
ELSE
RETURN TRUE;
ENDIF

Tables: To include a database field in a logical expression, you need to select the table first, from the Tables drop-down list.

Add Query: You can include either Action Queries, i.e., queries that perform some action, such as updating records in one or more tables, adding records to a table or deleting records from a table, or Selection queries; i.e., used to retrieve data from one or more fields, in a logical expression.

For example, in the following logical expression:
IF {APP_VALUE} > 0 THEN
RETURN |APP_VALUE|;
ELSE
RETURN 0;
ENDIF

The Appraised Value of the current record is retrieved, through the {APP_VALUE} Selection query, and if the value is positive it is returned by the
logical expression.

The queries need to be defined through the User-Defined Queries Setup form. To include a query, select it from the list. The query name is displayed in the Logical Expression Block edit box between braces, { }. For example, {AG_ VALUE}.

To create a new query, click “…”. This opens the SQL Definition Setup form. See SQL Query Editor on page 243 for more information.

The following conditions apply:

• Only Select Queries can be used with the IF statement.
• Only Action Queries can be used with the ACTION statement.

Fields: After selecting the table, select the field from the Fields drop-down list. The value of this field for the current record is included in the logical expression. These selected table and field are displayed in uppercase, between square brackets; for example [AC_ EXEMPTIONS.FROZEN_ID].

IF [PC_PARCEL.P_ID] > 0 THEN
RETURN [PC_PARCEL.P_ID];
ELSE
RETURN 0;
ENDIF

Add Constants: Select a constant from the drop-down list, to include it in the formula or click “…” to open the Constant Value Editor and create a new constant. Constants are displayed between ampersands (& &). See Constant Value Editor on page 155 for more information.

IF &TAX_RATE& > 0 THEN
RETURN &TAX_RATE&;
ELSE
RETURN 0;
ENDIF

Message: Enter a message to be displayed if the validation fails.

Dates, Numeric Values and Strings: You can also include dates, numeric values and strings, as follows:

• Dates are displayed between crosshatches: IF A = #2004111# THEN
• Numeric values are displayed: IF A = 999 THEN
• Strings are displayed between single quotation marks: IF A = ‘text’ THEN

Using a Temporary Field

You can include a temporary field in a logical expression. This field can hold any value, such as one generated from a query, database field or user input.

To include a temporary field, add the following to the beginning of the logical expression:

SET ?TMP1? = <value>, where <value> is the value you are using

To include multiple temporary fields, enter each value separately:

SET ?TMP1? = <value1>
SET ?TMP2? = <value2>
where <value1> and <value2> represent the values you enter

In the following example we see that within our IF/ELSE block, we have the following:
The following logical expression is based on a keyword and database field:

IF (~year id~ – [MA_BUILDINGS.YEAR_BUILT]) <= 10 THEN
RETURN 6;
ELSEIF (~year id~ – [MA_BUILDINGS.YEAR_BUILT]) <= 20 THEN
RETURN 5;
ELSEIF (~year id~ – [MA_BUILDINGS.YEAR_BUILT]) <= 30 THEN
RETURN 4;
ELSE
RETURN 3;
ENDIF

When written as above, the statement line is executed repeatedly. Should the IF statement contain one or more queries system performance could be severely impacted.

The above logical expression can be written using temporary fields, as follows:

SET ?TMP1? = ~year id~
SET ?TMP2? = [MA_BUILDINGS.YEAR_BUILT] IF (TMP1 – TMP2) <= 10 THEN
RETURN 6;
ELSEIF (TMP1 – TMP2) <= 20 THEN
RETURN 5;
ELSEIF (TMP1 – TMP2) <= 30 THEN
RETURN 4;
ELSE
RETURN 3;
ENDIF

Best Practices for Logical Expressions

Logical Expression Structures

For our example we would like to execute a logical expression with the following structure…

IF |FORMULA| > {Query} THEN
RETURN |FORM2|;
ELSEIF |FORMULA| < {Query} THEN
RETURN |FORM2|;
ELSEIF |FORMULA| = {Query} THEN
RETURN = 3;

Best Practices dictates that the above structure be rewritten as follows:

SET ?Formula_Result? = |FORMULA|;
SET ?Query_Result? = {Query};
IF ?Formula_Result? > ?Query_Result? THEN
RETURN |FORM2|;
ELSEIF ?Formula_Result? < ?Query_Result? THEN
RETURN |FORM2|;
ELSEIF ?Formula_Result? = ?Query_Result? THEN
RETURN = 3;

When the logical expression is written using temporary fields as above, the initial results are “cached”, as a result the same query and results are not run repeatedly in each statement line. As a result of this method, system performance is not impacted.

Improve Performance by Improving Design

When a Logical Expression is not written to run efficiently, the design of the expression could potentially impact the performance of the Govern application.

EXAMPLE:
IF ({BA1} = -1) AND ({BA2} = -1) AND ({BA8240} = 0) THEN RETURN
|PBR1|;
ELSEIF ({BA1} = -1)
AND ({BA2} = -1)
AND ({BA8240} > 0)
THEN RETURN |BA_PARK|;
ELSEIF ({BA1} = -1)
AND ({BA2} > -1)
AND ({BA8240} > 0)
THEN RETURN |BA_PRK_OV|;
ELSEIF ({BA1} = -1)
AND ({BA2} > -1)
THEN RETURN |PBR2|;
ELSEIF ({BA2} = -1)
AND ({BA1} > -1)
THEN RETURN |PBR3|;
ELSEIF ({BA2} > -1)
AND ({BA1} > -1)
THEN RETURN |PBR4|;
ENDIF

In the above example, although the logical expression is valid and will run, as a result of the design, the {BA1} and {BA2} queries are run six (6) times, and the {BA8240} query three (3) times. Each time a query is run, system resources are required, this potentially impacts system performance. To prevent this situation from occurring, instead of calling the queries directly, Variables can be declared to hold the result of the queries.

Using Variables when constructing Logical Expression

The above example can be rewritten is as follows:

EXAMPLE:
SET ?V_BA1?={BA1};
SET ?V_BA2?={BA2};
SET ?V_BA8240?={BA8240};
IF (?V_BA1? = -1) AND (?V_BA2? = -1) AND (?V_BA8240? = 0)
THEN RETURN |PBR1|;
ELSEIF (?V_BA1? = -1)
AND (?V_BA2? = -1)
AND (?V_BA8240? > 0)
THEN RETURN |BA_PARK|;
ELSEIF (?V_BA1? = -1)
AND (?V_BA2? > -1)
AND (?V_BA8240? > 0)
THEN RETURN |BA_PRK_OV|;
ELSEIF (?V_BA1? = -1)
AND (?V_BA2? > -1)
THEN RETURN |PBR2|;
ELSEIF (?V_BA2? = -1)
AND (?V_BA1? > -1)
THEN RETURN |PBR3|;
ELSEIF (?V_BA2? > -1)
AND (?V_BA1? > -1)
THEN RETURN |PBR4|;
ENDIF

In the above optimized version of the original example, using the SET statement the results of the three (3) queries were declared as variables called ?V_BA1?, ?V_BA2?, and ?V_BA8240?. The queries would be run once, the results would then be cached in memory and reused. An advantage here is that should the value be changed in the system by another user the result of our code fragment would not be impacted as we are still using the cached result.

Executing Formulas and Logical Expressions