Formula Cheat Sheet & Guide

How to write formulas for Computed Attributes

Computed Attributes help you transform your attribute outputs for Channels, E-Catalogs, and PDF Templates. In this guide, you will learn how to use the formula editor, and what operations are available. 

How to use the Formula Editor

After you have created your Computed Attribute, you can start editing the formula by clicking on the options icon next to the attribute you want to configure. 

Once you have done this, you should see this screen slide out from the right side of the page: 

Formula-Editor-Start

In the Formula Editor, you will see a text box. This is where you write your formulas using the available operations, your existing attributes, and text inputs. 

Formula syntax, or how a valid formula is written, is based largely on Excel, Google Sheets, and Python. 

How to write a formula

  • Use operations
  • Enclose operations in parentheses
  • Insert system attributes by using the $ symbol
  • Insert your custom attributes by using the $ATT. prefix 
  • Insert line breaks using ⌘ or CTRL, Enter

    The formula editor will help you with autosuggest for both operations and attributes. 

    Auto-suggest for Operations
    operations-auto-suggest

    Auto-suggest for Attributes

    attributes-auto-suggest

    How to validate a formula

    When you validate a formula, you are asking the system, "Is this formula written correctly?", "Do you understand this?" 

    Formula-Editor-Validate

    To validate a formula, once you are finished editing, click VALIDATE. 

    Then, one of two things will happen. Either you will get an error, explaining why the formula isn't correctly written, or you will see the attribute testing area below the Formula Editor

    You can also click TEST AND APPLY in the top right corner to validate the formula and close the window. 

    How to test formula attributes

    If your formula has been validated, then you will see this area appear below the Formula Editor, and the Editor will be disabled. 

    Formula-Editor-test-attributes

    This area lets you test different possible inputs for your Computed Attribute to make sure your formula gives you the correct results.

    Attributes without fixed inputs, like numbers or texts, will have dummy data presented, while those with fixed inputs like multi-select or dropdown, will show your existing inputs.

    To test attributes: 

    1. Check or change the data for each attribute so that you can test the outcome
    2. Click RUN
    3. Check the result at the bottom of the testing area and repeat the process as needed

     

    List of Operations

    Here are all the available operations listed in alphabetical order. Click each link to take you to the operations page in a new tab where you can see the definition, syntax, and examples.

    1. ADD_DAYS - Add days to a date
    2. ADD_MONTHS - Add months to a date
    3. ADD_YEARS - Add years to a date
    4. AND - Test multiple conditions
    5. AVERAGE - Find the average in a numerical dataset
    6. CONCAT - Concatenate multiple values
    7. COUNTIF - Count the number of occurrences of a value in a string
    8. DATE_FORMAT - Change the data format
    9. DECIMAL_FORMAT - Change the decimal format
    10. DIVIDE - Divide one number by another
    11. DLOOKUP - Replace values from a dictionary
    12. EQ - Verify that two values are equal
    13. ESCAPEHTML - Remove all HTML tags from a text
    14. ESCAPESTYLE - Remove all styling from a text
    15. FILTER - Return a list of items that achieve certain conditions
    16. FIND - Identify the first position of a string found in a text (case sensitive)
    17. GT - Strictly greater than
    18. GTE - Greater than or equal to
    19. IF - Return a value based on a set of conditions
    20. IFBLANK - Return a specified value if blank or not
    21. IFERROR -  Return a specified value if error or not
    22. IFNULL - Return a specified value if null or not
    23. IS_SUBSTR - Test if a string is found in a particular text
    24. ISBLANK - Test if a value or field is empty
    25. JOIN - Concatenate values with a delimiter
    26. LEN - Count the characters in a string
    27. LOWER - Convert a string of text to all lowercase
    28. LT - Strictly less than
    29. LTE - Less than or equal to
    30. MAP - Apply an operation to a list of inputs
    31. MAX - Find the maximum value in a numerical dataset
    32. MIN - Find the minimum value in a numerical dataset
    33. MINUS - Subtract one number from another
    34. MROUND - Round a number to the nearest integer multiple
    35. MULTIPLY - Return the product of two numbers
    36. NE - Not equal
    37. NOT - Returns the opposite of the provided logical value
    38. OR - Test multiple condition options
    39. PROPER - Capitalize the first letter in each word of a text
    40. REPLACE - Replace part of a particular string with a different string
    41. ROUND - Round a number to a specified decimal place
    42. RSUBSTITUTE - Replace the last occurrence of a string in a text
    43. SEARCH - Find the first position of a string found in a text (not case sensitive)
    44. SUB_DAYS - Subtract days from a date
    45. SUB_MONTHS - Subtract months from a date
    46. SUB_YEARS - Subtract years from a date
    47. SUBSTITUTE - Replace existing text with a new text in a string (case sensitive)
    48. SUM - Add a set of numbers together
    49. TRIM - Remove all spaces in a text string, leaving just a single space between words
    50. UPPER - Converts a string to all UPPERCASE