DBLOOKUP vs FILELOOKUP in Scribe Insight


DBLOOKUP

DBLOOKUP searches a column of a database table for a value and returns the corresponding value from another column in the same row.

Syntax

DBLOOKUP(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

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.

To optimize performance, if DBLOOKUP is called again with the same parameters, it returns the same result (without performing another lookup).

If you are migrating a large number of records into a target, you might want to use DBLOOKUPCached. The working of DBLOOKUPCached is similar to DBLOOKUP just that it caches all the values for the job which can greatly optimize your performance.

Below is actual difference between the two.

  • DBLOOKUP: Caches only one value at a time (e.g. if you ask for the same value; you’ll get the cached value – if you ask for a different value, that new value will be retrieved [and cached; removing the previous value cached]).
  • DBLOOKUPCached: Unlike DBLOOKUP, caches more than one value (so that each value asked for is always cached [can have some memory implications if you do lots of lookups]).

We had a scenario where we imported around 20k records in CRM. When we replaced the DBLOOKUP with DBLOOKUPCached, the time required for the import reduced by almost 50 per cent.

If we have two fields that we need to look on, we also have DBLOOKUP2 which takes two fields for the lookup. Please find more details here.

Examples

The following example uses this table named TITLE_CODE in a target database, which has the connection name of Target.

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

This function returns “Manager”:

DBLOOKUP(“3”, “Target”, “TITLE_CODE”, “Code”, “Value”)

FILELOOKUP

Description

Searches the left column of a cross-reference file for a value and returns the corresponding value from the right column.

Syntax

FILELOOKUP(TextSourceField, “filename“, “section“)

Parameter

Description

TextSourceField The value for which to search in the left column. The search is case sensitive.
filename The name of the cross-reference file. This file is a text file, structured like a Windows *.INI file. If the file is not located in the same directory as the DTS file, you must enter the full path (using the UNC) to the file.
section The section name within the file that stores the cross-reference values. The section name is enclosed in square brackets in the *.INI file.

Remarks

Useful for substituting full values for codes or vice versa.

Examples

The following example uses this cross-reference file named XREF.INI.

[Title_Code]

1=Owner

2=President

3=Manager

4=Executive Director

5=Principal

This function returns Manager:

FILELOOKUP(“3”, “XREF.INI”, “Title_Code”)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s