Reducing SFDC API calls when DBLookup std Pricebook (version 3.2.91a)
By Michael Bock, Founder & CEO - January 21, 2015
During our testing, we have found a performance problem with a DBLookup formula that we use in our standard Integration configurations with Salesforce.com. The problem is that the use of the formula would result in many extra API calls to Salesforce.com and hence slow down performance and use up to many API calls on SFDC side.
The problem is when using the following formula in a Transfer: DBLookup('DESTDS','Pricebook2','IsStandard','True','Id');
Normally the result of a DBLookup formula is cached and the second time the formula is executed with the same parameters, it will just find the result in cache and not make any additional calls to the target system.
The issue here is that the field 'IsStandard' is a boolean field in Salesforce and when salesforce returns a record as response for the DBLookup request, the boolean value is translated into an Integer field with value 1 internally in RapidiOnline. This is then the field value that is saved in the DBLookup cache.
When the central Rapidi service checks the cache for an existing record, it checks with the text value 'True' (from the DBLookup formula) and it finds nothing (until now at least).
This would result in continued requests to SFDC each time the formula is evaluated (two times for each record in the typical way we use this formula when adding or updating SFDC PricebookEntry records).
In the new version 3.2.91a, we now use a special compare function (when checking the DBLookup cache) that will accept that a numeric value of 1 is the same as the text 'True' for a boolean field. Now the record is found in cache and no extra calls to SFDC is made.
As a workaround (if you do not want to upgrade the central service), you can use a changed DBLookup formula like this: DBLookup('DESTDS','Pricebook2','IsStandard','1','Id')
This works just as fine, but generally we would prefer if you upgrade the central service. In that way you benefit from many other optimizations and new features (and you dont need to change anything in the configuration setup).
Michael founded Rapidi on technological excellence, fantastic customer service and continuous improvement. A data integration specialist since 1987, he remains focused on creating technology that solves real business problems.