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 returns a boolean value (True or False) for boolean expressions passed in. | Mapping: <TargetField> = AND(Boolean, Boolean)
Example: SALESFORCE OBJECT FIELDS READER: Select Id, FirstName, LastName from Contacts
The above query will select Id, FirstName and Last Name from contacts object in Salesforce. You can use AND() function to check weather first name and last name have same VALUE and Id is equal to 123. If both the conditions are true the function will return true, else it will return false.
<TargetField> = AND(VALUE("FirstName")==VALUE("LastName"), VALUE("Id")=="123") |
ASNUMBER(String value) | This function checks if a supplied value is a number and returns 0 if it is not a number otherwise returns the actual number. | MAPPING: <TargetField> = NUMBER("String") Example: SALESFORCE OBJECT FIELDS READER: Select Name, NumberOfEmployees from Leads The above query will select Name and Number of Employees from Leads object in Salesforce. You can use NUMBER () function to convert Number of Employees into integer format for target field. If the VALUE for Number of Employees is null or empty it will return Zero (0). <TargetField> = NUMBER(*VALUE("NumberOfEmployees"))* |
CLEAN(String string) | This function removes all non-printable characters from a supplied string value. | Mapping: TargetField = CLEAN("String VALUE") Example1: SALESFORCE OBJECT FIELDS READER: Select Id, Name from Account The above query will select ID and Name from Account object in Salesforce. You can use CLEAN () function to get the first Character of the Name and set it to target field. <TargetField> = CLEAN (VALUE("Name")) |
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("String VALUE") Example1: SALESFORCE OBJECT FIELDS READER: Select Id, Name from Account The above query will select ID and Name from Account object in Salesforce. You can use CODE () function to get the first Character of the Name and set it to target field. <TargetField> = CODE(VALUE("Name")) Example 2: STRING <TargetField> = CODE("Salesforce") The above mapping will put 'S' in the target field. |
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("SourceField"), "format string") Example 1: SALEFORCE OBJECT QUERY Reader: Select Company, CreatedBy, LastTransferDate from Leads Above query will select Company, CreatedBy and LastTransferDate from leads object in Salesforce. You can use DATE() function to format the passed string to a specific Date Format. <TargetField> = DATE(VALUE("LastTransferDate"),"MM-DD-YYYY HH:MM:SS") Above Mapping will convert Date into the Database format of Date Time. If VALUE of LastTransferDate is Mar-12-2010 12:33:21, then the DATE() will return 03-12-2010 12:33:21 Example 2: DATABASE QUERY Reader: Select ContactName, CreatedDate from dbContacts Above Query will select ContactName and CreatedDate from dbAccount table in database. <TargetField>= DATE(DATE(VALUE("CreatedDate"),"yyyy-MM-dd hh:mm:ss"),"yyyy-MM-dd'T'HH:mm:ss'Z'") Above mapping will convert the database date field to Salesforce format. |
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 passes date string as "yyyyMMdd-HHmmssZ". |
|
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).
|
|
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")) Example: OBJECT FIELDS READER: Select Id, FirstName, LastName from Contacts The above query will select Id, FirstName and Last Name from contacts object in Salesforce. You can use an EXACT() function to check whether FirstName and LastName have same VALUE. If both the fields have same VALUE the function will return true, else it will return false. <TargetField> = EXACT(VALUE("FirstName"),VALUE("LastName")) |
FAST_TLOOKUP(String query) I have to change description | This function looks up a supplied Id in a table, and retruns the corresponding value. | FAST_TLOOKUP("select id from account where writer_name = { reader_name } and writer_code = { reader_code }") @return value of the return_col based on the where_clause, else it would return empty value |
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")) Example1: STRING PARAMETERS In this example, I will pass first string as "arch" and second string as "search". The FIND() function will return the place VALUE of "arch" in "search". So it will return 3 as the place VALUE. <TargetField> = FIND("arch","search") Example2: SALESFORCE OBJECT FIELDS READER: Select Id, FirstName, LastName from Contacts The above query will select Id, FirstName and Last Name from contacts object in Salesforce. You can use FIND() function to check whether LastName is already existing in FirstName and If it is not existing in the FirstName it will return 0 else it will return the place VALUE of the LastName in FirstName. TargetField = FIND(VALUE("LastName"), VALUE("FirstName")) Note: If the search string exists more than once in the Second string, the function returns the place VALUE of first existence. |
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") Example1: STRING PARAMETERS In this example, I will pass first string as "arch" and second string as "search". The FIND() function will search the string "arch" in "search" from the second character and will return the place VALUE of "arch" in "search". So it will return 3 as the place VALUE. So characters 's' and 'e' will be ignored. TargetField = FIND("arch","search",2) Example2: SALESFORCE OBJECT FIELDS READER: Select Id, FirstName, LastName from Contacts The above query will select Id, FirstName and Last Name from contacts object in Salesforce. You can use FIND() function to check whether LastName is already existing in FirstName and If it is not existing in the FirstName it will return 0 else it will return the place VALUE of the LastName in FirstName. <TargetField> = FIND (VALUE("FirstName"), VALUE("LastName"),1) Note: If the search string exists more than once in the Second string, the function returns the place VALUE of first existence. |
FIXED (Double) | This function round off the decimal values to two digits and returns the rounded off value. |
|
FIXED (Double, Integer) | This function rounds a supplied number to a specified number of decimal places. |
|
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") |
GETROOTVALUE(String elementName) | This function returns the immediate parent node of an XML element. |
|
GETSOURCEXML2STRING() | This function returns the string formatted XML structure of the row. | Mapping: TargetField = GETSOURCEXML2STRING(VALUE("SourceField1")) |
GETSOURCEXML2STRING(String elementName)
| This function returns the string formatted XML structure of the row, for the element name passed. |
|
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) Example: SALESFORCE OBJECT FIELDS READER: Select UnitPrice, Quantity from Product2 The above query will select UnitPrice and Quantity from Product object in Salesforce. You can use IF condition to check if either UnitPrice or Quantity is returning '0' and add a validation accordingly. TargetField = IF(LEN(VALUE("Quantity"))==0,"1",VALUE("Quantity")) TargetField = IF(ISEMPTY(VALUE("Rate")),"0.00",VALUE("Rate")) |
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) Example: SALESFORCE OBJECT FIELDS READER: Select Id, FirstName, LastName from Contacts The above query will select Id, FirstName and Last Name from contacts object in Salesforce. You can use ISEMPTY() function to check Whether FirstName is Null or Not. If the VALUE for FirstName is null the Function will return true, else it will return false <TargetField> = ISEMPTY(VALUE("FirstName")) |
ISNULL(String s) | This function checks if the passed value is an null value and returns true;Otherwise, 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)) Example1: STRING PARAMETER In this example, I will pass a string as "search". The LEFT() function will return the first character from the right of string "search". So string returned by function will be "s". <TargetField> = LEFT("search") Example2: SALESFORCE OBJECT FIELDS READER: Select Id, FirstName, LastName from Contacts The above query will select Id, FirstName and Last Name from contacts object in Salesforce. You can use LEFT() function to get first character of FirstName. If the VALUE for FirstName is "salesforce" the resulting string will be "s". <TargetField> = LEFT(VALUE("FirstName")) |
LEFT(String var, Int count) | This function returns the specified number of characters from left of the supplied string. | Mapping: <TargetField> = LEFT(VALUE("SourceField1"), NoOfCharacters) Example1: STRING PARAMETER In this example, I will pass first string as "search" and second parameter as 3. The LEFT() function will return 3 characters from the left of string "search". So string returned by function will be "sea". <TargetField> = LEFT("search",3) Example2: SALESFORCE OBJECT FIELDS READER: Select Id, FirstName, LastName from Contacts The above query will select Id, FirstName and Last Name from contacts object in Salesforce. You can use LEFT() function to get first 5 characters of FirstName. If the VALUE for FirstName is "salesforce" the resulting string will be "sales". <TargetField> = LEFT(VALUE("FirstName"), 5) Note: If a number of characters inthestringislessthannumber of characters requested from the function, it will return the full string. |
LEN(String var) | This function returns the length of the supplied string. | Mapping: <TargetField> = LEN(String) Example1: STRING PARAMETER In this example, I will pass a string as "search". The LEN() function will return the length of string "search". So VALUE returned by function will be 6. <TargetField> = LEN("search") Example2: SALESFORCE OBJECT FIELDS READER: Select Id, FirstName, LastName from Contacts The above query will select Id, FirstName and Last Name from contacts object in Salesforce. You can use LEN() function to get length of FirstName or LastName. If the VALUE for FirstName is "salesforce" the resulting VALUE in LEN() will be 10. <TargetField> = LEN(VALUE("FirstName")) Example 3: DATABASE FIELDS READER: Select Id, FirstName, LastName from dbContacts The above query will select Id, FirstName and Last Name from contacts in database. You can use LEN() function to get length of FirstName or LastName.. If the value for FirstName is "avankia" the resulting value will be 7. <TargetField> = LEN(VALUE("FirstName")) |
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) Note: This function is deprecated and an alternative function could be MEMLOOKUP orTLOOKUP functions. | 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 The function creates an internally cached table querying the target (Salesforce) database with 2 columns, 1st column being the primary key for the table, 2nd for the other column name. It then looks for a string that is same across both Source and Target system before updating the Target LOOK-UP(Salesforce) field with the Source field value. | Example: Mapping: AVSFQB__Opportunity__c = LINK("AVSFQB__Opportunity__c",
"Select id,AVSFQB__Quickbooks_Id__c from Opportunity",
"AVSFQB__Quickbooks_Id__c",LSPLIT(VALUE("TxnID"),"-"),
"Opportunity") |
In the above example we are trying to update Salesforce
look-up field AVSFQB__Opportunity__cbased on the condition
where AVSFQB__Quickbooks_Id__c is equal to Quickbooks
transaction Id "TxnID" as they both are same across both
Salesforce as well as Quickbooks. |
In order to update Salesforce look-up field Opportunity
we are creating a cached table with the above query.
Then it does a lookup for all Opportunity records whose
Salesforce field AVSFQB__Quickbooks_Id__c value matches
with that of Quickbooks field "TxnID". |
|
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. <SourceField "loop" TargetField> is used in scenarios where a grouping of Line Item is required based on the Target identifier. It can also be used when the identifier is unique and same across both Source and Target systems. | Example1: Salesforce OppotunityLineItems to InvoiceLineItems Mapping : InvoiceAddRq/InvoiceAdd/InvoiceLineAdd [dbsync:loop] "OpportunityLineItems/records" In the above example for every Opportunity Line Item, the loop will create a corresponding Invoice Line Item. Example2: Source - Database ; Target - Quickbooks Database Query : select invoice_no,customerid,item,itemdesc from InvoiceTable Mapping : InvoiceAddRq/InvoiceAdd/InvoiceLineAdd ?[dbsync:loop] "invoice_no/list" In the above example, we are using "invoice_no" column from the database as an identifier. Based on this identifier we are grouping each row as a line item and then mapping it with Quickbooks Invoice Line Item. For example, if the database Invoice table has 4 rows with "invoice_no" being the same for all the 4 rows, then the mapping will create 1 Invoice in Quickbooks with 4 LineItems ratherthancreating one Invoice for each row. |
LOOKUP(String adapterName, String queryString) The description need to be added | 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. |
|
LOWER (String var) | This function converts all character in a supplied string to lowercase. | Mapping: <TargetField> = LOWER(String) Example1: STRING PARAMETER In this example, I will pass string as "SEARCH". The LOWER() function will return the string converted to lowercase. So "SEARCH" will be returned as "search" and that VALUE will be placed in target field. <TargetField> = LOWER("SEARCH") Example2: SALESFORCE OBJECT FIELDS READER: Select Id, FirstName, LastName from Contacts The above query will select Id, FirstName and Last Name from contacts object in Salesforce. You can use LOWER() function to get length of FirstName or LastName. If the VALUE for FirstName is "Avankia" the resulting string returned by function will be "avankia". <TargetField> = LOWER(VALUE("FirstName")) Example 3: DATABASE FIELDS READER: Select Id, FirstName, LastName from dbContacts The above query will select Id, FirstName and Last Name from contacts in database. You can use LOWER() function to get length of FirstName or LastName.. If the VALUE for FirstName is "DBSync" the resulting string will be "dbsync". <TargetField> = LOWER(VALUE("FirstName")) |
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) Example1: LPAD("tech on the net", "0", 15) would return "tech on the net" Example2: LPAD("tech on the net", "0", 18) would return "000tech on the net" |
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") Example: LSPLIT ("tech-on-the-net", "-") would return "tech" |
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) Example1: SALESFORCE OBJECT In this example, AccountID is the memory Table created on Account object in Salesforce using MEMTABLE() function. <TargetField> = MEMLOOKUP("AccountID",VALUE("SourceField") ) Example2: DATABASE TABLE In this example, AccountID is the memory Table created on dbAccount table in database using MEMTABLE() function. <TargetField> = MEMLOOKUP("AccountID", VALUE("SourceField") ) |
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.
You can append the MEMTABLE() at the end of any other mapping. | MAPPING: <TargetField> = VALUE("SourceField") + MEMTABLE(StringCacheIdentifier, StringQuery) Example1: SALESFORCE OBJECT In this example, I will select Id and Name value from Account Object for all Accounts. <TargetField> = VALUE("SourceField") +MEMTABLE("AccountID","Select Id, Name from Account") Example2: DATABASE TABLE In this example, I will select ID and Name value from dbAccount table for all records in dbAccount Table. <TargetField> = VALUE("SourceField") +MEMTABLE("AccountID","Select ID, Name from dbAccount") |
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 |
|
NUMBER(String text) This is not in UI | This function returns an integer value of the string passed to it. | MAPPING: <TargetField> = NUMBER("String") Example: SALESFORCE OBJECT FIELDS READER: Select Name, NumberOfEmployees from Leads The above query will select Name and Number of Employees from Leads object in Salesforce. You can use NUMBER () function to convert Number of Employees into integer format for target field. If the VALUE for Number of Employees is null or empty it will return Zero (0). <TargetField> = NUMBER(*VALUE("NumberOfEmployees"))* |
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) Example: SALESFORCE OBJECT FIELDS READER: Select Id, FirstName, LastName from Contacts The above query will select Id, FirstName and Last Name from contacts object in Salesforce. You can use OR() function to check whether first name and last name have same VALUE or Id is equal to 123. If both the conditions are true the function will return true. If first condition is true and second condition is false the function will return true. If first condition is false and second condition is true the function will return true. If both the conditions are false the function will return false. TargetField = OR(VALUE("FirstName")==VALUE("LastName"), VALUE("Id")=="123") |
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. |
|
PARENTVALUEATTR(String path, String attr) | This function reads the passed node element and returns attributes of the immediate parent node. |
|
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") Example1: STRING PARAMETER In this example, I will pass string "search" as the first parameter, 3 as start number for the second parameter, 3 as a number of characters to be replaced for third parameter and "a" as the replace string. The REPLACE() function will replace three characters from 3rd character to 5th character in the string. The resulting string returned by function will be "search". TargetField = REPLACE("seaaarch",3,3,"a") Example2: SALESFORCE OBJECT FIELDS READER: Select Id, FirstName, LastName from Contacts The above query will select Id, FirstName and Last Name from contacts object in Salesforce. You can use REPLACE() function to replace specified number of characters of FirstName with specified string. If the VALUE for FirstName is "salesforce" as first parameter, VALUE of start number is 2 as second parameter, VALUE for number of characters is 4 for third parameter, and VALUE of Replace String is "f-", then function REPLACE() will return string "sf-force". TargetField = REPLACE(VALUE("FirstName"),2,4,"f") Note: VALUE() function is used inside REPLACE() to convert the FirstName VALUE to 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) Example1: STRING PARAMETER In this example, I will pass string as "search". The RIGHT() function will return first character from the right of string "search". So string returned by function will be "h". TargetField = RIGHT("search",1) Example2: SALESFORCE OBJECT FIELDS READER: Select Id, FirstName, LastName from Contacts The above query will select Id, FirstName and Last Name from contacts object in Salesforce. You can use RIGHT() function to get last last characters of FirstName. If the VALUE for FirstName is "salesforce" the resulting string will be "e". <TargetField> = RIGHT(VALUE("FirstName")) Example 3: DATABASE FIELDS READER: Select Id, FirstName, LastName from dbContacts The above query will select Id, FirstName and Last Name from contacts in database. You can use RIGHT() function to get last last characters of FirstName. If the VALUE for FirstName is "salesforce" the resulting string will be "e". <TargetField> = RIGHT(VALUE("FirstName")) |
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) Example1: STRING PARAMETER In this example, I will pass first string as "search" and second parameter as 5. The RIGHT() function will return 5 characters from the right of string "search". So string returned by function will be "earch". <TargetField> = RIGHT("search",5) Example2: SALESFORCE OBJECT FIELDS READER: Select Id, FirstName, LastName from Contacts The above query will select Id, FirstName and Last Name from contacts object in Salesforce. You can use RIGHT() function to get last 5 characters of FirstName. If the VALUE for FirstName is "salesforce" the resulting string will be "force". <TargetField> = RIGHT(VALUE("FirstName"), 5) Example 3: DATABASE FIELDS READER: Select Id, FirstName, LastName from dbContacts The above query will select Id, FirstName and Last Name from contacts in database. You can use RIGHT() function to get last last characters of FirstName. If the VALUE for FirstName is "avankia" the resulting string will be "a". <TargetField> = RIGHT(VALUE("FirstName"),1) Note: If number of characters in the string is less than number of characters requested from the function, it will return the full string. VALUE() function is used to convert the FirstName to string. |
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. |
|
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. |
|
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")) Example1: STRING PARAMETERS In this example, I will pass the first string as "arch" and second string as "search". The SEARCH() function will return the place VALUE of "arch" in "search". So it will return 3 as the place VALUE. <TargetField> = SEARCH("arch","search") Example2: SALESFORCE OBJECT FIELDS READER: Select Id, FirstName, LastName from Contacts The above query will select Id, FirstName and Last Name from contacts object in Salesforce. You can use SEARCH() function to check whether LastName is already existing in FirstName and If it is not existing in the FirstName it will return 0 else it will return the place VALUE of the LastName in FirstName. <TargetField> = SEARCH(VALUE("LastName"), VALUE("FirstName")) Note: If the search string exists more than once in the Second string, the function returns the place VALUE of first existence. |
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") Example1: STRING PARAMETERS In this example, I will pass the first string as "arch" and second string as "search". The SEARCH() function will search the string "arch" in "search" from the second position in the string and will return the place VALUE of "arch" in "search". So it will return 3 as the place VALUE. So character 's' will be ignored. <TargetField> = SEARCH("arch","search",2) Example2: SALESFORCE OBJECT FIELDS READER: Select Id, FirstName, LastName from Contacts The above query will select Id, FirstName and Last Name from contacts object in Salesforce. You can use SEARCH() function to check whether LastName is already existing in FirstName and If it is not existing in the FirstName it will return 0 else it will return the place VALUE of the LastName in FirstName. <TargetField> = SEARCH(VALUE("FirstName"), VALUE("LastName"),1) Note: If the search string exists more than once in the Second string, the function returns the place VALUE of first existence |
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 active workflows with in DBSync. | Mapping: out = SESSION_PUT("KEY","VALUE") Example1: Storing a static value (Account Name) in the session Reader : Salesforce Writer : ConsoleAdapter Mapping : out (target field) = SESSION_PUT("Account_Name","Avankia") Intheaboveexample we are storing a static key/value pair for an AccountName. This assigns the valueAvankia to the key "Account_Name" which can be retrieved using SESSION_GET variable |
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")) |
|
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() Format returned from TODAY() is "Day Mon DD HH:MM:SS TTT YYYY" Example: Fri May 06 07:10:58 CDT 2011 |
TRIM(String value) | This function returns a text value with the leading and trailing spaces removed. |
|
UNIQUEFIELD(String colName, String value, String colValue) | Sets the column with the attribute of the unique field and sets the column value.This function returns the value of the key passed in from the Process Definition Session. |
|
UPPER(String text) | This function converts all the character in a passed string to upper case. | Mapping: <TargetField> = UPPER(String) Example1: STRING PARAMETER In this example, I will pass string as "search". The UPPER() function will return the string converted to lowercase. So "search" will be returned as "SEARCH" and that VALUE will be placed in target field. <TargetField> = UPPER("search") Example2: SALESFORCE OBJECT FIELDS READER: Select Id, FirstName, LastName from Contacts The above query will select Id, FirstName and Last Name from contacts object in Salesforce. You can use UPPER() function to get length of FirstName or LastName. If the VALUE for FirstName is "Avankia" the resulting string returned by function will be "AVANKIA". <TargetField> = UPPER(VALUE("FirstName")) Example 3: DATABASE FIELDS READER: Select Id, FirstName, LastName from dbContacts The above query will select Id, FirstName and Last Name from contacts in the database. You can use an UPPER() function to get the length of FirstName or LastName. If the VALUE for FirstName is "Dbsync" the resulting string will be "DBSYNC". TargetField = UPPER(VALUE("FirstName") |
VALIDATEROW() |
|
|
VALUE(String xPath) | This function returns string value of the FieldName. | MAPPING: TargetField = VALUE("SourceField") Example 1: SALESFORCE OBJECT FIELDS READER: Select Id, Name from Account The above query will select ID and Name from Account object in Salesforce. You can use VALUE () function to map the VALUE of name field to target field. TargetField = VALUE("Name") Example 2: SALESFORCE LOOKUP OBJECT FIELDS READER: Select Id, Name, Account.Name from Opportunity The above query will select ID, Name and Account Name from opportunity object in Salesforce. You can use VALUE () function to map the lookup field Account Name to the target field. <TargetField> = VALUE("Account/Name") |
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. |
|