Custom Functions

Function

Description

Examples

ADDXML(Arg 0)

ADDXML(Arg 0, Arg 1)

AND
(Boolean, Boolean)

This function returns Boolean value (True or False) for Boolean parameters 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(Arg 0)

CLEAN
(String)

It removes all non printable characters from String.

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(Arg 0)

CODE
(String)

This function returns the first character of the string passed to it.

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, Format)

This function returns the formatted Date or Date Time for specified string as per the format specified in function.

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.

ERROR()

This function can be used to print out error messages to your desired field or column as it returns error messages from the stack trace on integration failure.

Mapping: <TargetField> = ERROR()

EXACT
(String, String)

This function returns the Boolean value of the comparison of 2 string parameters.

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 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"))

FIND
(String, String)

This function returns the place value of the first string parameter in the second string parameter.

First Parameter: Text to find or search string.

Second Parameter: String in which it has to find search string.

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, String, Integer)

This function returns the place value of the text identified in the search text from the specified start index.

First Parameter: Text to find

Second Parameter: Text in which it has to find.

Third Parameter: Start the find from what column or 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.

FORMAT
(value, format)

Returns the value converted to the format specified.

Mapping: TargetField = FORMAT ("SourceField1", "Format String")

GETSOURCEXML2STRING()

This function returns the string formatted XML Structure of the row.

Mapping: TargetField = GETSOURCEXML2STRING(VALUE("SourceField1"))

IF
(String,Integer,String)

This function can be used as a normal if-else statement.

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)

This function returns Boolean value of whether the parameter is null or empty.

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"))

LEFT
(String)

Returns the first left char of the parameter.

Mapping: <TargetField> = LEFT(VALUE("SourceField1))

Example1: STRING PARAMETER

In this example, I will pass string as "search". The LEFT() function will return 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, Int)

Returns the specified number of chars from left of the parameters.

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 number of characters in the string is less than number of characters requested from the function, it will return the full string.

LEN
(String)

This function returns the length of the parameter passed in.

Mapping: <TargetField> = LEN(String)

Example1: STRING PARAMETER

In this example, I will pass string as "search". The LEN() function will return 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
(stringColName, stringQuery, stringFieldName, stringRefValue, stringObjectName)


Note : This function is deprecated and an alternative function could be MEMLOOKUP or TLOOKUP functions.

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 internal 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".

loop

This is a DBSync function different than the conventional '=' operator
used to map Source and Targert.

<SourceField "loop" TargetField> is used in
scenarios where 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

<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="8d646ac1-c3ba-45b8-8ed3-aacf817c7eab"><ac:plain-text-body><![CDATA[Mapping : InvoiceAddRq/InvoiceAdd/InvoiceLineAdd [loop] "OpportunityLineItems/records"
]]></ac:plain-text-body></ac:structured-macro>
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
<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="657df96f-8f35-4245-ab98-c5bc326ec642"><ac:plain-text-body><![CDATA[Mapping : InvoiceAddRq/InvoiceAdd/InvoiceLineAdd ?[loop]  "invoice_no/list"
]]></ac:plain-text-body></ac:structured-macro>
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 rather than creating one Invoice for each row.

LOWER
(String)

Returns the lowercase value

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
(stringText, stringValue, intSize)

Left Padding of the String with the value passed in for a specified size.

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
(stringText,stringSplitter)

Returns the Split value of the string starting from first character to the splitter specified.

Mapping: TargetField = LSPLIT(VALUE("SourceField"), "string")

Example:
LSPLIT ("tech-on-the-net", "-")
would return "tech"

MEMLOOKUP
(StringCacheIdentifier, StringKey)

This function identifies the value against the key stored in memory table created by MEMTABLE() function. 

Parameter1: StringCatcheIdentifier is the name of Catcheidentifier used in MEMTABLE() function

Parameter2: String Key is the

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") )

MEMTABLE
(StringCacheIdentifier, StringQuery)

This Functions creates a memory table in DBSync for Key Value pair for the Query provided.

Parameter1: StringCacheIdentifier is the memory table where the key and value pair is saved.

Parameter2: String Query is the query to be executed.
The query should have only 2 columns. First column is value and second column is key column.

Select columnName1, columnName2 from object where condition. where condition is optional.

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")

NUMBER
(String)

This function returns 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,Boolean)

Returns the Boolean value of the condition of 2 parameters passed in.

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 weather 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")

REPLACE
(String, Int, Int, String)

Returns the value with replacing the newText in oldText for specified start index and number of chars.

First Param: Old String

Second Param: Start Number

Third Param: Number of Chars

Fourth Param: Replace String

Mapping: TargetField = REPLACE(VALUE("SourceField1), 1, 5, "replace string")

Example1: STRING PARAMETER

In this example, I will pass string "seaaarch" as first parameter, 3 as start number for second parameter, 3 as number of characters to be replaced for third parameter and "a" as the replace string. The REPLACE() function will replace three characters from 3^rd character to 5^th character in 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.

RIGHT
(String)

Returns the last char 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, Int)

Returns the number of chars specified from right of the 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.

PARENTVALUE()

This function gets the value of the column which is in the higher level of the XML Structure.

REPT
(String, Int)

Returns the string with repeated string value for number of times specified.

Mapping: <TargetField> = REPT(VALUE("SourceField1"), 2)

Example: 
REPT("tech", 2) 
would return "techtech"

SEARCH
(findText, withinText)

Returns int value of Boolean if the findText exists in withinText

Mapping: <TargetField> = SEARCH(VALUE("SourceField1"), VALUE("SourceField2"))

Example1: STRING PARAMETERS

In this example, I will pass 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
(findText, withinText,startNum)

Returns the int value of Boolean, if the findText exists in withinText from the startNum of withinText.

Mapping: <TargetField> = SEARCH(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 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_PUT
(KEY, VALUE)

This function stores the key/value pair in the session of that particular state/workflow which is active. This can be accessd from any mapping within the same active state/workflow.

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")

In the above example we are storing a static key/value pair for an AccountName. This assigns the value Avankia to the key "Account_Name" which can be retrieved using SESSION_GET variable

SESSION_GET
(KEY)

This function returns the value of the key sotred in the session of that particular state / 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".

SETATTR
(colName, attName, attValue, 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"))

TODAY()

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

TLOOKUP
(stringQuery)

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.

UNIQUEFIELD
(stringColName, stringAttValue,stringColValue)

Sets the column with 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)

Returns the Uppercase String value

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 database. You can use UPPER() function to get length of FirstName or LastName.. If the VALUE for FirstName is "Dbsync" the resulting string will be "DBSYNC".

TargetField = UPPER(VALUE("FirstName")

VALUE
("FieldName")

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 target field.
<TargetField> = VALUE("Account/Name")

NULL VALUE UPDATE

VALUE("Xpath",true/false)

The mapping formula is used to update NULL values into Target field / column based on empty data in the source field. 

MAPPING: TargetField = VALUE("Xpath",true)

Example 1
Reader : Salesforce Adapter
Writer : Database Adapter
Reader Query: Select Id, Name from Account

The above query will select ID and Name from Account object in Salesforce. You can use the above formula to map the target database column and if you want to update the database column, say "Name" with NULL value based on Salesforce empty field data, you can do the following and it will overwrite the existing database COLUMN data with a NULL value.

Name = VALUE("Name",true)

Example 2:
Reader : Salesforce Adapter
Writer : Great Plains Adapter
Reader Query: Select Id, Name from Account 

The above query will select ID and Name from Account object in Salesforce. You can use the above formula to map the target Great Plains field and if you want to update the Great Plains field, say "Name" with NULL value based on Salesforce empty field data, you can do the following and it will overwrite the existing Great Plains field data with a NULL value.
Name = VALUE("Name",true)
Example 3
Reader : Database Adapter
Writer : Salesforce Adapter
Reader Query: Select Id, Name from dbaccount

The above query will select ID and Name from database table dbaccount You can use the above formula to map the target SF date field say "Date__c" with NULL value based on dbaccount "createdDate" empty field data, you can do the following and it will overwrite the existing SF field data with a NULL value.
Date__c = VALUE("createdDate",true)