| Navigation: How To's and Troubleshooting > How to… >====== Using drop-down lists to Lookup Records ====== |    |
There are two control templates which allow you to easily add the ability to lookup records from a data file and save a value to another file–the FileDrop control and the FileDropCombo.
Using the FileDrop Control Template (pick from existing list only, no updates)
This Control template scrolls through a data file and assigns the value of the selected field to the Use variable of the list control. It does not accept input. If you want the ability to enter data and select data, use the FileDropCombo Control template.
There are two different scenarios for which you can use this Control template:
·Displaying, then storing the displayed data.
·Displaying data. then storing an associated code.
Storing and Displaying the Same Data
In this scenario you want to select a value from the lookup file and store it in the Primary file. For example, a Product File with a field storing a color, with a lookup file of colors.
In this case, complete the LIST's Action prompts as follows:
Use
The field to which the lookup value is assigned: select this field when the Application Generator prompts you with the Select Field dialog.
Using the List Box Formatter, populate the list with the field from the lookup file (you may populate additional fields, but be sure to populate the display field first).
Field to Fill From
The field from the lookup file. This value is assigned to the Target Field.
Target Field
The field to which the Fill From value is assigned. In this case this is the same as the USE variable.
Press the More Field Assignments button to automatically assign values to other fields.
Record Filter
Optionally, type an expression to limit the contents of the drop-down list to only those records which match the filter expression.
Default to First entry if Use Variable empty
Automatically assign the value of the first field in the list to the USE variable. The fields in the list are sorted alphabetically (unless you specify Sort Fields).
Displaying Text Data and Storing a Code
In this scenario you want to select a value from a textual field in the lookup file and store its associated code in the Primary file. For example, a Product File with a field storing a Location Code, with a lookup file of Locations. You want the user to select the Location from a list of descriptions, but store the Location Number in the Product file.
In this case, complete the prompts as follows:
Use
Create a local variable that matches the text field. You can create this variable when the Application Generator prompts you with the Select Field dialog.
Using the List Box Formatter, populate the list with the text field from the lookup file (you may populate additional fields, but be sure to populate the display field first).
Field to Fill From
The code field from the lookup file. This value is assigned to the Target Field.
Target Field
The field to which the Fill From value is assigned.
Press the More Field Assignments button to automatically assign values to other fields.
Record Filter
Optionally, type an expression to limit the contents of the drop-down list to only those records which match the filter expression.
Default to First entry if Use Variable empty
Automatically assign the value of the first field in the list to the ?USE variable. The fields in the list are sorted alphabetically (unless you specify Sort Fields).
List Properties:
The List Properties for this control are the same as a list; however, the From entry requires some explanation.
From:
When placing a FileDrop Control, this field defaults to Queue:FileDrop. You should not modify this.
FileDropCombo Control Template (pick from list and add to list)
This Control template scrolls through a data file and assigns the value of the selected field to the COMBO's Use variable. It also allows adding records by typing a new value in the entry portion of the combo box.
There are two different scenarios for which you can use this Control template:
·Storing and Displaying the same data
·Displaying text data and storing a code.
Storing and Displaying the Same Data
In this scenario you want to select a value from the lookup file and store it in the Primary file. For example, a Product File with a field storing a color, with a lookup file of colors.
In this case, complete the prompts as follows:
Use
The field to which the lookup value is assigned: select this field when the Application Generator prompts you with the Select Field dialog.
Using the List Box Formatter, populate the list with the field from the lookup file (you may populate additional fields, but be sure to populate the display field first).
Field to Fill From
The field from the lookup file. This value is assigned to the Target Field.
Target Field
The field to which the Fill From value is assigned. In this case this is the same as the USE variable.
Record Filter
Optionally, type an expression to limit the contents of the drop-down list to only those records which match the filter expression.
Remove Duplicates
Check this box to remove duplicates from the list.
Default to First entry if Use Variable empty
Automatically assign the value of the first field in the list to the USE variable. The fields in the list are sorted alphabetically (unless you specify Sort Fields).
Update Behavior
In this scenario (a lookup file with only one required field), a form is NOT needed to update the lookup file.
Allow Updates
Check this box to enable updates directly from this control.
Update Procedure
Leave this field blank, because no separate update (form) procedure is needed.
Displaying Text Data and Storing a Code
In this scenario, you want to select a value from a text field in the lookup file and store its associated code in the Primary file. For example, a Product File with a field storing a Location Code, with a lookup file of Locations. You want the user to select the Location from a list of descriptions, but store the Location Number in the Product file.
In this case, complete the prompts as follows:
Use
Create a local variable that matches the textual field. You can create this variable when the Application Generator prompts you with the Select Field dialog.
Using the List Box Formatter, populate the list with the textual field from the lookup file (you may populate additional fields, but be sure to populate the display field first).
Field to Fill From
The code field from the lookup file. This value is assigned to the Target Field.
Target Field
The field to which the Fill From value is assigned.
Record Filter
Optionally, type an expression to limit the contents of the drop-down list to only those records which match the filter expression.
Remove Duplicates
Check this box to remove duplicates from the list displayed.
Default to First entry if Use Variable empty
Automatically assign the value of the first field in the list to the ?USE variable. The fields in the list are sorted alphabetically (unless you specify Sort Fields).
Update Behavior
In this scenario (a lookup file with multiple required fields), a form is needed to update the lookup file.
Allow Updates
Check this box to enable updates.
Update Procedure
Specify an update (form) procedure to call to add new records to the lookup file.
List Properties:
The List Properties for this control are the same as a list; however, the From entry requires some explanation.
From:
When placing a FileDropCombo Control, this field defaults to Queue:FileDropCombo. You should not modify this.