DBLOOKUP(DataSource, TableName, KeyField-1, KeyValue1, KeyField-n, KeyValue-n, ResultFieldName)

With this function it is possible to lookup a value from a specific field in another table.

"DataSource" is the name of the DataSource to be used to connect to the Database. You can either use one of the two predefined DataSource names: SOURCEDS or DESTDS (SOURCEDS will use the current connection (specified on the current Transfer running) to the Source Database and DESTDS will use the current connection to the Destination Database) - or you can create a new DataSource and put the name here.

"TableName" is the name of the Table to use for the lookup.

 

"KeyField-1", "KeyValue-1" are Key field names and corresponding values to be used for the lookup.
"KeyField-n", "KeyValue-n" are optionally the second, theird, forth, etc. Key field names and corresponding values to be used for the lookup. There must always be a matching number of KeyFields and KeyValues. You can also work with no key-fields, key-values, in which case the DBLookup will just retrieve the first record in the specified table.

 

Normally the KeyValue's refer to a field in the current record - like "Code" - but a KeyValue can also be a WHERE Clause to be evaluated by the target system. For example when doing a DBLookup against a MySQL Server. In this case the KeyValue has to start with ## (see example below).

 

"ResultFieldName" is the fieldname of the field in the lookup table, that you want the value from.

 

Return value: The function returns the value of the field "ResultFieldName" in the lookup Database if it finds a matching record. If no record is found, an empty string/text is returned.

 

Tip: When using this function to make lookups in a Navision database, ensure that you have a key in the table that matches the KeyFields specified (in the exact order of the KeyFields). The key may contain more fields, but should start with the fields that you have specified as KeyFields.

 

The Lookups are cached so that a lookup in a table with a given combination of key field values, is only made once to the database; The next time the value is retrived from a list in memory. The caching is done on Transfer level - i.e. when the transfer is done, the cache is deleted.

 

You can use several different DBLookups in the same transfer and you can nest several DBLookup's, but the overall performance might go down.

You should also note that it is a lot faster to do a DBLookup against a database based system like MS Dynamics NAV or MS-SQL Server or Oracle, than doing the same against a WebService based system like Salesforce.com or NetSuite.

 

Examples

DBLOOKUP('SOURCEDS', 'CompanyInfo', 'Name')

 

Return value: This will return the first record (in database specific order) in the CompanyInfo table.

It can be used when there is only one record in a table, for example to lookup some configuration parameters.

 

DBLOOKUP('DESTDS','Salesperson/Purchaser','ExternalID',"OwnerID",'Code')

 

This formula could be used on a Account/Customer Transfer from Salesforce.com to MS Dynamics NAV. It will Lookup the Code of the Salesperson in Navision based on the Owner of Account in Salesforce.com. As the destination field, you would use the "Salesperson Code" field on the Customer. Before this will work, you need to create the field "ExternalID" (Text 30) on the "Salesperson/Purchaser" table in Navision and transfer the ID's of the Salesforce.com Users into this field (you could use the email field as a key).

 

DBLOOKUP('SOURCEDS','orders','orderid',id,'caseid','##caseid is not null','caseid')

 

This formula will do a DBLookup in the table "orders" with a WHERE clause like "(orderid = ) AND (caseid is not null)". It will return the value of the caseid field.