To set up the bulk change parameters:

1. From the Module Menu select Maintenance, General, Utilities and then Bulk Change.
2. Set up the parameters you want (see below for details).
3. Click Save Settings to save the parameters to an xml file.

Selection

You can select the records you want to change by browsing or Boolean query or SQL statement. Select Include deactivated records if you want to select deactivated as well as active records. You can click Test Selection for a count of the number of records selected.

Field

Select the format and the field you want to change. All fields in a repeating group are changed, e.g. borrower’s telephone number.

Current field value must equal

This is where you can specify the current value of the field.

• Value. Element must equal this value otherwise it won’t be changed.

• SQL. An SQL statement must return “1” otherwise the element won’t be changed. Place holders can be used for element data. These will be substituted with element data before the SQL statement is applied to the database.

#ELMDATA_INTERNAL	Internal form of data
#ELMDATA	External form of data. Same as internal form except for date (dd mmm yyyy) and time fields (hh:mm:ss am/pm).
#ELMIRN	Element IRN.
#ELMPOS	Element position.

For example, IF ('#ELMDATA' = 'ABC') SELECT 1 ELSE SELECT 0.

• Regular expression. This regular expression will be applied to the element data via SQL function dbo.fn_SpyIsRegExMatch(value,pattern). If the pattern matches then the element will be updated. For example:

SELECT dbo.fn_SpyIsRegExMatch('Mary ABC','^Mary')

• Is value null. If you select Must be null then the element value must be empty for the change to be applied. If you select Must not be null then the element value must not be empty for the change to be applied.

Change field value to

This is where you can specify the new value of the field.

• Value. Element will be changed to this value.

• SQL. Change the element value to the result of this SQL statement. Substitution place holders may be used, for example:

SELECT CAST(1.05 * 25.55 AS MONEY)

SELECT DATEADD(year, 2, N'#ELMDATA')

SELECT UPPER(N’#ELMDATA’)

SELECT REPLACE(N’#ELMDATA’,N’ ',N''))

• Regular expression and Replacement regular expression. Change the element to the value returned from:

dbo.fn_SpyRegExReplace(value,pattern,replacement_pattern)

• Action. If you select Update element then the change will be applied only if the element is already present in the record. If you select Update and add element then the change will be applied even if the element is not already present in the record. If you select Clear element then the element will be cleared.

Process

• Description. Enter a description for the task.

• Validate mode. Select Validate mode to test the effect of your changes. No records will be updated, but changes to the first 100 records will be displayed in the task record as a guide. The dialog is initialised with Validate mode selected by default.