Typical businesses collect, produce and maintain data across various systems that can often be leveraged to reduce or eliminate the manual keying necessary when indexing documents. Lookups are an optional feature within each document type that allow you to tap into this data and integrate it into your thin client indexing solution.
Lookups use shared connections and queries from the PSIfusion organization to connect to external data sources. If you have not yet configured any connections, or are unfamiliar with connections and queries within PSIfusion, please refer to the Connections & Queries article.
Each lookup uses key fields, return fields and lookup options to shape the query that is built, as well as how the results are handled by the indexing client application. During indexing, index operators providing metadata values for lookup key fields either by using imported values, default values or manually keyed values; and the system then builds and executes the lookup query and automatically populates mapped return fields with the query results, using the defined lookup options to determine which records to apply.
Adding or Editing a Lookup
To add a new lookup click the Add button on the lookups tab. To edit an existing lookup, select the lookup you wish to edit and click the edit button. Both adding and editing a lookup share the same dialog, as seen in the screenshot below.
Enter a unique name for the lookup. The lookup name is only required to be unique within the current document type, and is only used for display and identification purposes.
Connection and Query
Select an available connection from the list, corresponding the data source you wish this lookup to use for querying external data. After selecting a connection, the list of queries available will be populated. Select an available query and hit the Connect button.
The field mapping portion of the dialog will only be displayed after selecting a valid connection and query and hitting the Connect button. A screenshot of the field mapping controls is available later in the article in the Field Mapping section.
Lookup options help shape the data query that the lookup will run and determines how the application should select and process query results indexing documents.
Record Selection Options
The record selection options setting determines how PSIfusion should handle lookup queries that result in more than one record being returned from the external data source. The following options are available:
Apply the first returned record
The first returned record from the query results will be used to update the metadata. This is the default behavior.
Select the record to apply
If the query results contain more than one record, a dialog will be shown to the index operator where they must select the record that should be applied. When the query returns only one record, the data from the single result record will be applied.
Create an index record for each returned record
Creates an index record on the document corresponding to each returned result record. Any existing records on the document will be reused before adding new records, and the result records metadata will be applied to the document records in the order the results are returned. This option will be ignored when multiple records are not enabled on the document type. Refer to Adding a Document Type for details on enabling multiple records.
Lookup Execution Options
The lookup execution options setting lets you modify the conditions under which the lookup query will be run. This option is useful for controlling the execution of multiple lookups that share one or more document index fields. You can setup multiple lookups that share the same or overlapping field sets for their key and mapped value fields, with each looking pointing to a different external data source. The lookups will be run in the order they are defined, as documented below in Reordering Lookups. Using the options defined here, you can ensure that the lookups will only be run until one of them provides a valid result record.
The lookup will run every time it is triggered, regardless of the metadata content of the return fields. This is the default behavior.
Execute if all returned fields are blank
The lookup will run when all return field values are blank.
Execute if one or more return fields are blank
The lookup will run when any of the return field values are blank.
Wildcard Handling Options
This option controls the query that the lookup will build before executing against the external data source. The available options and their effect are listed below.
Wildcard characters included in key field metadata will not be processed. This is the default behavior.
Wildcard characters included in key field metadata will be included in the resulting query and processed against the external data source. Users may execute explicit searches by entering key values into fields, or execute a wildcard enabled search by entering a partial value with a wildcard character.
All key field metadata values will be modified to be queried as if a wildcard character was included. This enables users to always execute wildcard enabled searches by simply entering a partial value into any key field.
Wildcard characters supported by the lookup query builder include:
Asterisk ( * )
Percent ( % )
Characters in key metadata fields will be converted into the appropriate database-specific character and syntax by the lookup query builder.
The maximum records option specifies the maximum number of result records the lookup query execution will allow. Where applicable, data source specific extensions will be used to limit the query results. For data sources that do not support a limit extension, the results will be queried at the PSIfusion server and limited manually before transporting the result set to the client application.
Field mappings take a field defined by the document type and map it to a field defined by the Connections & Query. Multiple fields from the document type can be mapped, but each field can only be mapped to a single query field. Each field mapping also has several options available that control how the metadata is used to build a lookup query, and upon receiving a result set, how the result metadata is applied to the document type fields.
Field mappings in PSIfusion may be defined as either a key field mapping, a return field mapping, or both.
In cases where a field mapping is both a key field and a return field, the value in the linked index field will be used to build the lookup query, and any values returned for the linked query field will be used to overwrite the original key field value once a result record is chosen.
Select the document type field that should be used on the left side of the mapping relationship. How the field is used within the lookup operation is dependent on the key and return field options selected below.
Select the query field that should be used on the right side of the mapping. How the field is used within the lookup operation is dependent on the key and return field options selected below.
Key Field Options
The key field options setting determines if the document type field referenced by the Index Field setting for this mapping should participate in the lookup as a key field.
Key fields are used to build the query portion of the lookup, that will be executed against the external data source. Key fields may be defined using the following options:
The index field should not be used as a filtering key field in the lookup query. This is the default behavior.
The index field should be used as a filtering key field in the lookup query, if a value is supplied; otherwise the field will not be included in the lookup query.
The index field is a required part of the lookup query and will always be included.
Required vs. Optional Key Fields
A lookup will not be triggered unless one or more key field values have been supplied by the index fields via a field mapping on the lookup.
When a lookup consists of all required key field mappings, all key fields must have a value assigned before the lookup will be triggered.
When a lookup consists of mixed required and optional key field mappings, all required fields must have a value assigned before the lookup will be triggered, and any optional fields that include a value will be included in the query.
When a lookup contains only optional key field mappings, the lookup will be triggered anytime at least one of the mapped key fields has a value. At the time the query is run, any optional key fields with values will be included in the query, and those optional key fields without values will be ignored.
Return Field Options
The return field options setting determines if the document type field referenced by the Index Field setting for this mapping should participate in the lookup as a return field.
Return fields are used to receive metadata queried from the records returned by the external data source. Return fields may be defined using the following options:
The field should not be used as a return field in the lookup query. This is the default behavior.
Map to Field
The field should be used as a return field, with the value from the result field specified by the Query Field setting used to populate the document metadata field.
Map to Field, if Field is Blank
The field should be used as a return field, just as described for the Map to Field setting, however the metadata field data will only it contains no value.
The field will be queried by the lookup query and returned to the client application, but it will not be mapped to any document index fields, and will only be used for display when record selection is required.
Trim Field Options
The trim field options settings enables removal of white space from query return fields, before the return field data is applied to the document index field. The field data is trimmed after the query is run and before the data is sent from the server to the client application. The data in the external data source is never modified. Available trim field options are described below:
Use the return field data as it is returned, with no white space trimming enable enabled. This is the default behavior.
Remove all white space from the beginning of the returned field value.
Remove all white space from the end of the returned field value.
Remove all white space from both the beginning and the end of the returned field value.
Copying a Lookup
Copying a lookup allows you to take a completed lookup, make modifications to it and save the modified lookup using a new distinct name. To copy a lookup:
Select the lookup you wish to use as a starting point for the new lookup
Click the Copy button
Edit the name of the lookup to be a new, unique name, within the current document type
To remove a lookup from the document type, select the lookup you wish to delete from the list of lookups on the lookup tab and click the Remove button.
Removing a lookup is a permanent and unrecoverable operation, if you wish to restore the lookup to the document type you will have to recreate it. Note that the Connection & Query selected for the lookup will not be deleted, and can be reused.
The order lookups are displayed on the lookups tab is also the order the lookups will be executed as the index operator moves through the fields providing key field values. If you wish to reorder the lookups for the document type, select a lookup you wish to move and use the Up and Down buttons to adjust the position.
Repeat this for each lookup you wish to move, and save the document type to persist the changes.