        Mapping Manager > 

             Maps  >  
            
             Map Editing  >  
            
             Functions Reference

Functions Reference

This reference lists all the Functions under Tools > Functions on the Mapping page that are available for use when editing field definitions. While there is no solid datatype concept, some functions do expect specific "types" (for example, @NUM expects a number or mathematical expression).

All functions are preceded by the "@" symbol.

@ABS(Value)

Value: Token or hardcoded value.

The "absolute" function or @ABS simply acts like finding the absolute in mathematics, by taking the token or value and converting it into a positive number.

Example: @ABS(-100) would return 100.

@CASE(Value,DefaultTranslation,CompareValue1,TranslationValue1,...)

Value: Token or hardcoded value.
DefaulTranslation: Value/result to use if no match is found (fallback value)
CompareValue1: Comparison value/token against Value variable
TranslationValue1: Return value/token if CompareValue1 equals true

@CASE allows logic that will look at a single token and compare it against multiple values in which will return the most appropriate result based on that comparison. Only a single return will result at any given time

@COPY(Value,StartPosition,CopyLength)

Value: Token or hardcoded value to alter
StartPosition: Position within the Token to start at
CopyLength: How many characters to copy

@COPY takes a given value, and will return a new value based on where and how many characters you wish to copy, such as producing only part of a value or to cap the length of a value

This also very useful in basing logic specifically on a value in a token.

Example: @COPY([T:STORE_NUMBER],9,4)

Where 9 is the starting position value for the store number, and 4 will be the returning value of the statement. Therefore, if the store number is 0078742030982, then the return value would be 0982.

@DATEADD(Date,DaysToAdd,SkipWeekends)

Date: Token/date value to modify
DaysToAdd: How many +/- days to Add/Subtract from Date Token
SkipWeekends: 1 to skip weekends. 0 (or omit) to indicate weekends are not skipped

@DATEADD modifies a given date by adding or subtracting days to it, with the option to skip weekends. See Using @DATEDD for usage examples.

@ERROR(String)

Returns a string message when set up for custom error logic. Used primarily for flat files but can be utilized for frameworking integrations as well. There is a 250 character limit on the message.

Example 1: @IF_THEN_ELSE([T:DATE_PURCHASE_ORDER],<>,,[T:DATE_PURCHASE_ORDER],@ERROR(Purchase Order Date was not provided for PO# [T:PURCHASE_ORDER_NUMBER]))

Example 2 @IF_THEN_ELSE(@NUM([T:ITEM_QUANTITY]),>,@NUM(0),[T:ITEM_QUANTITY],@ERROR(Item Quantity must be greater than 0 for [T:ITEM_UPC]))

@IF_THEN_ELSE(Value1,CompareType,Value2,ValueIfTrue,ValueIfFalse)

Value1: First Value/Token to compare
CompareType: Comparison Operator
Value2: Second Value/Token to compare
ValueIfTrue: Result to return if Value1 compares to Value2 based on CompareType
ValueIfFalse: Result to return if comparison of Value 1 and Value 2 failed

@IF_THEN_ELSE evaluates a comparison of values. If the statement is true, it will return the value that is set for true, or else it will return the value that is set for false.

Comparison operators include <, >, =, <>, <=, >=. Note that the comma represents the ELSE function. If you want to return nothing, add a second comma after the first one.

Example: @IF_THEN_ELSE([I: Account ID],=,ACE,[T:DISTRIBUTOR_ADDRESS_CODE],[T:STORE_NUMBER])

Where if the trading partner is 'ACE', this returns the value of the DC code, or else (the trading partner is not 'ACE') this returns the store number. If you wanted to return nothing (instead of the store number) you would use 'ACE,,' in the statement instead.

@IF_AND_THEN_ELSE(Value1,CompareType,Value2,Value3,CompareType,Value4, ... ValueIfTrue,ValueIfFalse)

Value1, Value2, Value3 ... : Values/Tokens to compare
CompareType: Comparison Operator
ValueIfTrue: Result to return if Value1 compares to Value2 based on CompareType
ValueIfFalse: Result to return if comparison of Value 1 and Value 2 failed

@IF_AND_THEN_ELSE evaluates multiple sets of comparisons to return true before returning the true value.

Comparison operators include <, >, =, <>, <=, >=. Note that the comma represents the ELSE function. If you want to return nothing, add a second comma after the first one.

Example: @IF_AND_THEN_ELSE([I: AccountID],=,ACE,([T:TRANSACTION_TYPE_CODE],=,SO,[T:DISTRIBUTOR_ADDRESS_CODE],[T:STORE_NUMBER])

Where if both the trading partner is ACE and the transaction code type is SO, then this will return the value of the DC code, or else it will return the store number.

@IF_OR_THEN_ELSE(Value1,CompareType,Value2,Value3,CompareType,Value4, ... ValueIfTrue,ValueIfFalse)

@IF_OR_THEN_ELSE allows any condition to evaluate as true in order to return the true value.

Comparison operators include <, >, =, <>, <=, >=. Note that the comma represents the ELSE function. If you want to return nothing, add a second comma after the first one.

Example: @IF_OR_THEN_ELSE([I: Account ID],=,ACE, ([I: Account ID],=,ACE2,[T:DISTRIBUTOR_ADDRESS_CODE],[T:STORE_NUMBER])

Where if the trading partner is 'ACE' or 'ACE2', then this will return the value of the DC code, or else it will return the store number.

@INT(Value,Shift,Size)

Value: Token or hardcoded value.

@INT turns a value into an Integer

@INSTR(String, Substring)

String: Token or hardcoded value to search against
Substring: Character/word to look for within Token

@INSTR finds the starting position of specific character or Word.

Example: @INSTR([R:4.10 Item.UserField1],Tracking #)

This returns the position count where 'Tracking #' first appears in the field. That would be the position count of the 'T' in 'Tracking #'. See Using @INSTR to Parse Data for examples.

@IS_NUMERIC(Value)

Value: Token or hardcoded value.

@IS_NUMERIC returns 1 if value is fully numeric or 0 if it contains string-based characters.

@LEN(string)

String: Token or hardcoded value.

@LEN returns how many characters make up a string.

Example: @LEN([T:ITEM_QUANTITY])

Where @LEN(100) would return a value of 3.

@LOWER_CASE (Value)

Value: Token or hardcoded value.

@LOWER_CASE converts any upper-cased characters to lower

Example: @LOWER CASE([T:SHIP_TO_NAME])

@NUM(TokenCalculation)

String: Combination of token or hardcoded value.

@NUM serves as a wrapper for performing mathematic calculations within a field. Operators include +, -, *, / . Rules:

Should not have spaces between tokens and operator
Only a single math-operation within @NUM statement
Nesting @NUM statements to perform multiple calculations

See Using @NUM for usage examples.

@NUM(@SUM(@NUM()))

@NUM(@SUM(@NUM())) returns the sum of all entries.

Example: @NUM(@SUM(@NUM([ITEM_QUANTITY]*[ITEM_PRICE])))

This returns the sum of all lines L1(Qty*Price)+L2(Qty*Price)+L3(Qty*Price)…etc.).

@REAL_ROUND(Token,DecimalPlaces)

@REAL_ROUND rounds a value to the decimal place that is set.

Example: @REAL_ROUND([T:ITEM_UNIT_PRICE],2)

@REAL_ROUND rounds Token up to the specific DecimalPlaces defined. Results uses Banker's Rounding (rounds from .6 and up, rather than .5).

@REAL_TRUNCATE(Token,DecimalPlaces)

@REAL_TRUNCATE truncates a value to the decimal place that is set.

Example: @REAL_TRUNCATE([T:ITEM_UNIT_PRICE],2)

Where if the unit price sent was 5.678, the return value would be 5.67.

@REPLACE_STRING(String,SearchFor,ReplaceWith)

String: The token to modify/search
SearchFor: Character/value/word to find
ReplaceWith: Character/value/wword to replace SearchFor value with(if found)

@REPLACE_STRING finds a character/value/word and replaces it with the desired text

Example: @REPLACE_STRING([R:1.9 Header.Bill of Lading],Z,A)

Where if BOL is '1Z2008FQ300', the value returned would be '1A2008FQ300'.

@STRING(Value)

Value: Token or hardcoded value.

All values, including numbers and calculations, are treated as strings.

@STRIP_CHARS(String,CharactersToStrip)

String: The token to modify/strip from
CharactersToStrip: Characters (individual) to strip

@STRIP_CHARS removes specified characters from a token. CharactersToStrip is a list, not a single word. That is, if you enter "ABC" it will not strip all words of ABC, but rather strip all As, all Bs, and all Cs from your incoming Token regardless of their position.

Note that the ‘ character needs to be used to remove commas.

Example: @STRIP_CHARS([T:SHIP_TO_NAME],#A)

This strips out all '#' and 'A' characters found in the ship to name.

@SIGN(Value)

@SIGN returns a sign (+ or -) based on the value specified.

Example: @SIGN([T:ITEM_QUANTITY])

Where @SIGN(100) returns a + sign.

@SUM(Value)

@SUM totals all found entries of Value.

Example: @SUM([ITEM_QUANTITY])

If two line items were sent on the PO of 4 and 6, the above statement would return the value of 10.

@SWITCH(String1,String2, String3, …)

@SWITCH tooks to the first token set in the statement. If that is not present on the transaction then it will look to the next token set in the statement, and so on.

Example: @SWITCH([T:STORE_NUMBER],[T:SHIP_TO_ADDRESS_CODE])

This first attempts to return the store number value. If the store number is not there, it will return the ship to code.

@TRIM(String)

@TRIM removes all spaces in a field.

Example: @TRIM([R:3.4 Item.VendorNum])

Where if [R:3.4 Item.VendorNum] is ' AB CD ', this would return 'ABCD'.

@TRIM_LEFT(String)

@TRIM_LEF removes all spaces to the left of a field.

Example: @TRIM_LEFT([R:3.4 Item.VendorNum])

Where if [R:3.4 Item.VendorNum] is ' ABCD', this would return 'ABCD'.

@TRIM_RIGHT(String)

@TRIM_RIGHT removes all spaces to the right of a field.

Example: @ TRIM_RIGHT([R:3.4 Item.VendorNum])

Where if [R:3.4 Item.VendorNum] is 'ABCD ', this would return 'ABCD'.

@UNESCAPE(String)

@UNESCAPE inserts a carriage return on an exported transaction. Use @UNESCAPE(/n) to input it into the field in Mapping Manager.
You must set the field in the Entities to “AllowTextFormattingCharacters”.

@UPPER_CASE(String)

@UPPER_CASE returns the data held within a token as all upper case.

Example: @UPPER CASE([T:SHIP_TO_NAME])

Usage Examples

Using @DATEADD

To a add 3 days to the current date: @DATEADD([T:SPECIAL_TOKEN_CURRENT_DATE],3)

To subtract 1 day from the requested delivery: @DATEADD([T:DATE_REQUESTED_DELIVERY],-1)

If 5/29/2024 is a Wednesday. Function results are as follows:

Add: @NUM([T:Main.Charges.Total of Line Items]+[T:Main.Charges.Other Charges])

Subtract: @NUM([T:Items.Price]-[T:Items.Promotional Allowance])

Multiply: @NUM([T:Items.Quantity]*[T:Items.Price])

Divide: @NUM([T:Items.Quantity]/[T:Items.# Inner Pack])

Nested: @NUM([T:Items.Price]*@NUM([T:Items.Quantity]/[T:Items.# Inner Pack]))

Using @INSTR to Parse Data

The @INSTR function provides TrueCommerce users with the ability to pull multiple data elements from one field when there is a variable length.

Scenario 1

Amazon is sending the first and last name of the customer in one ship to name field which need to be separated into a first name and last name field in your TrueCommerce system.

Two questions before writing the statement: 1) What character(s) separate the values? 2) Is this character always present? If the value you are looking for via @INSTR is not always present, then an ‘if’ statement will be needed to check that the character is present.

Using 'Andrew Cutch' for this example, first and last name are separated by a single space and are always sent in this format:

First Name. This copies everything to the left of the space ...

@COPY([T:SHIP_TO_NAME],1,@INSTR([T:SHIP_TO_NAME], )-1)

... and the result is 'Andrew'.

Last Name. This copies everything to the right of the space ...

@COPY([T:SHIP_TO_NAME],@INSTR([T:SHIP_TO_NAME], )+1,255)

... and the result is 'Cutch'.

Scenario 2

Length, width, and height are kept in the same field in your TrueCommerce system. The trading partner needs to receive these as three separate values.

Using '10x11x12' for this example, values are separated by an 'x' that is always present:

Length. This copies everything to the left of the first 'x' ...

@COPY([R:1.21 Dimension],1,@INSTR([R:1.21 Dimension],x)-1)

... and the result is '10'

Width. This copies everything between the first and second 'x' ...

@COPY(@COPY([R:1.21 Dimension],@INSTR([R:1.21 Dimension],x)+1,255),1,@INSTR(@COPY([R:1.21 Dimension],@INSTR([R:1.21 Dimension],x)+1,255),x)-1)

... and the result is '11'

Height. This will copy everything after the second 'x' ...

@TRIM(@COPY(@COPY([R:1.21 Dimension],@INSTR([R:1.21 Dimension],x)+1,255),@INSTR(@COPY([R:1.21 Dimension],@INSTR([R:1.21 Dimension],x)+1,255),x)+1,255))

... and the result is '13'