SCRIBE Common Functions and Formulla

Scribe is a data migration platform which supports many data sources to migrate data e.g. Dynamics CRM, Salesforce, ODBC, SQL, NAV, GP, AX etc

Here are some common functions/formullas we used while designing DTS

FILELOOKUP
Syntax: FILELOOKUP( TextSourceField, "filename", "section" )

Source Value (S1) = “Customer”

Function = FILELOOKUP (LOWER(S2), “Mapping.INI”, “Relationtype”)

Result = 2

Create a .INI file in the same folder where your DTS resides and edit it as following

Mapping.INI

[Relationtype]

customer=1
supplier=2

DBLOOKUP

Syntax: DBLOOKUP( TextSourceField, "connection", "table", "lookup_field", "substitution_field" )

Description: Look up result from the substitution_field of the connection based on the lookup_field value, if it has more than one result then it will return top one

Source Value (S1) = “ABC Compnay”

DBLOOKUP( S1, "CRM2011", "account", "name", "emailaddress1" )

Result: bob@abccompany.com

          DBLOOKUP2   
          Syntax : DBLOOKUP2( SourceValue1, SourceValue2, "connection", "table", "lookup_field1", "lookup_field2, "substitution_field" )
 
        Description: Similar to the DBLOOKUP function, except that it accepts two lookup values and fields.

Source Value (S1) = “ABC Compnay”

Source Value (S2) = “Active”

          DBLOOKUP2( S1, S2,"CRM2011", "account", "name","statecodename", "emailaddress1" )

          Result: bob@abccompany.com

 FORMAT

Source Value (S1) = 7082347901

Function = FORMAT(S1, “(###) ###-####”)

Result = (708) 234-7901

ENDJOBRETRYMSG

Syntax: ENDJOBRETRYMSG( error message)

Description: this function ends the job and sends the message back into the queue and log custom error message.

It is useful in an integration processes when one process message is dependent on parent entity message e.g. IN Dynamics CRM you do not want to create a contact if it does not have parent customer in the system and parent customer (Account) is coming into the system from another integration process. So in this situation you need to send the contact to retry queue with custom message like “Account not found!” until an account message goes to CRM

ISERROR
Syntax: ISERROR (SourceField)

Description: this function is used with IF conditions to check null value which returns True if it is null otherwise return False

GOTOSTEP
Syntax: GOTOSTEP (<stepnumber>)

Description: This function causes a jump to a specified step number