How to Define an Expression Column

Expression columns allow you to define calculated values that are to be used in application views. Expression columns are defined as new records in the columns table. The formula/expression is specified in the Default Value field. The definition uses hard-coded values, operands, functions and macros. The value is calculated at runtime.

In column macros, you can only include fields from tables within the same identifying tree. In other words, you can only include fields belonging to the current table as well as any table that is an identifying parent to the table for which you are defining the column. As a result, for example, if you are adding a calculated value to the sales orders table, you cannot use a field from the customers table in the calculation. It is possible, however, to include an SQL select statement or a function call to a stored procedure that will retrieve values from a table that is external to an identifying tree. The result of either the SQL select statement or the stored procedure must return a single value only.

The following subtopics are available:

• Creating a Literal
• Creating a Domain
• Refreshing the Data Dictionary and Literals
• Defining an Expression Column
• Examples

Creating a Literal

Literals are static strings of text used within the application's user interface to define database elements and events in easy-to-understand terms. Typically, PointForce Enterprise users do not require access to the Literals view. We strongly recommend that you contact your TECSYS SMB Professional Services Representative for additional assistance.

When creating an expression column, you may use an existing literal, or, you may require a new literal.

Follow these steps to create a literal:

1. Launch the Literals (md_literal) resource.
2. Select the Create action.
3. Enter the literal key (e.g. total_line_quantity) and click Continue.
4. Select the literal type (e.g. 17 - Label) and click Submit to apply your changes.
5. Launch the Literal Texts resource and select the Create action.
6. Enter the literal key you just created.
7. The locale key is defaulted from your current session. Typically the locale key should default to en_CLIENT which is correct for the PFE environment. Click Continue.
8. Enter the literal text in the description box, e.g. Total Line Quantity and click Submit to apply your changes.

Creating a Domain

A domain is used to assign a set of properties, such as the logical data type, to a database column. Its attributes provide information about how such a column object should be displayed and what rules should be used when validating data of this type.

In this example, we will define an expression column called Total Line Quantity. This field will be defined on the orders 1 (ibis_oeoo1) table and will display the sum of the open order quantities for all the associated sales order lines.

If we analyze the domain requirements for this expression column, we would see that the qty_decimal_11_2 domain meets our requirement. We can use this domain or create a new domain that emulates this one.

Follow these steps to copy an existing domain:

1. Launch the Domains resource.
2. Perform a search for the domain you wish to emulate and select the View Record Details action.
3. Select the Copy action.
4. Replace the old domain name with a new domain name, e.g. total_line_quantity.
5. Click Submit to apply your changes.

Follow these steps to create a new domain:

1. Launch the Domains resource and click Create.
2. Enter the database name (e.g. ibis) and the domain name (e.g. total_line_quantity) and click Continue.
3. Enter the description literal key you created in the previous step (e.g. total_line_quantity). The alternate identifier should already be populated with the domain name value.
4. Select the data type from the drop-down menu. Since we are creating a calculated column for this example, select the Fixed Decimal option.
5. Enter the data length (e.g. 11) and the decimal places (e.g. 2).
6. Enter the minimum and maximum range values.
7. Set the Renderer Code flag to the Formatted Numeric option.
8. Set the Enumeration Display flag to Value - Description.
9. Set the Enumeration Sort Order flag to Sequence.
10. Select Submit to apply your changes.
11. Refresh the metadata using the Refresh Data Dictionary and Literals resource. For more information, refer to the Refreshing the Data Dictionary and Literals subtopic.

For more information on domains, please refer to the Domains help topic.

Refreshing the Data Dictionary and Literals

After you have created your literal and domain, you must refresh the metadata and literal database for your changes to take effect.

Follow these steps to refresh the data dictionary and literals:

1. Launch the Refresh Data Dictionary and Literals resource.
2. Click the Launch Reload Agent hyperlink.
3. Once the Loading Complete message appears, close the resource.

For more information on refreshing the data dictionary and literals, refer to the Refresh Data Dictionary and Literals topic.

Defining an Expression Column

We will define a new calculated field named Total Line Quantity in the orders 1 table (ibis_oeoo1). The Total Line Quantity field will sum up all the quantities for all related sales order lines with a matching order number.

Follow these steps to define an expression column:

1. Launch the Columns resource and select the Create action.
2. Enter the database name (e.g. ibis), the table name (e.g. oeoo1), the column name (e.g. total_line_quantity), and click Continue.
3. Enter the domain name of the domain you created in the previous step, i.e. total_line_quantity.
4. Specify the key associated with the literal representing the description you want to assign the column, i.e. total_line_quantity.
5. Set the Physical Sequence to 9999.
6. Set the Type to Expression.
7. Ensure that the Maintainability Scope is defined as Not Maintainable.
8. Set the Is Required flag to No.
9. Set the Is Available for Results flag to Yes.
10. Enter the default value of the expression column. In this example, we want to have the column display the open order quantity sum in the stocking UOM from all the related sales order lines; therefore, the expression (SQL) would look something like this:

coalesce((select sum(ordqty) from oeoop where oeoop.company={column.oeoo1.company} and oeoop.salord={column.oeoo1.salord}),0)

11. Click Submit to apply your changes.
12. Refresh the metadata using the Refresh Data Dictionary and Literals resource.
13. Log out of the application and then log back in.
14. Retrieve the view for which you created the column, and then personalize it by adding the new column to one of its pages.

Examples

Examples (Within Identifying Tree)

The following table lists examples of default values that can be entered for an expression column.

Examples (Using an SQL Select Statement)

In this example, we use an embedded SQL select statement. We retrieve the value of the Weight Cost field from the Branch Warehouse Record (icwm) table and add it to the Products (ici1.im13) view.

1. Follow steps 1 to 3 in the defining an expression column procedure, using the following values:

   Database Name: ibis

   Table Name: ici1

   Column Name: wghtcst

   Domain Name: wghtcst

2. Enter the following statement in the Default Value field:

   (select wghtcost from dbobject.ici1)
   
   where company={column.ici1.company} and
   whse="01' and
   prodct={column.ici1.prodct})

3. Follow steps 5 to 13 in the Defining an Expression Column procedure (above).

4. Perform step 14 from the Defining an Expression Column procedure (above), using the Products (ibis_ici1.im13) view.