This reference lists all the Functions under Tools > Functions on the Mapping page that are available for use when editing field definitions. All functions are preceded by the "@" symbol. As BSPs best emulate 'duck typing,' there is no solid datatype concept. However, some functions expect specific "types" (for example, @NUM expects a number or mathematical expression).
Adds up 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.
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.).
Evaluates real expression. Must assign a token as a number in order to perform a calculation.
Example: @NUM([T:ITEM_UNIT_PRICE]) or @NUM([ITEM_QUANTITY]*[ITEM_UNIT_PRICE])
All values and calculations are treated as strings.
Example: @INSTR([R:2.20 PAW_Invoice.Note],TRACKING #)
This finds the first 'TRACKING #' in the data and returns a character count up to that point. Where if [R:2.20 PAW_Invoice.Note] is 'ABC123 TRACKING #', then the value returned would be 17.
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'.
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'.
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'.
Looks 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.
Returns specific characters of a token, which is 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.
Returns a sign (+ or -) based on the value specified.
Example: @SIGN([T:ITEM_QUANTITY])
Where @SIGN(100) returns a + sign.
Finds the starting position of a substring.
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 usage examples.
Returns length of field.
Example: @LEN([T:ITEM_QUANTITY])
Where @LEN(100) would return a value of 3.
Returns the absolute value of a token.
Example: @ABS([T:ITEM_QUANTITY])
Where @ABS(-100) would return 100.
Removes specified characters from a token. 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.
Returns the data held within a token as all upper case.
Example: @UPPER CASE([T:SHIP_TO_NAME])
Returns the data held within a token as all lower case.
Example: @LOWER CASE([T:SHIP_TO_NAME])
Finds a substring and replaces it.
Example: @REPLACE_STRING([R:1.9 Header.Bill of Lading],Z,A)
Where if BOL is '1Z2008FQ300', the value returned would be '1A2008FQ300'.
Rounds a value to the decimal place that is set.
Example: @REAL_ROUND([T:ITEM_UNIT_PRICE],2)
Where if the unit price sent was 5.678, the return value would be 5.68.
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.
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 a 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.
Evaluates two sets of comparisons. If either 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 <, >, =, <>, <=, >=.
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.
Evaluates two sets of comparisons. If both statements are 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 <, >, =, <>, <=, >=.
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.
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]))
The @INSTR function provides TrueCommerce users with the ability to pull multiple data elements from one field when there is a variable length.
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'.
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.
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 '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'
Related Topics