We have made a number of changes to the Formula system, in particular addressing the 'DBLookup' functionality. Below you will find a summary of these changes and when they were introduced.
Formula error checking and messages improved
Error handling has been enhanced to provide better and more descriptive error messages. The error messages now include the entire formula that is failing. This should make it much easier to find and correct the problem.
We have also added checks to the number of parameters supplied for each formula. This might result in that you start getting errors from formulas that seemed to work before. In reality, the formula would not have worked, but just silently failed to produce the correct result. Now you will get an error message if the number of parameters is not correct.
Using the Message function resulted in transfer failures when using RapidiConnector version 3.2.90a or later. This has been fixed and the Message formula now works again proficiently.
We also fixed a problem with 'DBLookup' (which was introduced around version 3.2.90a) that resulted in the RapidiConnector crashing. This was the result of an error in the DBLookup formula (if for example the arguments for the table name or field name were wrong). These errors are now correctly handled and reported. At the same time we also enhanced the timing of the error reporting to check and report these errors right away instead of continuing to read from the source system - in this way you will get the error reported as quickly as possible.
These above changes were introduced in version 3.2.91q
The functions CASE and IF now also have better error messages in the event of missing arguments. This change was introduced in version 3.2.91v.
DBLookup now works with any Connection (not just 'SOURCEDS' or 'DESTDS')
The first argument to DBLookup is the Connection (or "DataSource" as it was called earlier). You can either enter 'SOURCEDS' or 'DESTDS' - allowing the DBLookup use the current Source or Destination Connection or you can specify any other Connection that you have setup under Connections (like NAV001 or MSSQL001).
The issue was when using a Connection other than SOURCEDS or DESTDS, the DBLookup would not reuse the Connection correctly which resulted in a "DataHandler already in use" error if the DBLookup was executed more than one time - for example if more than one record was read from the Source.
This problem has now been fixed and you can now use any Connection with DBLookup and as many different connections as you wish.
Example of DBLookup: ##DBLookup('MSSQL002','Contact','CustomerNo',"No",'Name')
Use 'SOURCEDS' or 'DESTDS' when possible
However, 'SOURCEDS' or 'DESTDS' synonyms shouls still be used to access the current Source or Destination Connection. If not, you will get a "Connection already in use" error as these Connections are in use by the Transfer itself.
Ultimately, it is highly recommended that you use 'SOURCEDS' with DBLookup as this normally performs a lot better than using other Connections. This is specially true when reading data through the RapidiConnector.
Example of DBLookup using SOURCEDS: ##DBLookup('SOURCEDS','CONTACTPERSON','CUSTACCOUNT',"ACCOUNTNUM",'NAME')
We have also fixed an issue with DBLookup when using 'DESTDS' combined with the Mirror Technology to read from the Source. The Mirror would then return that all records were changed each time. This has been fixed and the Mirror now correctly ignores the DBLookup using 'DESTDS' as it should. This error was introduced around version 3.2.90a.
The above changes were introduced in version 3.2.91y and you need to be on this version or later on both the Rapidi Central Service and on the RapidiConnector(s).
Please contact our support team to arrange an upgrade.
Best Regards
Michael
