Views:

Product: LABELVIEW, CODESOFT

Version: All Versions

 

OVERVIEW 
The table lookup feature allows you to connect to another data source, other than the one merged with the current document. From that data source, you can select the table and the field (called the “result” field) that contains the data you are looking for.
To obtain the data from the result field, you identify a key field in the external database and a key field (an existing variable) in the merged database that will allow the result field to be displayed.

PROCEDURE 
The procedure for setting up table lookup variables is similar to that for creating database variables. You will be connecting to an external database using the ODBC Administrator. This requires installing a driver if necessary, selecting a data source and associating it with a database. Once the connection is made, you define the table and field from which you want to extract data. You then associate the data with a key field and a key value (variable) in the current document.

Table lookup variables are classified according to the table name from which they are extracted. The table name appears under Table lookup (in the Data sources tab of the Document Browser). If you have multiple variables from the same data source, you can add them directly by right-clicking the table name. The properties dialog box for the variable will be pre-configured with the data source information.

1. In a new or existing label, create a When Printed variable that will be used at print time to retrieve the data for your label.

The When Printed variable is needs to be a part of the data source that all of the rest of the information is a part of. In this example, the When Printed field is Serial Number, which is a field in the data source we will be connecting to.

NOTE: The process for creating a When Printed variable can be found here. 

2. In the Data Sources window, right-click Table lookup, and then click Add.

3. The Table Lookup window will open. Name the Table Lookup being created. This could be named the intended result field in order to easily know what is being referenced.

For example, we will name this Color and have it display the Color based on the When Printed field.

4. In the Table lookup tab, select a data source that you want to use. A new data source could also be created as well. 

NOTE: The process for connecting to a data source can be found here. 

5. In the Select table box, select the appropriate table from your data source.

6. In the Select result field, select the field you'd like the Table Lookup to return. 

In this example, we'd like the Table Lookup to return Color.

7. Now a Key field needs to be created. Locate the Append row button and click on it to create a Key field.

8. In the Key field column, select the reference item from the data source. This is the field in the external table in which the search will be carried out.

This is what the Key value will be compared to. In this example, the name of the database column is Serial.

9. In the Key value (data source) column, select the data source whose value will be compared against the value in your database.

Another way to think of this is what is the name of the "When Printed" field that the search information is based on. In this example it is Serial Number. 

NOTE: The Use format value with the key-value checkbox (Under Data formatting tab) allows the use of the variable value with its formatting (prefix, suffix…) as the key value.

10. In order to make sure the information is coming through correctly click on the Test button, located in the lower right corner underneath the Key Field and Key Value information box.

11. There will be another pop out box for the returned Test information to be displayed. The pop out box will need to be closed whether the set up needs to be updated or the information is correct.

12. If the information is incorrect from the Test, update any of the fields that might be incorrectly set up and try the Test again. When the Table Lookup is working correctly, click OK.

13. The table name appears under Table lookup in the Data sources window.

14. The Table Lookup variable can now be displayed on the label. Highlight the name of the Table Lookup (Sheet1$: Color) and right click. Select Place. Click on the label where this variable is desired. Then select how it is to be displayed: Text, Barcode, or Image.

15. This variable is now displayed on the label and will change based on the Serial Number entered into the Serial Number When Printed field.

 

Keywords: table lookup, database table, labelview table lookup, codesoft table lookup