After configuring SFTP, you need to configure the customer or transaction block to map the headers of the CSV file with that of the Capillary system and automate data transformations.

According to the type of data of the selected template, you will see configuration options.

  1. In InTouch org, select the org ID for which you want to configure data mapping/transformation.
  2. In File Delimiter, enter the character used to separate each field in the CSV file - comma or tab. It is usually a comma (,).
  3. To convert the date format of date entries of the CSV file, check Format Transaction Date and select your preferred date format in Transaction Date Format.

 4. Configure each tab for the respective field mappings and data transformations.

  1. extendedFields: Contains all transaction and line item level extended fields. Map each of the extended fields with the extended fields in the CSV file.
  2. transaction: Contains all transaction fields. Map each of the transaction fields with the names in the CSV file.
  3. attribution: Contains attribution headers.
  4. lineitem: Contains all transaction line-item fields. Map each of the transaction line item fields with the names in the CSV file.
  5. filter: Available for templates with Filters. For example,  Transaction Add V2 with filter.  
  6. customFields: Contains all transaction and line item level custom fields. Map each of the custom fields with the custom field names of the CSV file. 
  • Transformation: This is a field that allows you to automate the process or transforming data of the respective field. For example, you can have a net transaction amount which is the gross amount*quantity-discount. For any such automated computations for a field/entry, you can use the transformation column - To add or update transformation, click on the respective Edit icon.

To know more about header mapping, data transformation expressions, and filter expressions with examples, navigate to the respective section.

  1. Click Continue to save and proceed to Step 3 to configure API authentication details. 


Data Transformation using mapping & expressions

Attribution Headers

File headers mapping to API store or till attribution headers

Example 1: Attribute using Till Code 

  • Header: Till_IUN 
    • identifierType: const(TILL_CODE) 
    • identifierValue: Till IUN 

Example 2: Attribute using Store Code 

  • Header: Store Code 
    • identifierType: const(STORE_CODE) 
    • identifierValue: Store Code

Filter Expressions

Example: Process dataflow only if a transaction is paid or refunded.

  • HeadersFinancial Status 
  • Filter header mapping (header): Financial Status 
  • Filter expression: ${header_value:equals('paid'):or(${header_value:equals('refunded')})} 

Data Transformation

Header Mapping

Mapping the API fields billAmount and billNumber with headers

  1. Header: BILL AMOUNT;  Value Mapped: BILL AMOUNT 

Assign a constant value

OperatorExpression FormatExample
constconst{header name}1. Expression to set the source as INSTORE:
2. Expression to set Transaction Type as Not Interested: 

Arithmetic expressions

Supported OperatorsExpression FormatExample
exp{hdr{header 1} [operator] hdr{header 2}}
Ensure the open or close bracket '{' '}' 
Note: Ensure that the braces are matched properly (opened and closing).

Example 1: Compute Net Amount 

  • Headers: LineItem_Amount, LineItem_Discount 
  • Expression: exp{hdr{LineItem_Amount} - hdr{LineItem_Discount}}

Example 2: Compute net amount based on quantity
  • Headers: LineItem_Amount, Quantity
  • Expressionexp{hdr{LineItem_Amount} * hdr{Quantity}}

Example 3: Currency Conversion: INR to DOLLAR

  • Headers: LineItem_Amount
  • Expressionexp{hdr{LineItem_Amount} / 74}

String operations

Supported OperatorsExpression FormatExample
exp{string expression1.concat(string expression2)}Example 1: Concat two or more headers for Description.
  • Headers: Item_Name, Item_Purpose
  • Expression: exp{'hdr{Item_Name}'.concat('hdr{Item_Purpose}')} 

Example 2: Concat two or more headers with fixed info for Item Description. 
  • Headers: Item_Name, Item_Purpose 
  • Expression: exp{'hdr{Item_Name}'.concat(' is used for ').concat('hdr{Item_Purpose}')}

Example 3: Remove '+' from the mobile number if exists. 
  • Headers: Mobile_No 
  • Expression: exp{ ('hdr{Mobile_No}'.startsWith('+')) ? 'hdr{Mobile_No}'.substring(1) : 'hdr{Mobile_No}' }

Conditional Operations

OperatorExpression FormatExample
? :exp{(conditional expression) ? expression1 : expression2}Example 1: Check if the length of MOBILE number is 10, add country code '+91' otherwise return the existing value.
  • Headers: Mobile_No
  • Expression exp{('hdr{Mobile_No}'.length() == 10) ? '+91'.concat('hdr{Mobile_No}') : 'hdr{Mobile_No}' }

Example 2: Check if an item is returned: if yes, set transaction type value 'RETURN' else 'REGULAR' 
  • Headers: Is_Returned (Possible values 'true' / 'false') 
  • Expressionexp{ ('yes'.equalsIgnoreCase('hdr{Is_Returned}')) ? 'RETURN' : 'REGULAR' }

Complex expressions

--Example 1: Compute Non-fraction Qualified quantity based on Product Category.
if Product Category is any of FUEL, JEWEL, METAL then qualified quantity would be the difference of LineItem_Amount and LineItem_Discount divided by LineItem_Rate else it would be LineItem_Qty
  • Headers: Product_Category, LineItem_Discount, LineItem_Amount, LineItem_Rate, LineItem_Qty 
  • Expression: exp{ ({'FUEL','JEWEL','METAL'}.contains('hdr{Product_Category}') ) ? T(Math).floor( (hdr{LineItem_Amount} - hdr{LineItem_Discount}) / hdr{LineItem_Rate}) : T(Math).floor(hdr{LineItem_Qty}) }