Creating selection lists for traceability

Three columns in the Traceability Priority List table (TrPriMst) are not used by the GainSeeker system. You can use these columns to dynamically change the items displayed for a traceability list.  

For example, if a data entry operator selects a particular Department, you may want the list of Machines to reflect only those machines from the selected department rather than those from all Departments.

The Traceability Priority List table (TrPriMst) is formatted as follows:

Column name

UDL

UDLPOS

UDLVALUE

NAME

LONGINT

STRING30

STRING150

Data type

4-byte integer

4-byte integer

30-character
string

20-character
string

4-byte
integer

30-character
string

150-character
string

Used by GainSeeker?

Yes

Yes

Yes

Yes

No

No

No

Contents

Designates which traceability field (traceability field 1, 2, etc.)

Reserved for future use -
do not modify contents

Item in this user's traceability priority list for this traceability field

GainSeeker login name (user or group name)

 

 

 

The SPC/DMS Priority List Editor sets the values for the first four columns when you create the traceability priority list.

To set values in the last three columns, use the SPC/DMS Priority List Editor to edit the traceability priority list. Click the Expand button to show more options.

In this example, the expanded list changes the Expand button to a Collapse button.

The values that you set in these last three columns will depend on the type of template logic you will use to further restrict the traceability list. The following examples show two ways of doing this.

Example 1:

In the loan processing department, two traceability fields have been set up as follows:

Traceability
field
number

Traceability
field
name

Values in the
pre-defined
traceability list

3

Loan Type

  • MORTGAGE

  • PERSONAL

4

Loan Form Number

  • 1033

  • 1034

  • 1035

  • 1043

  • 1044

However, Mortgage loans are processed using forms 1033, 1034 and 1035, while Personal loans use forms 1043 and 1044.  

The template users would like for the system to work such that after choosing a Loan Type of "Mortgage", the list of available Loan Form Numbers will display only forms 1033, 1034 and 1035.  Similarly, choosing a Loan Type of "Personal" should display only the Loan Form Numbers 1043 and 1044.

To accommodate this need, the template designer created a Priority List on Loan Form Numbers for these users and then set values in the STRING30 column as follows:

TrPriMst Table contents

UDL

UDLPOS

UDLVALUE

NAME

LONGINT

STRING30

STRING150

4

0

1033

LOAN PROCESSING

 

MORTGAGE

 

4

0

1034

LOAN PROCESSING

 

MORTGAGE

 

4

0

1035

LOAN PROCESSING

 

MORTGAGE

 

4

0

1043

LOAN PROCESSING

 

PERSONAL

 

4

0

1044

LOAN PROCESSING

 

PERSONAL

 

The template designer then created a template that uses this extra column to display only the needed loan forms to the user, depending on which loan type was selected:

Cell

Type

Column

Formula

Explanation

[D1]

Keyboard

Loan Type

 

User selects from pre-defined list of Loan Types

[E1]

Formula

Loan Form Number

Svar 1 =
CONCAT

"Select UDLVALUE From ",
Table "TrPriMst",
" Where UDL = 4 And STRING30 = '",
[D1],
"'" :

 

LIST_Q 1, "Select Loan Form Number", Svar 1

The template first creates an SQL statement and holds the completed statement in Svar 1.  For example, if the user selected "MORTGAGE" in cell [D1], the resulting SQL statement is:

Select UDLVALUE From TrPriMst

Where

UDL = 4

And STRING30 = 'MORTGAGE'

The clause "UDL = 4" ensures that we only return values for traceability field number 4 (Loan Form Number).

The LIST_Q command then runs this SQL Statement, and the values returned from the statement (loan form numbers) are displayed as a list from which the user must choose.

Notice that the TRACE_OPT command is not needed to set options for the Loan Form Number priority list, because the LIST_Q command actually creates the list of Loan Form Numbers that is displayed to the user.

Example 2:

Continuing with the previous example, a new form – number 1053 – has been added for processing both Mortgage loans and Personal loans.  

The template writer begins by adding this new item to the pre-defined list for Loan Form Numbers:

Traceability
field
number

Traceability
field
name

Values in the
pre-defined
traceability list

3

Loan Type

  • MORTGAGE

  • PERSONAL

4

Loan Form Number

  • 1033

  • 1034

  • 1035

  • 1043

  • 1044

  • 1053

Next, the template writer adds the new Loan Form Number 1053 to the Priority List for these users and makes a few additional changes:

TrPriMst Table contents

UDL

UDLPOS

UDLVALUE

NAME

LONGINT

STRING30

STRING150

4

0

1033

LOAN PROCESSING

 

;MORTGAGE;

 

4

0

1034

LOAN PROCESSING

 

;MORTGAGE;

 

4

0

1035

LOAN PROCESSING

 

;MORTGAGE;

 

4

0

1043

LOAN PROCESSING

 

;PERSONAL;

 

4

0

1044

LOAN PROCESSING

 

;PERSONAL;

 

4

0

1053

LOAN PROCESSING

 

;MORTGAGE;PERSONAL;

 

In this example, the table column STRING150 is not used.  If the number of options in STRING30 caused the value to be more than 30 characters long, however, then STRING150 would need to be used because of its greater length.

Now that the STRING30 column can contain 2 or more values, the SQL query must use "STRING30 LIKE... " instead of "STRING30 = ...".  

To guard against finding a substring of a longer value (e.g., asking for STRING30 LIKE '% MORTGAGE%' and finding both "MORTGAGE" and "MORTGAGE RE-FINANCE"), the template designer has placed the desired values between semicolons.  This ensures that a query for STRING30 LIKE '%;MORTGAGE;%' finds only the desired value (";MORTGAGE;").

The template designer has now modified the template as follows:

Cell

Type

Column

Formula

Explanation

[D1]

Keyboard

Loan Type

 

User selects from pre-defined list of Loan Types

[E1]

Formula

Loan Form Number

Svar 1 =
CONCAT

"Select UDLVALUE From ",
Table "TrPriMst",
" Where UDL = 4 And STRING30 LIKE '%;",
[D1],
";%'" :

 

LIST_Q 1, "Select Loan Form Number", Svar 1

The template first creates an SQL statement and holds the completed statement in Svar 1.  For example, if the user selected "MORTGAGE" in cell [D1], the resulting SQL statement is:

Select UDLVALUE From TrPriMst

Where

UDL = 4

And STRING30 LIKE '%;MORTGAGE;%'

The clause "UDL = 4" ensures that we only return values for traceability field number 4 (Loan Form Number).

The LIST_Q command then runs this SQL Statement, and the values returned from the statement (loan form numbers) are displayed as a list from which the user must choose.

Notice that the TRACE_OPT command is not needed to set options for the Loan Form Number priority list, because the LIST_Q command actually creates the list of Loan Form Numbers that is displayed to the user.