Lookup Block
Use a Lookup Block to retrieve data from selected fields within an Entity according to the lookup criteria that you specify. This Block retrieves data to use in a formula or to populate a field in another entity that may not be in your initial query. See Lookup Block Example for a detailed use case scenario.
Note: If you have enabled Batch Processing on any Blocks in your Map, Lookup Blocks are not recommended. Using Lookup Blocks causes batching of records to be interrupted and reduces overall performance. See Lookups And Batch Processing.
Block Properties — General Tab
From the Block Properties General Tab, you can change the Block Label, add a description, enable caching, and select the entity to use for this operation.
Caching
Improve the performance of the Map by enabling the caching option labeled, Cache the results of this operation. When enabled, the Agent caches the Lookup results until the end of the Map. The results data is available for reuse without having to retrieve it again.
The Agent caches the data as it is looked up instead of caching all of the data in the entity being searched. For example, assume you are running a query that has to do with currency. When the Map needs to know the value of a specific currency, such as US Dollars, the Lookup operation requests the value of that single currency and caches it. When the Map needs to know the value of Euros, the Lookup operation requests the value and caches it along with the value of US Dollars. The cache grows as each new piece of information is added. When a request is repeated, such as asking for US Dollars a second time, the Map uses the cached data.
When data changes during the time the Map is run, the Map uses the cached data, and the modified data is not used. If the values of the data retrieved by the Lookup Block might change during the execution of the Map, you may not want to enable caching. The default setting for this option is disabled.
Note: If the Lookup results are very large, they may not be cached. The maximum size of the cache is 10 MB.
Block Properties—Lookup Criteria Tab
Use the Lookup Criteria tab to specify one or more criteria to find the record in the target that corresponds to the current source record.
- On the Lookup Criteria tab, select Add
. A lookup criterion row displays. - Select the blank cell under Field to display a list of the fields in the entity available for the lookup. Select the field to look up from the drop-down list.
- Under Operator, select an operation, such as equals.
- Under Value, enter a value to match for the lookup. TIBCO Scribe® Online uses this value to match records between the source Entity and the Entity specified for this Block.
On the Value field, select the Formula icon to open the formula editor. Use the formula editor to include a formula, another field, or a previously specified operation. See Formula Editor.
- If needed, select Add again to create an additional lookup criterion.
After you select Add
, the And/Or column displays. Select and or or from the drop-down list to determine how to combine lookup criteria. When the Map runs, the criteria is evaluated in order from top to bottom. - To delete a row, select the row and from the Gear menu
select Delete. - When you are done, continue defining this Block or select OK to return to the workspace.
Tip: The Lookup Block is not supported with the Text Source Connector. Use the Fetch Block instead.
Note: When using a formula for lookup criteria, the rules are similar to those for filtering criteria. For more information, see Filtering Notes.
Lookup Criteria Examples
In the examples shown below, TIBCO Scribe® Online looks up Contacts that meet the selected criteria for use later in the Map.
Example 1
|
And/Or |
Field |
Operator |
Value |
|---|---|---|---|
|
|
CompanyName |
equals |
Customers.CompanyName |
|
and |
Active |
equals |
TRUE |
|
or |
ContactName |
equals |
Customers.ContactName |
These settings are interpreted by TIBCO Scribe® Online as follows:
((CompanyName=CompanyName AND Active=TRUE) OR ContactName=ContactName)
To include the Contact in the lookup, one of the following must be true:
- CompanyName field must be the same AND Active must be set to TRUE.
- ContactName field must be the same.
Example 2
|
And/Or |
Field |
Operator |
Value |
|---|---|---|---|
|
|
ContactName |
equals |
Customers.ContactName |
|
or |
Active |
equals |
TRUE |
|
and |
CompanyName |
equals |
Customers.CompanyName |
These settings are interpreted by TIBCO Scribe® Online as follows:
((ContactName = ContactName OR Active=TRUE) AND CompanyName = CompanyName)
To include the Contact in the lookup, both of the following must be true:
- ContactName field is the same or Active is true.
- CompanyName field is the same.
Block Properties — Field List Tab
Use the Field List tab to specify the fields you want returned for every record that meets the lookup criteria. The fields you specify are available to use in formulas in subsequent Blocks. Select only the fields that you need to reduce the time needed to return the data to the Map.
Note: You must select at least one field in the Lookup Block.
Selecting Fields
From the Field List tab, select the checkbox for each field you want to return. The following rules apply:
- To select all fields, use the Select link above the checkboxes.To select most fields, use the Select link, and then clear the checkboxes for the fields you do not want to include.
- To clear your selections, use the Deselect link above the checkboxes.
- You can filter on Field name, Data Type, or Description. Select the Filter icon (
) to edit the Filter dialog, as described in Using The Filter Dialog.Note: The Select link selects every field including those that are not displayed when a filter is used.
Block Properties — Error Handling Tab
Use the Error Handling options to enable or disable processing this record when a Block operation encounters a problem.
For the Lookup Block, errors can be logged as follows:
- When an operation fails — If the Lookup operation fails, an error is logged.
- If no matches are found — If the record being processed does not match any target records based on the information in the Lookup Criteria Tab, the Agent logs an error.
- If one or more matches are found — If the record being processed matches one or more target records, the Agent logs an error.
- If more than one match is found — If the record being processed matches more than one target record, the Agent logs an error.
See Block Properties Error Handling Tab for additional information.
Note: If your lookup criteria finds more than one matching record, and you do not flag this as an error condition, TIBCO Scribe® Online only returns data from the first record that matches your criteria. There is no way to determine which one of the multiple records is returned first to TIBCO Scribe® Online, which could cause the wrong record to be used in subsequent Blocks.
Block Properties — Errors And Warnings Tab
If there are any errors or warnings for this Block, the Errors and Warnings tab displays the number of errors or warnings in the tab name. See Block And Map Errors And Warnings.
Once you have corrected the errors, select the Validate button to update the Errors and Warnings tab.
