2022
Function | Description | Examples |
ADDXML(String xml) | This function adds xml strcuture to the current element. | { ADDXML("<CustomField><DefinitionId>2</DefinitionId><Name>SalesRep</Name><Type>StringType</Type><StringValue>"+VALUE("SalesRep__c")+"</StringValue></CustomField>"); return ""; } Where→<DefinitionId>2</DefinitionId> :Position of custom field in QuickBook online. <Name>SalesRep</Name>: The custom field name in QuickBook online. <StringValue>"+VALUE("SalesRep__c")+"</StringValue>: The custom field in SalesForce. |
ADDXML(String xPath, String xml) | This function appends xml strucutre to the xpath passed. | |
AND (boolean booleanExp1, boolean booleanExp2) | This function used to check more than one condition/expression at the same time and returns true only if both of conditions are satified; else returns false. | Mapping: <TargetField> = AND(booleanExp1,booleanExp2) Example: Salesforce object fields Trigger: select Id, firstname, lastname from contact The above query fetches Id, firstname, and lastname from the contact object of Salesforce. You can use AND() function to check whether firstname, lastname have same text value and Id of 123. If both the conditions are satisfied then AND() function returns true, else returns false. <TargetField> = AND(VALUE("firstNname")==VALUE("lastname"), VALUE("Id")=="123") |
ASNUMBER(String value) | This function checks if a supplied value is a number and returns the actual passed number; else retruns 0. | MAPPING: <TargetField> = NUMBER("String") |
CLEAN(String s1 string s2) | This function removes all non-printable characters from a supplied string value. | Mapping: TargetField = CLEAN("String VALUE") |
CONCATENATE(String... strings ) | This function allows you to join two or more text strings together. | |
CODE(String s) | This function returns the numeric code of the first character of a supplied text value. | Mapping: <TargetField> = CODE("text value") |
DATE(String dateString) | This function reads a date string and returns it in the format of "yyyyMMdd-HHmmssZ". | |
DATE (String dateString, String inputFormat) | This function reads a date string, user specified date format and returns the date string as per user specified date format. If user specified date format is not passed then it passes date string as "yyyyMMdd-HHmmssZ". | Mapping: <TargetField> = DATE(VALUE("source field"), "format string") |
DATE(String dateString, String inputFormat, String outputFormat) | This function reads a date string, user specified date input format, user specified date output format and returns the date string as per user specified date output format. If the user specified date input format is not passed then it returns date string as "yyyyMMdd-HHmmssZ". | Example: Database query Trigger: select contactname, createddate from dbcontact The above query will select contactname, createddate field from dbcontact table of the specified database. <TargetField>= DATE(DATE(VALUE("CreatedDate"),"yyyy-MM-ddhh:mm:ss"),"yyyy-MM-dd'T'HH:mm:ss'Z'") The value of createddate is 03-12-2010 12:33:21, then the DATE() function will return value as 03-12-2010 12:33:21( output format is wrong) |
DOLLAR(Arg 0) | Not implemented yet | |
EQUALS(String v1, String v2) | This function compares two supplied string values and returns true if both of the values are exact match otherwise false(case-insensitive). | Mapping: <TargetField> = EQUALS(VALUE("CurrencyISO"),"USD") This function compares USD in CurrencyISO field for USD value and is found then it returns true; else false. |
ERROR() | This function can be used to print error messages to the DBSync console. The function when applied to a target field, returns error messages from the stack trace due to integration failure in user readable format. | Mapping: <TargetField> = ERROR() |
EXACT(String str1, String str2) | This function compares two supplied string values and returns true if both of the values are exact match otherwise false(case-sensitive). | Mapping: <TargetField> = EXACT(VALUE("SourceField1"), VALUE("SourceField2")) |
FAST_TLOOKUP(String query) | This function looks up a supplied Id from a passedquery,and returns the corresponding value. | FAST_TLOOKUP("select id from account where writer_name = { reader_name } and @return value of the return_col based on the where_clause, else it would The example is not consistent |
FIND(String findText, String withinText) | This function returns the position of the first string parameter within the supplied second string parameter. | Mapping: <TargetField> = Find(VALUE("SourceField1"), VALUE("SourceField2")) In this example, I will pass the first string as "arch" and second string as "search". The FIND() function will return the position of "arch" in "search". The FIND() function will return 3 as the value. Trigger: Select Id,firstname,lastname from contact The above query will retrieve Id, |
firstnameandlastnamefieldsfromcontacts object of Salesforce. You can use FIND() function to check whether lastName fields has |
valuesinfirstname.If the function |
findsthelastname valueinfirstname field then the function returns zero; else it returns the position |
ofthelastnameinfirstname. TargetField = FIND(VALUE("lastname"), VALUE("firstname"))
| ||
FIND(String findText, String withinText, int startNum) | This function returns the position of the first string parameter within the supplied second string parameter from the passed start index. | Mapping: TargetField = FIND(VALUE("SourceField1"), VALUE("SourceField2"), "Start Position")
|
FIXED (Double) | This function round off the decimal values to two digits and returns the rounded off value. | Mapping: <TargetField> = FIXED("sourcefield1") In the price field the value passed is 12.6789 so the FIXED() function will return the price value as 12.68 |
FIXED (Double, Integer) | This function rounds a supplied number to a specified number of decimal places. | Mapping: <TargetField> = FIXED("sourcefield1") In the |
pricefieldthe value passed is 14.789 so the FIXED() function will return the price value as 14.8 | ||
FIXED (Double, Integer, Boolean) | Not supported | |
FORMAT(String value, String format) | This function transforms the numeric string passed in the first parameter based upon the format passed in the second parameter and returns the transformed number as a string. | Mapping: <TargetField> = FORMAT("SourceField1", "Format String") Example: Database query In the price field the value passed is 210.6 so the FIXED() function will return the price value as 210.60 |
GETROOTVALUE(String elementName) | This function returns the immediate parent node of an XML element. | Mapping: <TargetField> = GETROOTVALUE("SourceField1") |
GETSOURCEXML2STRING() | This function returns the string formatted XML structure of the row. | Mapping: <TargetField> = GETSOURCEXML2STRING() |
GETSOURCEXML2STRING(String elementName) | This function returns the string formatted XML structure of the row, for the element name passed. | Mapping: <TargetField> = GETSOURCEXML2STRING("InvoiceRet") |
IF(boolean condition, String trueValue, String falseValue) | This function tests the user-defined condition and returns one result if the condition is true, and another result if the condition is false. | Mapping : <TargetField> = IF(String,Integer,String) |
product2 object of Salesforce. You can use IF |
function to check if either |
unitprice or |
quantity is returning '0' and add a validation accordingly. |
|
In this case the IF function checks the value of quantiy to be zero then it returns 1 or else it returns the original quantity value from the salesforce.
In this case the IF function checks the value of rate to be empty then it returns 0.00 or else it returns original rate value from the salesforce. | ||
ISEMPTY(String s) | This function returns true if the variable is unintialized or explicity set to empty; otherwise it returns false. | Mapping: <TargetField> = ISEMPTY( |
String) |
Salesforce object field |
firstname, |
lastname from |
contact |
retrieves Id, |
firstname and |
lastname from |
contact object |
of Salesforce. You can use ISEMPTY() function to check |
whether the first name is empty or not. If the |
value of first name field is empty then the function return true, else |
return false. In the above example, the first name field has a value of "Alan" so the function ISEMPTY() returns true. | ||
ISNULL(String s) | This function checks if the passed value is an null value and returns true;Otherwise, returns false. | Mapping: <TargetField> = ISEMPTY(String) Example: Salesforce object field In the above example, the first name field has a value of "Alan" so the function ISNULL() returns false. |
LEFT (String var) | This function returns the first character of the supplied string which is on left hand side. | Mapping: <TargetField> = LEFT(VALUE("SourceField1")) |
passing a string <TargetField> = LEFT("search") In this example, I |
passed a string as "search". The LEFT() function |
returns the value as "s". |
|
Salesforce object fields |
firstname, |
lastname from |
contact |
retrieves Id, |
firstname and |
lastname from contacts object |
of Salesforce. You can use LEFT() function to get the first character of |
the field firstname. <TargetField> = LEFT(VALUE("FirstName")) so the firstname field has a value of "salesforce" and the function will return 's' which is the fist character of string from the left. | ||
LEFT(String var, Int count) | This function returns the specified number of characters from the left of the supplied string. | Mapping: <TargetField> = LEFT(VALUE("SourceField1"), |
noofcharacter) |
passing a string <TargetField> = LEFT("search",3) In this example, I |
passed the first |
parameter as "search" and second parameter as 3. The LEFT() function will return 3 characters from the left of |
the parameter "search" |
and the function returns "sea". |
Trigger: select Id, firstname, lastname from contact |
retrieves Id, |
firstname and |
lastname from |
contact object in Salesforce. You can use LEFT() function to get |
five characters of |
field firstname from the left side. <TargetField> = LEFT(VALUE("FirstName"), 5) so the firstname field has a value of "salesforce" and the function will return 'sales' which are the five character of string from the left. |
the length of the passed string is less than the second parameter then the function will return a full string as a result. | ||
LEN(String var) | This function returns the length of the supplied string. | Mapping: <TargetField> = LEN(String) |
passing a string <TargetField> = LEN("search") In this example, I |
pass |
the string as "search". The LEN() function will return value as '6' which is the length of string "search". |
|
Salesforce object fields |
firstname, |
lastname from |
contact |
retrieve Id, |
firstname and |
lastname from contacts object |
of Salesforce. You can use LEN() function to get the length of |
either field firstname or |
lastname. <TargetField> = LEN(VALUE("FirstName")) |
The value in the field firstname has a value "mydbsync" and hence the LEN() function result as 7. | ||||||||
LINK(String colName, String value) | This function reads the second parameter passed through this function and sets it as an attribute to the column name which is passed through the first parameter. | |||||||
LINK(String colName, String query, String fieldName, String refValue, String objectName) | The Link function is used to update Salesforce (Target) field with a source value based on the source identifier that remains unique across Salesforce (Target) as well as Source (Ex: Quickbooks) application | Example:
| ||||||
LINK(String colName, String query, String fieldName, String refValue, String objectName, String valueField) | ||||||||
LOG(Arg 0) This is not in function2base | This function is used to print the value of a parameter passed to DBSync console. It can be used for debugging purpose. | |||||||
LOOP this is not coming in UI | This is a DBSync function different than the conventional '=' operator used to map Source and Target. | Example1: Salesforce OppotunityLineItems to InvoiceLineItems | ||||||
LOOKUP(String adapterName, String queryString) | This function searches for a specific value in the source connector where the condition is passed in the form of a query to be searched on the first parameter. | LOOKUP("Salesforce","select Id from Account where Name='Test'") | ||||||
LOWER (String var) | This function converts all character in a supplied string to lowercase. | Mapping: <TargetField> = LOWER(String) | ||||||
LPAD(String text, String pattern, int pad) | This function is used to pad left side of a string with the specific set of characters. The integer is the total length of the string returned after padding. | Mapping: <TargetField> = LPAD(VALUE("SourceField1"), "string", Int) | ||||||
LSPLIT(String text, String splitter) | This function returns the split value of the string starting from left side of a string till the splitter value. | Mapping: TargetField = LSPLIT(VALUE("SourceField"), "string") | ||||||
MAP(String key, String...mapEntries) | This function returns the value against the key passed from the key-value pair(s) passed via second parameter of the function. | MAP("myKey", "a=b", "c=d", "myKey=myValue") will return "myValue". If key is not present in any of the key-value pairs then function will return empty string. | ||||||
MEMLOOKUP(String cacheIdentifier, String query) | This function uses unique identifier of a query i.e cacheIdentifier which would load up the dataset for a lookup.This function creates a table in memory for returning the value of a lookup. | Mapping: <TargetField> = MEMLOOKUP(StringCacheIdentifier, StringKey) | ||||||
MEMLOOKUPREGEX(String cacheIdentifier, String key, String regex) | A variant of the MEMLOOKUP function which takes an additional parameter i.e. regex, returns the value of only those key which matches with regex. | the value whose key starts with prefix, or null if not available | ||||||
MEMTABLE(String cacheIdentifier, String query) | This function creates a memory table in DBSync with key -value pair of the query passed through this function. | MAPPING: <TargetField> = VALUE("SourceField") + MEMTABLE(StringCacheIdentifier, StringQuery) | ||||||
MID(String text, Int startNum, Int numChars) | This function extracts a substring from the string and returns the substring. | |||||||
NOTEQUALS(String v1, String v2) | This function compares the value with another value and returns true ifitisnotequalselsereturns false | Mapping: <TargetField> = EQUALS(VALUE("CurrencyISO"),"USD") This function compares USD in CuurencyISO field and if USD is found then it returns as true else false. | ||||||
NUMBER(String text) This is not in UI | This function returns an integer value of the string passed to it. | MAPPING: <TargetField> = NUMBER("String") | ||||||
OR(boolean exp1, boolean exp2) | This function evaluates the conditions passed through this function and returns true if any one of condition evaluates to true; otherwise, returns false. | MAPPING: <TargetField> = OR(Boolean, Boolean) | ||||||
PARAM(String name) | This function PARAM extract the values from session which is in the format PARAM.SOURCE_Object.Variable=PARAM.TARGET_Object.Variable and returns the variable value. | |||||||
PARAM_PARENT(String name) | This function PARAM extract the values from session which is in the format PARAM.SOURCE_Object.Variable=PARAM.TARGET_Object/Variable and return the parent value. | |||||||
PARENTVALUE(String name) | This function reads any node element and returns immediate parent value of the node element passed. | Mapping: TargetField = PARENTVALUE("SourceField1") | ||||||
PARENTVALUEATTR(String path, String attr) | This function reads the passed node element and returns attributes of the immediate parent node. | Mapping: TargetField = PARENTVALUEATTR("CustomerRef","name") | ||||||
PROPER(String text) | This function reads a string and converts the first letter of a word to upper case and rest of alphabets in a word to lower case.This is used to represent camel notation | |||||||
REPLACE (String oldText, Int startNum, Int numChars, String newText) | This function replaces a full string or a part of string text with another text string from the position sent through parameter i.e. startNum. | Mapping: TargetField = REPLACE(VALUE("SourceField1), 1, 5, "replace string") | ||||||
REPT(String text, Int numberOfTimes) | This function returns a string consisting of a supplied text string, repeated specified number of times. | Mapping: <TargetField> = REPT(VALUE("SourceField1"), 2) Example: REPT("tech", 2) would return "techtech" | ||||||
RIGHT(String var) | This function returns a right most character of the string value passed. | Mapping: TargetField = RIGHT(String) | ||||||
RIGHT(String var, Int count) | This function returns a specified number of characters from the end of a supplied text string. | Mapping: <TargetField> = RIGHT(VALUE("SourceField1"), NoOfCharacters) | ||||||
RPAD(String var, String value, Int size) | This function returns a string after padding the input string with extra characters from the right side. The user can pass the size of the input string till which the padding should be done. | Mapping: <TargetField> = RPAD(VALUE("SourceField1"), "string", Int) Example1: RPAD("tech on the net", "0", 15) would return "tech on the net" Example2: RPAD("tech on the net", "0", 18) would return "tech on the net000" | ||||||
RSPLIT(String var, String splitter) | This function takes splitter text and compares it with the variable text and splits the variable text basing on the splitter text and returns character after the splitter text to the right. | Mapping: TargetField = RSPLIT(VALUE("SourceField"), "string") Example: RSPLIT ("tech-on-the-net", "-") would return "net" | ||||||
SEARCH(String findText, String withinText) | This function returns the position of a supplied text string from within a supplied text string. | Mapping: <TargetField> = SEARCH(VALUE("SourceField1"), VALUE("SourceField2")) | ||||||
SEARCH(String findText, String withinText, int startNum) | This function returns the position of a supplied text string from within a supplied text string for which starting position can be specified. | Mapping: <TargetField> = SEARCH(VALUE("SourceField1"), VALUE("SourceField2"), "Start Position") | ||||||
SESSION_GET(String name) | This function returns the value of the key stored in the session of that particular process or workflow. | Mapping: <TargetField> = SESSION_GET("KEY") Example1: Retrieving the KEY stored in the session Reader : Salesforce Writer : Quickbooks Mapping : CustomerAddRq/CustomerAdd/Name = SESSION_GET("Account_Name") In the above example, we are retrieving the KEY ie. "Account_Name" stored in the session and value that is being parsed internally in that of what was stored in the session, "Avankia". | ||||||
SESSION_PUT(String name, String value) | This function stores the key/value pair in the session of that particular process or workflow which is active. This function can only work activeworkflowswithinDBSync. | Mapping: out = SESSION_PUT("KEY","VALUE") | ||||||
SETATTR(String colName, String attName, String attValue, String colVal) | This function sets the column with an attribute of name and value as specified. The column value would be set as specified in the colVal. Eg: SETATTR("Name","priceBook","Standard PriceBook",VALUE("Name")) | Example : SETATTR("PricebookEntryId","pricebook","Standard Price Book",VALUE("ItemRef/FullName")) | ||||||
SUBSTITUTE(String str, String oldStr, String newStr) | This function replaces all occurrences of a string, within an old string, with the passed new string. | |||||||
SUBSTITUTE(String str, String oldStr, String newStr, int occurrences) | This function replaces the specified number of occurrences of a string, within an old string, with the passed new string. | |||||||
TEXT(Arg 0, Arg1) | Not Currently Implemented | |||||||
TLOOKUP(String queryString) | Returns the value for the column in the query. In the query, only one column can be specified. | Mapping: <TargetField> = TLOOKUP(stringQuery) Example1: SALESFORCE OBJECT In this example, I will select ID value for Account with name as ABC. TargetField = TLOOKUP("Select Id from Account where Name ='"+ "ABC" + "'") Example2: DATABASE TABLE In this example, I will select ID value for Account from dbAccount table with name as ABC. <TargetField> = TLOOKUP("Select Id from dbAccount where Name ='" + VALUE("NAME") +"'") Here assumption is that NAME field contains value ABC. | ||||||
TODAY() | This function returns Today's date | Mapping: <TargetField> = TODAY() | ||||||
TRIM(String value) | This function returns a text value with the leading and trailing spaces removed. | Example: TRIM(VALUE("sourcefield1")) | ||||||
UNIQUEFIELD(String colName, String value, String colValue) | This function sets the column with the column value and attribute with the unique field value and returns as an object. | |||||||
UPPER(String text) | This function converts all the character in a passed string to upper case. | Mapping: <TargetField> = UPPER(String) | ||||||
VALIDATEROW() | This function returns true and is used only for validation of a rule section to check whether any conditon is satisfied | Example: IF(ISEMPTY(VALUE("OpportunityId")),"true","false") | ||||||
VALUE(String xPath) | This function converts the input parameter to be read as string. | MAPPING: TargetField = VALUE("SourceField") | ||||||
VALUE(String xPath, boolean treatAsEmpty) | ||||||||
VALUEATTR(String xPath, String attr) | This function takes a path and attribute name on it and returns the actual value of the attribute. | Mapping: TargetField = VALUEATTR("CustomerRef","name") |
SETATTR("PricebookEntryId","pricebook","Standard Price Book",VALUE("ItemRef/FullName"))