Lookup datasources provide a convenient way to make a connection from the currently linked database or document to a filename, translation list, or other database. These connections are made on the basis of a key field in the datasheet to dynamically lookup the data in the lookup datasource.
You define, modify, or delete a lookup datasource, by clicking Lookup in the Functions group on the Datasources tab. In the menu that appears you can select an existing Lookup datasource or select New.
This article describes:
Database lookup datasources enable you to retrieve data from another (additional) database over a key field in the linked database. This makes it possible to lookup e.g. the name and address of a client over a customer number, find an article description over a reference number etc.
To set up a database lookup datasource:
In the Lookup window, specify a name for the Lookup datasource in the Name box.
Select Database as the Lookup type.
Select the datasource you want to use a the key for the lookup in the Lookup key list. This could e.g. be a foield containing an article or customer number.
In the Database group select the database containing the data that you want to find and the field in that database with the key that should match the data in the Lookup key.
In the Result fields list you can select one or more fields you want to retrieve from the lookup database when a match is found. You may also define a default value to be returned when there was no matching value found.
When you have finished defining your database lookup datasource, the program will add as many columns to the datasheet as you have selected as result fields containing the data from the lookup database.
When you need to replace, or translate, a code by a description, and you do not have a database available that contains that description, you can use a Value list lookup to do so.
A Value list is basically a set of codes with a description linked to each code. You can create the Value list within the program. Value lists you define are stored in separate files and are project independent. This means that you only have to define your Value list once; you can than use the same files over and over again for different projects using the same codes.
To define a Value list lookup:
In the Lookup window, specify a name for the Lookup datasource in the Name box.
Select Value list as the Lookup type.
Select the field you want to use a the key for the lookup in the Lookup key list. This could be e.g. the field containing a code for the title.
Select the Value list you want to use for your translation. When you click Select, the program shows the Value lists that are available.
You can edit, create and delete lists using the buttons on the Value lists screen.
Each code constists of a Value and a Result. You can combine two or more values with the same result in one code by typing a semicolon ( ; ) between the codes.
When you have finished defining your Value list lookup datasource, the program will add a columns to the datasheet containing the data from the Value list. When there is a value in the lookup key field for which there is no translation available, the program will leave that cell blank.
File name lookup is especially useful when you you are adding variable graphics to a design. With this type of lookup you can easily translate a value is a field to a full file name that can be used to display an image. Since the translation is dynamic (i.e. evaluated for each row in your dataheet) it enables you to change the displayed picture file based on a value in your database.
To define a File name lookup:
In the Lookup window, specify a name for the Lookup datasource in the Name box.
Select File name as the Lookup type.
Select the datasource you want to use as the key for the Lookup in the Lookup key list. For File name lookups this means that this is the part of the file name that dynamically changes.
In the File name group select the folder containing the files that you want to use.
Select one of the pre-defined search patterns that will result in the file name based on the selected Lookup key field in combination with wildcards.
If you want to make the seach pattern more specific, click Modify. In the File mask window that appears, you can modify the file mask, review the generated file name for each value in the lookup key field and even select particular files for individual values.
If you want to, you may specify a Default file to use in case the generated file the could not be found.
When you have finished defining your File name lookup datasource, the program will add a column to the datasheet containing the generated file names.