Substituting a Regular Expression for a Recordset Field

Use the Substitution dialog box to configure advanced read and write options for both attributes and recordset fields.

Note: Use regular expressions judiciously and only when necessary. Added performance overhead can occur when using regular expressions.

See How to use Regular Expressions in Host Integrator for more information on basic guidelines regarding regular expressions.

In this example, use the sample model CCSDemo to substitute a recordset field usually displayed with a trailing minus sign, with one which will be displayed with a leading minus sign. There are also steps to test the output.

Open CCSDemo in the Design Tool.
Click to connect to the host.
Select AcctTransactions from the Entity list to navigate to the AcctTransactions entity.
Click the Recordset tab, then select the Fields tab on the lower portion of the Recordset tab.
Select the row named Amount.
In the Read group box, click Advanced to open the Read Field Substitutions dialog box.
Click Insert - - to add a row for a new substitution.
Create an expression that matches a money field with a trailing minus sign. In the Search For column of the newly created row, enter the expression:

(^|[^-.\d])(\d+(?:\.\d+)?)-(?=[^-.\d]|$)

See details of this expression
To transpose the minus sign to the front of the field, enter the following in the Replace With column:

$1-$2
In the Description column, type:

Move minus sign to front
Close the Read Field Substitutions dialog box and click Apply to update your model.
To test the results, select Procedure Test from the Debug menu.
From the Table drop down list, select Transactions and confirm that the GetTransactions procedure is selected.
In the Value field of the Procedure Filters box, type 167439459
Click Execute.
Scroll down the procedure output and note that several amounts have a leading minus sign.