<img height="1" width="1" style="display:none;" alt="" src="https://px.ads.linkedin.com/collect/?pid=440810&amp;fmt=gif">

New Functions SkipField, BlankValue and IsBlank (version 3.2.91v)

By Michael Bock - June 26, 2015

I am happy to announce that we now have a few new functions available in RapidiOnline. These new functions serve to optimize your use of formulas in the FieldList mapping so that the formulas in general can be shorter. In this way it will not only be easier to write the formulas, but they are also easier to maintain and its easier to quickly get an overview of a particular mapping.

New Function IsBlank(expr)

The IsBlank(expression) function takes a field or an expression and return 1 (or true) if that field is blank or null or if the expression evaluates to a blank or null value.

Some examples:

IsBlank("Fax No.")    --->   1 (true) if the field "Fax No." is blank or null, otherwise 0 (false)

IsBlank(DBLookup('DESTDS','User','Alias',"Salesperson",'Id'))   ---> 1 (true) if the DBLookup function does not find a User for this Salesperson, otherwise 0 (false)

The formula
##IF(EQUALS(TAXGROUP,''),0,1) 

can now be written like

##IF(ISBLANK(TAXGROUP),0,1)  or even shorter like ##1-ISBLANK(TAXGROUP)


New Function BlankValue(expr, expr2)

The BlankValue(expression, expression-if-blank) returns "expression-if-blank" if the "expression" evaluates to blank or null, otherwise it returns "expression". The function can be used instead of a combination of IF and EQUALS functions and generally make the overall formula shorter and easier to read.

Some examples:

BlankValue(TAXGROUP, 'LOCAL')     ---> returns 'LOCAL' if the value of the TAXGROUP field is blank or null, otherwise it returns the value of the TAXGROUP field.

BlankValue(DBLookup('DESTDS','User','Alias',"Salesperson",'Id'), DBLookup('DESTDS','User','Alias','MB','Id'))   ---> returns the Id of the User with alias as in the Salesperson field if this user exists in the destination, otherwise it returns the Id of the User with alias "MB".

The formula
##IF(EQUALS(DBLookup('DESTDS','User','Alias',"Salesperson",'Id'),''), DBLookup('DESTDS','User','Alias','MB','Id'),DBLookup('DESTDS','User','Alias',"Salesperson",'Id'))
   

can now be written as 

##BlankValue(DBLookup('DESTDS','User','Alias',"Salesperson",'Id'), DBLookup('DESTDS','User','Alias','MB','Id'))

 

New Function SkipField()

The new function SkipField() is used to tell Rapidi to skip this field entirely and not send it to the destination system.

The function can only be used with the following destination systems: Salesforce.com, MS Dynamics CRM, MS Dynamics NAV WebService or MS Dynamics AX WebService.

The function is very usefull if you want to set a field only when you have a value for it, but otherwise you want to leave the field as it is in the destination (or let the destination system generate a default value for the field). This can be the case for the OwnerId field in Salesforce.com or for the productid on invoicedetail in MS CRM. The SkipField() function is typically used in combination with BlankValue or IF functions.

Some examples:

SkipField()   ---->  will omit sending the field to the destination system.

The formula
##BlankValue(DBLookup('DESTDS','User','Alias',"Salesperson",'Id'), SkipField())

will send the Id of the User with alias as in the Salesperson field only when this user exists in the destination, otherwise it will skip the field and not send it to the destination at all (leaving the value that is already set or letting the destination generate a default value if it is a new record). This could be used with MS Dynamics NAV Customer as source and Salesforce.com Account as destination.

The formula
##IF(EQUALS("Type",2), DBLOOKUP('DESTDS','product','productnumber',"No.",'productid'), SkipField())

will send the productid corresponding to item "No." to the destination for lines with Type = 2 and it will skip the field for all other lines. This could be used with MS Dynamics NAV invoice lines as source and MS Dynamics CRM invoicedetail as destination.

These new functions are available from version 3.2.91v. You need to upgrade both the central and the RapidiConnectors to this version (or a later version).

Please contact our support to get this upgrade.

Thanks

Michael

Stay Tuned for more Product Updates


About the author

Michael Bock

Picture of
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.


SHARE