DBLOOKUP2NOCACHE

Description

Searches two (lookup) fields in a database table for specific values and returns the value from another field in the record. This function is similar to the DBLOOKUPNOCACHE function which accepts one lookup field value.

This function avoids issues associated with the DBLOOKUP function's caching strategy. Only use this function if the value to be looked up may change during execution.

Warning: Only use this non-cached version of DBLOOKUP as a last resort because of its potential negative impact on performance.

Syntax

DBLOOKUP2NOCACHE ( SourceField1, SourceField2, "connection", "table", "lookup_field1", "lookup_field2", "substitution_field" )

Parameter	Description
SourceField1	The value for which to search in the lookup_field1 column.
SourceField2	The value for which to search in the lookup_field2 column.
connection	The connection where this lookup table resides. If the connection is Scribe Internal Database, the SCRIBE user must have at least SELECT privileges on the lookup table.
table	The name of the lookup table.
lookup_field1	The name of the column in the lookup table that stores the first value to search for.
lookup_field2	The name of the column in the lookup table that stores the second value to search for.
substitution_field	The name of the column in the lookup table that stores the value to return.

Remarks

Note: This function requires special handling when working with bulk operations. See Using Bulk Operations for more information.

When updating the same target row from several source rows, there may be a need to append information to target columns instead of just overwrite/update them. Normally, this can be achieved by using DBLOOKUP/SQLQUERY, however:

SQLQUERY does not work with the adapter for Dynamics CRM.
DBLOOKUP does not see previously appended data
You may have to change the shutdown interval for the message processor to shut down after every run, to force DBLOOKUP to drop the connection.

Note: The DBLOOKUP2NOCACHE function returns only the first match. If there could be multiple records matching your search criteria, use the DBLOOKUPLIST function to return a comma-delimited list of results.

Examples

The following formula searches records in a table in the target database for PHONE and POSTAL fields whose values match "603-622-5109" and "03110", respectively, and then return the record's COMPANYNAME field value:

DBLOOKUP2NOCACHE("603-622-5109", "03110", "Target","PHONE","POSTAL","COMPANYNAME"

If source fields S4 and S7 contain the phone number and postal code values you need to match, you could enter the formula as:

DBLOOKUP2NOCACHE(S4,S7,"Target","PHONE","POSTAL","COMPANYNAME"

which would produce the same result.