MyRapidi
MyRapidi
Menu

Search our Wiki

DBLOOKUP

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. The lookup can be set up to query data from both source ('SOURCEDS') and destination ('DESTDS'). When building a lookup, you need to make sure that there is a common identifier/denominator between the selected tables.

 

"1. 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.

"2. TableName"

  • Is the name of the Table to use for the lookup. You have to make sure that this table exists in your source or destination system ( depends on the data source that you use - SOURCEDS or DESTDS).

"3. KeyField-1", "KeyValue-1"

  • Are Key field names and corresponding values to be used for the lookup.This would be the field name that you need to query data from and match it with the second KeyField.

"4. KeyField-n", "KeyValue-n"

  • are optionally the second, third, 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 3 below).

"5. ResultFieldName"

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

"6. 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.

 

Important 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.

Statistics from the DBLookup feature 

When a Transfer is running, it will collect some statistics values for the use of the DBLookup function. This statistics is shown on the console and also in the Log after the Transfer finishes.

 

The statistics for using a DBLookup could look like this:

DBLookup Statistics for SOURCEDS+Salesperson_Purchaser+Code: Fields 1, Total Lookups 10, DB Fetch 3 (00:00:01-234), Cache 3 (00:00:00-200)

which means that there is 1 field retrieved by this DBLookup, the DBLookup formula was called 10 times in total, it resultet in 3 lookups to the database (which took a total time of 1 second and 234 thousands of a second) and finally that the DBLookup cache contained 3 values when the transfer finished (and that querries to the cache took a total of 0 seconds and 200 thousands of a second). That the cache contains 3 records indicates that the DBLookup was called with just 3 different values (in this case 3 different Salesperson codes).

 

This Statistics feature is available in version 4.0.01g of the central Rapidi version.

 

 

Example 1:

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

Return value: This will return the first record (in database specific order) in the CompanyInfo table. The data source would be the source system, the table name would be 'CompanyInfo' and the field from that table would be 'Name'.

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

 

Example 2:

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

This formula could be used on a Account/Customer Transfer from Salesforce.com to MS Dynamics NAV. This lookup will do the following: perform a lookup in the destination system ('DESTDS') where the table name is 'Salesperson/Purchaser', the field name is ''ExternalID'. This field from the destination system is matched with the field 'OwnerID' which needs to exist in the source system ( since formulas are always created on the left side in Field List Mapping). Note: This field will always be listed with double quotation marks. And the lookup result between the 'ExternalID' and 'OwnerID', you will get the field value for 'Code.

 

Example 3:

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.

Example 4:

IF(EQUALS(DBLookup('DESTDS','RapidiContactBusinessRelations','Link_to_Table','1','No',DBLOOKUP('SOURCEDS','account','accountid',"_parentcustomerid_value",'accountnumber'),'Contact_No'),''),ERROR('Account does not have a related NAV company contact:'+"_parentcustomerid_value"),DBLookup('DESTDS','RapidiContactBusinessRelations','Link_to_Table','1','No',DBLOOKUP('SOURCEDS','account','accountid',"_parentcustomerid_value",'accountnumber'),'Contact_No'))

This formula is a nested lookup that will do the following: look for the 'No' field in the table ,'RapidiContactBusinessRelations' where 'Link to Table=1'. Then, the result of this lookup will be matched with the second lookup ( which will look for the 'accountid' from the 'account' table, match it with the '_parentcustomerid_value' and get the 'accountnumber'.) and this would translate into 'No'='accountnumber'.If they match, then the lookup result will be the value from 'Contact_No'. If there is no match or it's empty, an error will be thrown.