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 |
20-character |
4-byte |
30-character |
150-character |
Used by GainSeeker? |
Yes |
Yes |
Yes |
Yes |
No |
No |
No |
Contents |
Designates which traceability field (traceability field 1, 2, etc.) |
Reserved for future use - |
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.
In the loan processing department, two traceability fields have been set up as follows:
Traceability |
Traceability |
Values in the |
3 |
Loan Type |
|
4 |
Loan Form Number |
|
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 = "Select UDLVALUE From ",
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.
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 |
Traceability |
Values in the |
3 |
Loan Type |
|
4 |
Loan Form Number |
|
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 = "Select UDLVALUE From ",
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.