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 users to tap into this data and integrate it into this 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 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 at the top right corner of the lookup table. To edit an existing lookup, select the lookup you wish to edit and click the pencil icon. 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 profile, and is only used for display and identification purposes.
Connection - Select an available connection from the list to use for querying external data. After selecting a connection, the list of queries available will be populated.
Query - Select an available query to use as the key field return. NOTE: The field mapping portion of the dialog will only be displayed after selecting a valid connection and query
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.
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.
Lookup Execution Options - The lookup execution options lets users 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. Users can setup multiple lookups that share the same or overlapping field sets for their key and mapped value fields, with each lookup pointing to a different external data source. The lookups will be run in the order they are defined in the field mappings table. Using the options defined here, users can ensure that the lookups will only be run until one of them provides a valid result record.
Execute Always - 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 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.
None - Wildcard characters included in key field metadata will not be processed. This is the default behavior.
Allowed - 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.
Implied - 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.
Field mappings take a field defined by the document type and map it to a field defined by the Connections. Multiple fields from the document type can be mapped, but each field can only be mapped to a single index 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.
Mapping Type - This option allows users to map the field to the document profile or to a specific Index field value.
Index Field - Select the index field to map the query to. 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 to map the index field to. How the field is used within the lookup operation is dependent on the key and return field options selected below.
Key - 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:
None - The index field should not be used as a filtering key field in the lookup query. This is the default behavior.
Required - The index field is a required part of the lookup query and will always be included.
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.
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 - 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:
None - 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.
Display Only - 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 - 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:
None - Use the return field data as it is returned, with no white space trimming enable enabled. This is the default behavior.
Trim Start - Remove all white space from the beginning of the returned field value.
Trim End - Remove all white space from the end of the returned field value.
Trim Both - Remove all white space from both the beginning and the end of the returned field value.
Removing a Lookup
To remove a lookup from the document profile, select the lookup to delete and click on the "x" icon to the right of the lookup name.
Removing a lookup is a permanent and unrecoverable operation, if a user wishes to restore the lookup to the document profile it will need to be recreated. NOTE: The Connection used 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 a user wishes to reorder the lookups for the document profile, select a lookup to move and use the Up and Down buttons in the upper right corner of the table.
Repeat this for each lookup that needs to be reordered, and save the document profile.