Introduction

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.

Lookup Name

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

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:

OptionDescription
Apply the first returned recordThe first returned record from the query results will be used to update the metadata. This is the default behavior.
Select the record to applyIf 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 recordCreates 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.

OptionDescription
Execute AlwaysThe 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 blankThe lookup will run when all return field values are blank.
Execute if one or more return fields are blankThe 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.

OptionDescription
NoneWildcard characters included in key field metadata will not be processed. This is the default behavior.
AllowedWildcard 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.
ImpliedAll 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.

Max Records

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

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.

For more details on return field mapping, see the section on Return Field Options.

 

Index Field

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.

Query Field

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:

OptionDescription
None

The index field should not be used as a filtering key field in the lookup query. This is the default behavior.

Optional

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.

RequiredThe 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:

OptionDescription
NoneThe field should not be used as a return field in the lookup query. This is the default behavior.
Map to FieldThe 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 BlankThe 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.
Display OnlyThe 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:

OptionDescription
NoneUse the return field data as it is returned, with no white space trimming enable enabled. This is the default behavior.
Trim StartRemove all white space from the beginning of the returned field value.
Trim EndRemove all white space from the end of the returned field value.
Trim BothRemove 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
  • Make any additional changes to the lookup Connection & Query, Lookup Options, or Field Mappings as documented throughout this section
  • Save the completed lookup

Removing a Lookup

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.

Lookup Order

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.

On this page:


In this section:


Related items: