Standard Functions
Learn how to use the standard functions to build your workflows
Standard functions are predefined functions available to all users in a project. When you import or clone a project, these functions remain intact, ensuring consistency across the original and copied projects.
ADDXML(String xml)
Adds an XML structure to the root node of the target element. Use this function when the source application does not expose fields through its API and you need to map a field by modifying the XML structure to post back.
The following trigger fetches id and firstname from the Contact table, but the table also contains a contactaddress field the query does not return:
To map contactaddress to the target:
ADDXML(String xPath, String xml)
Appends an XML structure to the node at the given XPath. Use this function when the source application does not expose fields through its API and you need to parse data from a custom field.
The following mapping writes a Salesforce custom field (SalesRep__c) to a QuickBooks Online custom field (SalesRep):
Where:
<DefinitionId>2</DefinitionId>— position of the custom field in QuickBooks Online<Name>SalesRep</Name>— name of the custom field in QuickBooks Online<StringValue>— value of the matching Salesforce field
AND(boolean exp1, boolean exp2)
Returns true when both conditions are met; otherwise returns false.
The following mapping returns true for records where Stage is "Closed Won" and Type is "Key Account":
ASNUMBER(String value)
Returns the numeric value of a string. When the supplied value is not a number, the function returns 0.
Returns 123. When the value is null or empty, the function returns 0.
CLEAN(String s)
Removes all non-printable characters from a string. The function removes the first 32 non-printable characters defined in the 7-bit ASCII code.
Returns "Alan" after removing the non-printable character.
CODE(String s)
Returns the numeric ASCII code of the first character of a string.
Returns 65, which is the ASCII code for "A".
CONCATENATE(String... strings)
Joins two or more strings and returns the combined result.
Returns "It's raining heavily outside.".
DATE(String dateString)
Parses a date string and returns it in the format yyyyMMdd-HHmmssZ.
Returns "20101203-123321".
DATE(String dateString, String inputFormat)
Parses a date string using the specified input format and returns the date in that format. When no output format is specified, the function returns the date in yyyyMMdd-HHmmssZ format.
When lasttransferdate holds "Mar-12-2010 12:33:21", the function returns "03-12-2010 12:33:21".
DATE(String dateString, String inputFormat, String outputFormat)
Converts a date string from one format to another.
When createddate holds "03-12-2010 12:33:21", the function returns "2010-12-03T12:33:21".
DOLLAR(Arg0)
Not supported.
—
EQUALS(String v1, String v2)
Compares two string values and returns true when both match. The comparison is case-insensitive.
Returns false because the strings do not match.
ERROR()
Returns the error message generated when a write to the target fails. Use this function in status write-back mappings, after a write attempt has completed.
When a write to a QuickBooks sales order fails with the message "Invalid argument. The specified record does not exist in the list.", the following mapping writes that error back to the source application:
EXACT(String str1, String str2)
Compares two string values and returns true when both match exactly. The comparison is case-sensitive.
Returns true when both fields hold the value "Chris".
FAST_TLOOKUP(String query)
Looks up a value from a query and returns the corresponding result. This function works identically to TLOOKUP, but executes based on writer batch size for faster performance.
Note: The return value must not start or end with a hyphen, and must not be null.
The following mapping updates a Salesforce Opportunity by matching a QuickBooks customer List ID:
When a match is found between the List ID and the Salesforce Account ID, the related invoice data from QuickBooks updates the corresponding Opportunity record.
FIND(String findText, String withinText)
Returns the 1-based position of findText within withinText. When findText appears more than once, the function returns the position of the first instance.
Returns 3 because "arch" begins at position 3 in "search".
FIND(String findText, String withinText, int startNum)
Returns the position of findText within withinText, starting the search at startNum. When findText appears more than once, the function returns the position of the first instance found from startNum onward.
Returns 3. The search begins at position 2, so the character "s" at position 1 is skipped.
FIXED(Double)
Rounds a decimal number to two decimal places.
Returns 12.68.
FIXED(Double, Integer)
Rounds a decimal number to the specified number of decimal places.
Returns 14.8.
FIXED(Double, Integer, Boolean)
Not supported.
—
FORMAT(String value, String format)
Transforms a numeric string using the specified format pattern and returns the result as a string.
Returns "12.00".
GETROOTVALUE(String elementName)
Returns the value of the immediate parent node of the specified XML element.
Given the following XML:
The mapping:
Returns "John".
GETSOURCEXML2STRING()
Returns the entire source row as a formatted XML string.
Given the following XML input:
The mapping:
Returns the entire structure as a string.
GETSOURCEXML2STRING(String elementName)
Returns the specified XML element and its contents as a formatted string.
Given the following XML input:
The mapping:
Returns:
IF(Boolean condition, String trueValue, String falseValue)
Evaluates a condition and returns trueValue when the condition is true, or falseValue when it is false.
When Rate is empty, the function returns "0.00". Otherwise, it returns the original value of Rate.
ISEMPTY(String s)
Returns true when the variable is uninitialized or explicitly set to empty; otherwise returns false.
When FirstName holds the value "Alan", the function returns false.
ISNULL(String s)
Returns true when the passed value is null; otherwise returns false.
When lastname holds the value "Alan", the function returns false.
LEFT(String var)
Returns the first (leftmost) character of a string.
Returns "s".
LEFT(String var, int count)
Returns the specified number of characters from the left of a string. When the string is shorter than count, the function returns the full string.
Returns "sea".
LEN(String var)
Returns the number of characters in a string.
Returns 6.
LINK(String colName, String value)
Deprecated — use MEMLOOKUP or TLOOKUP instead.
Reads the second parameter and sets it as an attribute on the column specified in the first parameter. Returns the result as an object.
Sets the Id value as an attribute on the Name node and returns an object.
LINK(String colName, String query, String fieldName, String refValue, String objectName)
Deprecated — use MEMLOOKUP or TLOOKUP instead.
Creates an internally cached table from a target query with two columns — the primary key and one other column — then looks for a string that matches across both source and target to update the target lookup field.
The following mapping updates a Salesforce lookup field based on a matching QuickBooks transaction ID:
The function builds a cache of Opportunity records and looks for a record where AVSFQuickBooks__Quickbooks_Id__c matches the QuickBooks TxnID. When a match is found, the Opportunity lookup field is updated.
LINK(String colName, String query, String fieldName, String refValue, String objectName, String valueField)
Deprecated — use MEMLOOKUP or TLOOKUP instead.
Creates an internally cached table from a target query and returns the value of the specified valueField when the key match is found.
Sets attributes on the Name node in the order: Id (sql), Phone (keyField), Website (objectIs), PhotoUrl (valueField). Returns the Id value (refValue).
LOG()
Prints the value of a parameter to the DBSync console. Use this function to inspect field values when debugging.
The following mapping logs the value of Udf_Pa_Pricing and returns the leftmost 20 characters:
The logged output appears in the DBSync Logs section.
LOOKUP(String adapterName, String queryString)
Searches the specified source connector using a query and returns the matching value.
Returns the Salesforce Account Id where Name equals "Test".
LOOP
Groups line items based on a target identifier. Use LOOP instead of the = operator when the target requires line item grouping, or when an identifier is unique and consistent across both source and target systems.
Example 1: Salesforce Opportunity line items to QuickBooks Invoice line items
For each Opportunity Line Item, the loop creates a corresponding Invoice Line Item.
Example 2: Database to QuickBooks
When four database rows share the same invoice_no, the mapping creates one QuickBooks Invoice with four Line Items instead of four separate invoices.
LOWER(String var)
Converts all characters in a string to lowercase.
Returns "search".
LPAD(String text, String pattern, int pad)
Pads the left side of a string with a specified character until the total length equals pad. When the string length already equals or exceeds pad, the function returns the original string.
Returns "tech on the net" (string length already equals 15).
Returns "000tech on the net".
LSPLIT(String text, String splitter)
Returns the substring from the beginning of the string up to (but not including) the first occurrence of the splitter.
Returns "tech".
MAP(String key, String... mapEntries)
Returns the value that corresponds to key from the provided key-value pairs. When the key is not found, the function returns an empty string.
Returns "myValue".
MEMTABLE(String cacheIdentifier, String query)
Creates an in-memory data cache identified by cacheIdentifier. The cache holds the results of a query executed against the target connector as key-value pairs. Use MEMLOOKUP to retrieve values from this cache.
Creates a cache named "AccountID" containing all Id–Name pairs from the dbAccount table, for example: a_01 → Name1, b_01 → Name2.
MEMLOOKUP(String cacheIdentifier, String key)
Returns the value corresponding to key from the cache identified by cacheIdentifier. You must create the cache with MEMTABLE before calling this function.
Using the cache from the MEMTABLE example, passing "a_01" as the key returns "Name1".
MEMLOOKUPREGEX(String cacheIdentifier, String key, String regex)
Returns the value corresponding to key from a cache, but only when key matches the specified regular expression. When the key does not match the expression, the function returns an empty string.
Returns a value only for keys that start with "a_". Keys that do not match the regular expression return an empty string.
MID(String text, int startNum, int numChars)
Extracts a substring of numChars characters from text, beginning at position startNum.
When Firstname holds "christopher", the function returns "tophe" (5 characters starting at position 6).
NOTEQUALS(String v1, String v2)
Returns true when the two values differ; otherwise returns false.
Returns true for all records where CurrencyISO does not equal "USD".
OR(boolean exp1, boolean exp2)
Returns true when at least one of the conditions is met; otherwise returns false.
When FirstName is "Alan", LastName is "Chris", and Id is 456, neither condition is met, so the function returns false.
PARAM(String name)
Extracts a variable value from the session. The session entry must follow the format PARAM.SOURCE_Object.Variable=PARAM.TARGET_Object.Variable. The function returns the third text literal from the matching entry.
When Description starts with "PARAM" and the value exists in the session, the function extracts and returns the third text literal.
PARAM_PARENT(String name)
Extracts the parent value from a session entry in the format PARAM.SOURCE_Object.Variable=PARAM.TARGET_Object.Variable. The function concatenates the second text literal with "/" and the third text literal, then returns the result.
When Description starts with "PARAM" and the value exists in the session, the function returns the second and third text literals joined with "/".
PARENTVALUE(String name)
Returns the value of the immediate parent XML node for the specified element.
The following trigger fetches Id, Name, and Account.Name from the Salesforce Opportunity object:
The mapping:
Returns the Account Name associated with each Opportunity.
PARENTVALUEATTR(String path, String attr)
Returns the attribute value of the immediate parent node at the specified path.
Given the following XML:
The mapping:
Returns "Alan".
PROPER(String text)
Converts the first letter of each word to uppercase and the remaining letters to lowercase (title case).
Returns "Search".
REPLACE(String oldText, int startNum, int numChars, String newText)
Replaces numChars characters in oldText starting at position startNum with newText.
Replaces 3 characters starting at position 3 ("arc") with "a", returning "seah".
REPT(String text, int numberOfTimes)
Returns a string consisting of the supplied text repeated the specified number of times.
Returns "techtech".
RIGHT(String var)
Returns the last (rightmost) character of a string.
Returns "h".
RIGHT(String var, int count)
Returns the specified number of characters from the right of a string. When the string is shorter than count, the function returns the full string.
Returns "earch".
RPAD(String var, String value, int size)
Pads the right side of a string with a specified character until the total length equals size. When the string length already equals or exceeds size, the function returns the original string.
Returns "tech on the net" (string length already equals 15).
Returns "tech on the net000".
RSPLIT(String var, String splitter)
Returns the substring to the right of the last occurrence of the splitter.
Returns "net".
SEARCH(String findText, String withinText)
Returns the position of findText within withinText. The comparison is case-insensitive. When findText appears more than once, the function returns the position of the first instance.
Returns 3.
SEARCH(String findText, String withinText, int startNum)
Returns the position of findText within withinText, starting the search at startNum. The comparison is case-insensitive. When findText appears more than once, the function returns the position of the first instance found from startNum onward.
Returns 3. The character "s" at position 1 is skipped.
SESSION_GET(String name)
Returns the value of the specified key from the active process or workflow session.
The following mapping retrieves the Account_Name key stored in the session:
Returns the value stored under "Account_Name", for example "Avankia".
SESSION_PUT(String name, String value)
Stores a key-value pair in the active process or workflow session. This function works only within active DBSync workflows.
Stores "Avankia" under the key "Account_Name". Retrieve this value later using SESSION_GET.
SETATTR(String colName, String attName, String attValue, String colVal)
Sets a column with a named attribute and value, and returns the column value. This function applies only when writing to the Salesforce Pricebook object.
Queries the Pricebook object for a record where the name is "Standard Price Book" and ItemRef/FullName matches PricebookEntryId. When the condition is met, the function returns the PricebookEntryId value and assigns it to the mapped target field.
SUBSTITUTE(String str, String oldStr, String newStr)
Replaces all occurrences of oldStr within str with newStr.
Replaces every occurrence of "John" in FirstName with "Alan".
SUBSTITUTE(String str, String oldStr, String newStr, int occurrences)
Replaces the specified number of occurrences of oldStr within str with newStr.
Replaces only the first two occurrences of "John" with "Alan".
TEXT(Arg0, Arg1)
Not supported.
—
TLOOKUP(String queryString)
Returns the value of the single column specified in the query, fetched from the target connector.
The following mapping updates a Salesforce Opportunity by matching a QuickBooks customer List ID:
When the List ID matches a Salesforce Account ID, the function returns that Account ID so the related QuickBooks invoice data updates the corresponding Opportunity.
TODAY()
Returns today's date in the format Day Mon DD HH:MM:SS TTT YYYY.
Example output: Fri May 06 07:10:58 CDT 2011
TRIM(String value)
Removes leading and trailing spaces from a string.
When FirstName holds " John", the function returns "John".
UNIQUEFIELD(String colName, String value, String colValue)
Sets the column with a unique field attribute value and returns the result as an object.
Sets the Name node with Id as the uniquefield attribute value and returns the Phone value as an object.
UPPER(String text)
Converts all characters in a string to uppercase.
Returns "SEARCH".
VALIDATEROW()
Returns true or false based on a conditional expression. Use this function in rule sections to control whether a row is processed.
Processes a row only when OpportunityId has a value. When OpportunityId is null, the function returns false and the row is skipped.
VALUE(String xPath)
Returns the value of the specified source field as a string.
When Name holds "John", the function returns "John".
VALUE(String xPath, boolean treatAsEmpty)
Returns the value of the specified source field as a string. When treatAsEmpty is true and the field has no value, the function returns an empty string.
Given the following XML:
The mapping:
Returns "Alan". When firstname has no value and treatAsEmpty is true, the function returns "".
VALUEATTR(String xPath, String attr)
Returns the value of the specified attribute at the given XPath.
Given the following XML:
The mapping:
Returns "female".
Last updated