Home → Using LincDoc 3.1+ → Database Lookups → Creating a New Lookup

19.4. Creating a New Lookup

You can create a new lookup using the new button available from the drop-down list in the lookup column.

Note: Once configured, a lookup is available system-wide, not just in the action in which it was created.

Adding a Lookup and Specifying the Lookup Type

You can add a new individual lookup using the new button available from the lookup drop-down list on the lookup dialog box.

  1. Access the lookup dialog box as described in Configuring a Lookup Action.
  2. In the lookup column at the bottom of the dialog box, click the drop-down list.
    A list of previously defined lookups appears. 
  3. In the upper right corner of the displayed list, click new.


    The New lookup dialog box appears.
     
  4. In the ID text box, specify a name for the new lookup.
    This name will appear in the list of available lookups.
  5. Specify the type of lookup you are creating using the Type drop-down list. The following options are available:
    • Multi Value Field. Allows you to extract data from a multivalue table in the current document.
      Important: This lookup type is only available with LincDoc version 3.2 (and above) and is not currently available with LincDoc Mobile.
    • JDBC Advanced. Allows you to write your own custom SELECT query (which may join two or more existing tables/views) to pull data from a third party database into a LincDoc form. The query can use any syntax that is acceptable for the back end database.
      Important: This lookup type is currently not available with LincDoc Mobile. 
    • JDBC. Assumes that the table or view from which you want to extract form data already exists in your third party database. The data is then extracted and inserted into a running LincDoc form.
  6. Click the add button.
    The lookup's configuration dialog box appears.
  7. Proceed to one of the following sections, based on the lookup type you are creating:

Configuring a Multi Value Field Lookup

Note: This feature is only available with LincDoc version 3.2 and above.

A Multi Value Field lookup allows you to extract data from a multivalue table in the current document. You specify the table that you want to use by selecting any existing field in the corresponding table. No database is used with this type of lookup, unlike the other two lookup types (JDBC and JDBC Advanced).

  1. Access the lookup's configuration dialog box as described in Adding a Lookup and Selecting the Lookup Type.
     
  2. (optional) Provide a description for the lookup in the corresponding text box.
    This text will appear immediately below the lookup's name in the list of lookups.
  3. From the Any field in source table drop-down list, select an existing field in the multi-value table you want to use. This action selects the table for use by the lookup. You can choose any field in the desired multi-value table.
  4. Proceed to one of the following topics for information on using the settings at the bottom of the dialog box:

Configuring a JDBC Lookup

A JDBC lookup assumes that the table or view from which you want to extract form data already exists in your third party database. When the lookup is used, the data is extracted and inserted into the currently running LincDoc form.

  1. Access the lookup's configuration dialog box as described in Adding a Lookup and Selecting the Lookup Type.
  2. (optional) Provide a description for the lookup in the corresponding text box.
    This text will appear immediately below the lookup's name in the list of lookups.
  3. From the Data source drop-down list, select the existing data source (database connection) that the lookup will use.
    For more information on creating and configuring these connections, see Connecting LincDoc to an External Database.
  4. From the Table/View drop-down list, select the table or view in the specified data source that the lookup will use.

    Note: You can also upload data directly from a CSV file to a table using the upload CSV button or export information from the selected table to CSV file using the export CSV button.

  5. From the Sort column drop-down list, select the column in the table that the lookup will use.
  6. From the Sort direction drop-down list, select whether the data in the column will be arranged in ascending (A-Z) or descending (Z-A) order.
  7. (optional) Specify a condition if you want to execute one type of lookup (if the condition is true) and a different lookup under different (defined) circumstances.
    For example, if your company has an expense report where you want to run a different lookup based on the employee type, you could define conditions so that a specific lookup is executed for employees of type "SALES" and a different lookup to executed for employees of type "ENGINEERING". Each employee type would use a different condition.
    1. Click the Condition check box.
    2. Define a single condition or multiple conditions. For more information, see Creating and Editing Custom Conditions.
  8. Determine if you want to allow offline caching. This option applies to forms that are enabled to run on LincDoc Mobile (an iPad). Because forms on an iPad may be run without having a WiFi signal (network connection), it would otherwise be impossible to run the lookup, since the lookup connects to a database somewhere on the network, requiring a network connection. When this option is active, LincDoc Mobile will download the entire table that corresponds to this lookup when form definitions are refreshed on the iPad and a network connection is present.

    Tip: It is recommended that you not use this option with tables that contain more than approximately a few hundred rows.

    1. Click the allow offline caching? check box.
      Additional cache options appear. 
    2. Specify one of the following cache policies from the corresponding drop-down list:
      • timed. Whenever you update forms on an iPad using information from the server, the offline lookups are refreshed. The act of refreshing starts the "timer" for each lookup. At the point that the lookup data is needed (for example, while running the form), this time is evaluated to determine whether the data for the lookup needs to be refreshed. If the iPad is online, the data will be refreshed and the timer reset before the lookup is run. If the iPad is not online (not connected to WiFi and the network), the lookup simply uses the existing offline data, and attempts to refresh the data the next time it is needed or the next time the forms are updated from the server.
      • userSession. A timed lookup, as described above, with a pre-determined duration of 24 hours.
  9. Proceed to one of the following topics for information on using the settings at the bottom of the dialog box:

Configuring an Advanced JDBC Lookup

An Advanced JDBC lookup allows you to write your own custom SELECT query (which may join two or more existing tables/views) to pull data from a third party database into a LincDoc form. The lookup's query can use any syntax that is acceptable for the back end database as well as DRAT expressions. 

  1. Access the lookup's configuration dialog box as described in Adding a Lookup and Selecting the Lookup Type.
  2. (optional) Provide a description for the lookup in the corresponding text box.
    This text will appear immediately below the lookup's name in the list of lookups.
  3. From the Data source drop-down list, select the existing data source (database connection) that the lookup will use.
    For more information on creating and configuring these connections, see Connecting LincDoc to an External Database.
  4. In the large Query dialog box, define a standard SQL SELECT statement. Each column returned by the query can then be mapped to a LincDoc field, as described below.
    For complete details, see Defining a Lookup Query.
  5. Proceed to one of the following topics for information on using the settings at the bottom of the dialog box:

Automatically Mapping Lookup Fields

You can use the auto-map button to have LincDoc attempt to automatically map the database columns or multi-value table entries (based on your lookup type) returned by the specified query to fields in the current form.

This feature works by comparing LincDoc fields in the current form with database columns returned by the lookup. For any returned column, if there is a LincDoc field with the same name (independent of case), the column is automatically mapped to the field.

Tip: If you use this option, it is highly recommended that you check the results of the automatic mapping. If necessary, you can edit the automatic mappings as described in Manually Configuring Lookup Mapping and Prompt Columns.

Once the automap operation is complete, click save at the top of the dialog box.

Manually Mapping Lookup Fields

You can manually map the database columns or multi-value table entries (based on your lookup type) returned by the specified query to fields in your LincDoc form using the Mapping tab at the bottom of the lookup's configuration dialog box.

Tip: At any time, you can click the refresh button (near the middle of the dialog box, above the Mapping tab) to verify that you are viewing the most up-to-date database or multi-value table information.

  1. Verify that you have configured the basic lookup settings and specified the type of lookup you are creating as described in the appropriate sections above.
  2. On the Mapping tab at the bottom of the dialog box, specify the relationships between the database columns/multi-value table entries and the form's fields.
    1. Click and drag items from the lookup fields column (left side of the tab) to the correct item in the forms fields column (right side of the tab).
      A green check mark icon appears if the mapping is valid.
       
    2. Release your mouse button when your mouse pointer is over the correct items in the form fields column.
      The mapped lookup field appears in the lookup fields column on the right side of the tab when the mapping is complete.

      Note: You can also remove mapping by dragging entries in the lookup fields column on the right side of the tab back to the lookup fields column on the left side of the tab. A mapping is removed once the entry in the lookup fields column on the right side of the tab is blank. 

  3. Repeat this process until all of the fields are mapped to your satisfaction.
  4. At the top of the dialog box, click save.

Configuring the Prompt Columns Settings

Note: This feature is only available with LincDoc version 3.2 and above.

The Prompt columns tab allows you to sort and specify the data that will be shown to the user during the data entry process, allowing the user to chose the desired row of data that will be mapped into the running form from among several options. These prompts differ based on the current (selected) lookup.

  1. Click the Prompt columns tab.
    The currently mapped fields are displayed, allowing you to select which ones will be used as prompts.
  2. Using the check boxes on the left side of the tab, select the mapped fields that will appear when the lookup is performed.
  3. (optional) Add text to the label column for each selected entries.
    When defined, the label text will appear in the lookup instead of the field name (as it appears in the lookup fields column).
  4. (optional) Reorganize the list of fields by clicking and dragging the entries in the lookup fields column.
    A green check mark icon appears when the item is being moved, and shows you that the current location (where your mouse pointer is hovering) is an acceptable new location.
     
  5. At the top of the dialog box, click save.

This page was: Helpful | Not Helpful
← 27.3. Creating an Email Template