Mapping Options in Rapidi

The purpose of mappings is to transform data as it moves from source to destination and convert it to the expected format and structure. Within Rapidi, there are different mapping options available such as 1:1 mappings, formulas, hardcoding, lookup tables, etc.

A.1:1 Mappings

  • Purpose: A 1:1 mapping is a direct mapping where each source field is mapped directly to a destination field and it is ideal for fields that do not require transformation.
  • Example: Map a "Customer ID" field from an ERP system directly to the "Customer ID" field in a CRM system.
  • Usage: It is recommended when you have identical fields with compatible data types and formats).

B. Formula-Based Mappings

  • Purpose: Formulas are used to manipulate data before it reaches the destination field. They are being used for data transformation, concatenating values, performing lookups, formatting the data,etc.
  • Example:
    1. Concatenate first and last name fields to create a full name field.
    2. Perform lookups on related tables to obtain a value that’s not available on your current table;
    3. Apply formatting to fields so they meet the destination system’s requirements;
    4. Skip certain field values from being transferred to the destination system;
  • Syntax and Usage: Formulas are always added under Field List Mapping on the left side and all formulas are preceded by double hashes ##. 
  • Formula Examples: 
  • ##BLANKVALUE(DBLOOKUP('SOURCEDS','User','Id',"OwnerId",'NAV_Salesperson_Code__c'),'ABC')
   It is being used in combination with other formulas/expressions. If the result of your main lookup is NULL, then the formula will display the result indicated in the formula (‘ABC’) or another formula/expression can be added to account for those situations when the result of the initial lookup is NULL.

  • ##COPY(“Name”,0,10)
  It is a very common formula used to limit the number of characters or digits being sent to the destination system. So the value found in the Name field will be limited to 10 characters or digits. It is used when the destination system has a different limitation in terms of the field length and the total number of allowed characters/digits. 

  • ##DBLOOKUP('DESTDS','Salesperson/Purchaser','ExternalID',"OwnerID",'Code')
  This is a lookup performed on the destination system on the table called Salesperson/Purchaser where if 'ExternalID' = OwnerID from the current table, then the Code will be the result. 

  • ##DBLookup('SOURCEDS','Salesperson_Purchaser','Code',"Salesperson Code",'Name')
  This is a lookup done on the SOURCE system on the table Salesperson_Purchase where if 'Code'=Salesperson Code and the result will be Name.

  • ##LSLOOKUP('%%LSUSER%'+'#REVERSE',"OwnerId").
  This is a lookup performed on the existing values from the indicated Link Storage (‘LSUSER’) where you get the corresponding value of the field OwnerId. All the above are lookup formulas ( either on the source, destination or Link Storage) that can be used to get the data that is needed and which is not directly available on the current table/object. 

Usage: Formulas are beneficial, such as applying business logic or adjusting values for destination compatibility.

C. Hardcoding Values

  • Purpose: Hardcoding means setting a fixed value for a destination field, regardless of source data. Useful for setting default values, status flags, or other constants.
  • Example: Assign a constant value to indicate a data source or system identifier, like setting a Region field to "US" for all records.

In order to hardcode values, you can follow the steps from this article or simply use the section Default Values where you can add a default value for a field that exists in your destination but not in your source system. 

  • Usage: It is being used when there are fields that need a default or universal value that isn’t derived from source data.

D. Conversion List Mappings

  • Purpose: Conversion Lists are a way to map source values to different destination values based on predefined associations. They are useful for standardizing data formats or values (e.g., converting country codes).
  • Example: Convert "USA" to "United States" or map source-specific codes to standard ones in the destination system.
  • Usage: Scenarios for lookups, such as translating codes or applying standardized naming conventions.
  • Possible options in Rapidi:
  1. Use the ##CASE formula to set up a conversion list : ##CASE("Legal_Entity__c",'SE','EXT','DK','EMT','FR','EXM','NO','EXS','DE','EXR',SKIPFIELD())
  2. Use Conversions (list of from-to values to convert) field where you define the field values from source and destination and Rapidi will convert them accordingly. 

E. Data Type Conversion

  • Purpose: Rapidi allows conversion between data types (e.g., integer to string, date formatting). It ensures compatibility between source and destination systems when data types differ.
  • Example: Convert an integer date (e.g., 20231114) into a readable date format (11/14/2023).
  • Usage: Depending on the systems that are being integrated, you might need data type conversion, especially with dates, currency fields, and numeric values.
  • Possible options in Rapidi:
  1. Use ##TOINTEGER formula to convert a text field into an integer format;
  2. Use ##CONVERT formula to adjust the data that you need to transfer to the destination system;
  3. Use ##FORMAT formula to transform your data into a desired format in the destination system; It is often used for datetime conversions;


F. Multi-Field Mapping

  • Purpose: Multiple fields in the source can be combined or split to match destination requirements. It is useful for consolidating fields or creating derived fields.
  • Example: Map multiple address fields (street, city, zip) into a single address field in the destination.
  • Usage: Situations where fields are split or combined to meet destination formatting requirements.
  • Possible options in Rapidi:
    1. Use ##PREPEND in combination with other formulas to join the values from 2 different fields when the destination system has only 1 field available: ##"Address"+IF(EQUALS("Address 2",''),'',PREPEND("Address 2",'<13,10>',LENGTH("Address 2")+2))
    2. Concatenate fields or values so they are inserted together into a single destination field: ##'%%COMP%'+'#'+"Document_Type"+'#'+"No" where actual fields are represented by double quotes and any text/string is represented by single quotes. 

G. Conditional Mappings

  • Purpose: Conditional mappings are used to map data based on specific criteria. For example, Filter or transform data based on set conditions.
  • Example: Map only active customers by adding a condition like IF [Status] = "Active".
  • Usage: Conditional mappings can be used when transferring only records that meet certain criteria.
  • Possible options in Rapidi:
    1. Use a ##IF/EQUALS/CASE formulas if you want to transfer only specific records:

##IF(EQUALS(Type,'Item'),Description,SKIPFIELD()); ##IF(EQUALS("Currency Code",''),Amount,"Amount"/"Currency Factor")

##IF(EQUALS("CurrencyIsoCode",''),'%%CURR%',"CurrencyIsoCode")

##CASE(ISEMAIL("E-Mail"),1,"E-Mail",SKIPFIELD())

##IF(ISEMAIL("E-Mail"), "E-mail", '')

  1. Use the Filters Source (delimitations) section to set up the criteria regarding the source records.