This Instruction Article explains how to create a pick-list or pull down list based on information from a database. This is useful in situations where a pull down list is desired but ONLY for information related to a specific key field.
In the example given below, the client had a work order database. The database included a list of work orders and one or many line items for each work order. When complete, the CODEOSFT label design form will prompt the user for the work order number. After entering a valid work order number, the pull down pick list only shows work order lines items for the work order number previously entered.
This article is intended for the advanced user of CODESOFT. It assumes that the user has the following knowledge:
- Basic label design understanding using CODESOFT
- Create/edit database connections within CODESOFT
- Understand and able to create Table Lookup in CODESOFT
The ultimate goal is to create a label design that ask user to enter Order Number and Customer Number in order to print a label. We must create a pick-list or dropdown box containing all the customer numbers within an order so user can select the Customer Number from list instead of typing it in manually.
The information in this article is based on the following assumptions
- One-to-Many Relationships Exist – In the example below, the Work Order (Order Number in table below) for one product has multiple customers (Cust_No in table below). It is similar in concept with an invoice having multiple line items.
- A Table Lookup is Used with Both Key Field (i.e. Order Number) and Pick List Value (i.e. Cust_No) Included – All fields needed from the database are identified in the Table Lookup. This will filter the data available to a pick list to only those records that match the key field entered (i.e. Order Number).
- Drop Down Pick List Variable (i.e. Cust_No) in Table Lookup is Linked to Key Field (i.e. Order Number) – The pick list variable should be setup similar to the screen below.
- When Printed Field Used to Prompt for Key Field Value (i.e. Order Number) – the When printed Cust_Number and Order are data entry fields for user to select the information to be printed on the label
- Configure Table Lookup Value to Generate Dynamic Pick List
- Open the property page of Cust_No under Table lookup/Job$ and select Data formatting on the left side of the window.
- Change Record separator string from /R/N to | (vertical bar or pipe – commonly found at shift-\ on typical keyboard) – this separates the pick list record values (i.e. Cust_No).
- Change Relevant records from 1 to 0 – this allows unlimited pick list records (i.e. Cust_No) to be pulled.
- Click OK to complete – this essentially creates a dynamic pick list in CODESOFT.
- Configure When Printed Value to Connect to Dynamic Pick List
- Open property page for Customer Number under When printed
- Select When printed on the left side of the window
- Under Pick list, change the value from <NONE> to <VARIABLE>
- The Force data to come from the list is optional. If checked, it will not allow user to manually enter values that are not from the database. In most application, this may be desirable. It does have a side-effect
- Click on the
- Select Cust_No from the list and click OK
- Update Table Lookup Values to Link Database Variables to Pick List Data Source – update the properties of each value under Table lookup/Job$, with the exception of the items that are being used for search/filter criteria (i.e. Order Number and Cust_No), to include another criteria – Cust_no = Cust_Number. We have to add extra criteria to the Table lookup for fields being used in the label design so that correct information is pull from the database. If we do not add the extra criteria, the information may be incorrect as the label will pull data from the first record that has matching Order Number.
- Test – make sure the setup works properly (see testing steps below).
- To test the design, select File/Print and select Form tab.
- Initially, the label design will have no data as the information (Order and Customer) has not yet been entered.
- Enter 357834 in Order field. When you click on the down arrow on Customer field, you will see the list of all Cust_No related to the same order number listed.
- As you select the customer number from the list, the information will be populated on the label design on the left.