DBLOOKUPCached

Description

Searches a column of a database table for a value and returns the corresponding value from another column in the same row. This function caches all values for the job, which can greatly optimize performance when you have a small number of values in your lookup table.

Syntax

DBLOOKUPCached ( TextSourceField, "connection", "table", "lookup_field", "substitution_field" )

Parameter	Description
TextSourceField	The value for which to search in the lookup_field 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_field	The name of the column in the lookup table that stores the 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.

Useful for substituting full values for codes or vice versa. Similar to DBLOOKUP except that all of the data in the lookup table is cached in memory when processing the first source row. This can improve performance by reducing the number of reads on the database that stores the lookup table. There is a practical limit to the size of the table that can be cached that is determined by the available memory of the computer.

Running DBLookupCached to look for Accounts or Pricebook Entries is unlikely to cache any value, because there will likely be only a few duplicates.
Running DBLookupCached to look for Pricebooks (such as, End User, GP Pricebook, and so on) or Salesforce Users, is more likely to cache values that can be re-used, because there are fewer distinct records in Salesforce to look up.

Examples

The following example uses this table named TITLE_CODE in the target database.

Code	Value
1	Owner
2	President
3	Manager
4	Executive Director
5	Principal

This function returns Manager:

DBLOOKUPCached("3", "Target", "TITLE_CODE", "Code", "Value")