## Product Content Formulas

Use this reference to find specific formulas and syntax. Click a button to filter to specific formula types, sort by any column, and as you type in the search box, the results will automatically narrow to those that match your text.

Check out Anatomy of a Formula for full details, including a step-by-step video, on how the pieces of a formula work.

#### Copying & Using Formulas from the Cheat Sheet

**Formula & Syntax Column**

When copying a formula from the table below, replace what's inside <brackets>, including those brackets. So for example, for `VALUE("<property_ID>")`

, if your property ID is *Brand, *you replace `<property_ID>`

including the brackets, and the formula would be `VALUE("Brand")`

.

**Examples & Output Column**

Refer to the output below the formulas for what to replace. The pieces in italics are what you replace with your own values. In this example:

`CONCATENATE(VALUE("Brand"), ": ", VALUE("Product Name"))`

Where *Brand* is Acme and *Product Name* is Anvil, output is Acme: Anvil

Replace *Brand* and *Product Name* with your property IDs.

Formula Type | Formula & Syntax | Definition | Example & Output |
---|---|---|---|

Working with Numbers | ADD`ADD` `("<propertyID>", "<propertyID>"...)` |
Arithmetic function applied to numeric property values to obtain calculated values. | `ADD(VALUE("MSRP"),VALUE("Surcharge")` Where MSRP is 19.99 and Surcharge is 2.00, output is 21.99. |

Conditionals | AND`IF` `(AND("<propertyID1>" ` |
Allows testing two values in relation to each other in a formula. Both values must be true for the result to be true. | `IF(AND(EQUAL(VALUE("Country of Origin"), "United States"), EQUAL(VALUE("Country of Assembly"), "United States")), "Made in USA", "Imported")` Where both Country of Origin and Country of Assembly are United States, output is United States. If both are not United States, output is Imported. |

DIgital Asset URLs & Metadata | ASSET_VALUE`ASSET` `_VALUE("<propertyID>","<metadata attribute>","<N>")` |
Returns the value of the attribute for the Nth digital asset in the property. System metadata properties must be prefixed with salsify:. Click here for the list of Salsify system metadata properties. | `ASSET_VALUE("Hero Image", "salsify:Height", 1)` Where the first digital asset in Hero Image has a height of 500, output is 500. |

Arrays & Advanced Formulas | AT`AT` `(["<propertyID1>", "<propertyID2>", "<propertyID3>"], 3)` |
Allows you to return the Nth value of an array. | `AT(["Red", "Blue", "White"], 3)` Output is White. |

Pulling Out Values | CHANNEL_STATUS_FROM`CHANNEL` `_STATUS_FROM(<channel_id>)` |
Returns channel status string value for the most publish, if it exists.Returns empty/null value if no status is available from the channel,or product has never been published through the channel. To find channel ID, navigate to the channel and the ID is in URL path after /channels/.Function is not currently available for in-app computed properties. | `CHANNEL_STATUS_FROM(11234)` Where current channel status is "Published", output is "Published". |

Pulling Out Values | CLEAN_TEXT`CLEAN` `_TEXT(VALUE("<propertyID>"))` |
Removes any special and/or hidden characters, including trademarks and/or copyright symbols. | `CLEAN_TEXT(VALUE("Brand"))` Where Brand is Acme™, output is Acme. |

Combining & Transforming Values | COALESCE`COALESCE` `("<propertyID1>", "<propertyID2>", ...)` |
Outputs the first value given that is not empty. | `COALESCE(VALUE("Amazon Item Name"),VALUE("Retail Item Name"),VALUE("ERP Item Name"))` Output is the Amazon Item Name value. If it's empty, output is Retail Item Name value. If empty, ERP Item Name value is the output. |

Arrays & Advanced Formulas | COMPACT`COMPACT` `("<formula>")` |
Removes blank values that would be generated by empty properties. | `COMPACT(CONCAT_ARRAYS(VALUES("Feature 1"), VALUES("Feature 2"), VALUES("Feature 3")))` Where Feature 1 is Blue, Feature 2 is blank, Feature 3 is 2 pack, returns Blue and 2 pack on separate lines and doesn't leave a blank line for Feature 2. |

Arrays & Advanced Formulas | COMPOUND`COMPOUND` `("<label1>", "<propertyID1>","<label2>", "<propertyID2>"...)` |
Specify pairs of information, a label and the value that the information is stored in. Output is label:property value | `COMPOUND( "Color Family",VALUE("Color"), "Fabric",VALUE("Material"))` Where Color value is Blue and Material value is Cotton, output is:Color Family:BlueFabric:Cotton |

Combining & Transforming Values | CONCATENATE`CONCATENATE` `("<propertyID1>", "<propertyID2>", ...)` |
Combines together multiple values. You can concatenate as many values as you want, and you can combine literal text values with other Salsify formulas. | `CONCATENATE(VALUE("Brand"), ": ", VALUE("Product Name"))` Where Brand is Acme and Product Name is Anvil, output is Acme: Anvil |

Arrays & Advanced Formulas | CONCAT_ARRAYS`CONCAT` `_ARRAYS(VALUES("<propertyID1>","<propertyID2>"...)` |
Use with VALUES to return multiple values from each referenced property | `CONCAT_ARRAYS(VALUES("Features"), VALUES("Benefits"))` Where Feature 1 is shiny, Feature 2 is bright, Benefit 1 pretty, and Benefit 2 is kind, output is:shinybrightprettykind. |

Conditionals | CONTAINS`IF` `(CONTAINS("<propertyID_to_test>", "<string_to_check>"), "<true_result>", "<false_result>")` |
Tests a value for the string specified in the argument. If the string exists in the value, result is true; if not, false. | `IF(CONTAINS(VALUE("Brand"),"Salsify"),"Cool","Bummer")` If Brand contains Salsify, output is Cool. If not, Bummer. |

Pulling Out Values | DATE_LAST_PUBLISHED_TO`DATE` `_LAST_PUBLISHED_TO(<channel_id>)` |
Returns date of most recent publish through the channel indicated via the publish button in the channel, through ephemeral publish or marked as a publish event via the readiness report. If product has notbeen published, result is empty/null.To find channel ID, navigate to the channel and the ID is in URL path after /channels/.Function is not currently available for in-app computed properties. | `DATE_LAST_PUBLISHED_TO(11234)` Where the most recent product publish date was 5/24/17, output is 2017-05-24. |

Arrays & Advanced Formulas | DIFFERENCE`DIFFERENCE` `(["<array1>"], ["<array2>"])` |
Allows you to subtract one array from another to return only the remaining values. | `DIFFERENCE(["White","Blue","Red"],["Blue","Red"])` Output is White. |

Working with Numbers | DIVIDE`DIVIDE` `("<propertyID1>", "<propertyID2>")` |
Arithmetic function applied to numeric property values to obtain calculated values. | `DIVIDE(VALUE("Cost"),VALUE("Retail")` Where Cost is 10 and Retail is 25, output is .4 |

Arrays & Advanced Formulas | EACH`EACH` `(<"array">,<name for single item in array> => <"action you'd like to take">)` |
Access each value in an array. To use, define the array, name for each item within the array, and the action to take on each. Typically used in combination with other formulas. | `EACH(VALUES("Bullet Points"), feature => CONCATENATE("• ", feature))` Where Bullet Points contains "Bullet 1" and "Bullet 2", output is:• Bullet 1• Bullet 2 |

Conditionals | EQUAL`EQUAL` `("<propertyID1>", "<propertyID2 ` |
Allow you to compare two values, and can be used with conditional formulas to evaluate true/false state. Can be used in combination with IF. Returns true if value is equal to the compared value. | `IF(EQUAL(VALUE("Assembly Required?"), "Yes"), "Y", "N")` If Assembly Required is Yes, output is Y. If it is blank or other than Yes, output is N. |

Pulling Out Values | FIND`FIND` `("<propertyID>", "<search_string>")` |
Finds a string of characters inside a property value. Combine with other formulas to perform actions on found string. | `IF(FIND(VALUE("Category"),"Soft Line"),"Apparel","Hard Goods")` If Category contains Soft Line, output is Apparel, otherwise output is Hard Goods. |

Working with Numbers | GE`GE` `("<propertyID1>", "<propertyID2 ` |
Greater than or Equal to. Allow you to compare two numbers/numeric values, and can be used with conditional formulas to evaluate true/false state. Can be used in combination with IF. Returns true if value is greater than or equal to the compared value. | `IF(GE(VALUE("Width","12"),"Large","Small")` If Width is 12 or greater, output is Large. If less than 12, output is Small. |

Working with Numbers | GT`GT` `("<propertyID1>", "<propertyID2 ` |
Greater than. Allow you to compare two numbers/numeric values, and can be used with conditional formulas to evaluate true/false state. Can be used in combination with IF. Returns true if value is greater than the compared value. | `IF(GT(VALUE"Width","6"),"Large","Small")` If the value of Width is greater than 6, output is Large. If 6 or less, output is Small. |

Conditionals | IF`IF` `("<test>", "<output_if_true>", "<optional_false_output>")` |
Outputs either one value or another, depending on whether test is true or false. true and false outputs can be either literal values or Salsify formulas. This is similar to the IF formula in Excel.Tests either check if a product has a value for a particular property or check whether the value for a product's property is equal to a specific value. | `IF(VALUE("Brand"),"Acme")` If Brand contains a value, output "Acme", if not, output blank. |

Arrays & Advanced Formulas | IN`IN` `("<searched_text>", "<propertyID ` |
Returns T/F for whether searched text exists in the properties/array searched. | `IN("White","Red", "White", "Blue", "Green", "Yellow")` Output is true. |

Working with Numbers | ISNUMBER`ISNUMBER` `("<propertyID>")` |
Outputs true if value is a number, otherwise false. Typically used in combination with conditionals like IF. | `ISNUMBER("Hello")` Output is "False". |

Working with Numbers | IS_VALID_GTIN`IS_VALID_GTIN(<"property ` |
Checks for a valid UPC (correct number of digits and valid check digit) or GTIN and returns a true/false value. | `IS_VALID_GTIN("Each GTIN","gtin12")` Where Each GTIN is valid, output is true. If Each GTIN is invalid, output is false. |

Arrays & Advanced Formulas | JOIN`JOIN` `("<propertyID>", "<delimiter>")` |
Outputs all values for a property with a specified delimiter between the values. Use with VALUES rather than VALUE. | `JOIN(VALUES("Bullets"), "\n")` Where Bullets values are red and blue, output is:redblue |

DIgital Asset URLs & Metadata | JOIN_ASSET_VALUES`JOIN` `_ASSET_VALUES("<property name>", "<metadata property>", "<delimiter>")` |
Joins metadata together for all of the digital assets associated with a property. | `JOIN_ASSET_VALUES("Lifestyle Images", "salsify:Filename", ", ")` Where Filename values are DSC01.jpg and DSC02.jpg, output is DSC01.jpg,DSC02.jpg. |

Category Hierarchy Values | JOIN_PATH_NAMES`JOIN` `_PATH_NAMES("<property name>", "<optional_delimiter>", "<optional_numeric_index>")` |
Joins all levels of the hierarchy's names for the property together using delimeter specified, and for the property value specified by numeric index. Default delimiter is / and default index is 1. If your hierarchy properties include different value IDs and names, you can use the PATH_ID function to access IDs. | `JOIN_PATH_NAMES("Category", " | ", 2)` Where Category values areBags > Laptop Bags > Laptop BackpacksLuggage > Wheeled Luggage > Wheeled Carry-on LuggageOutput is Luggage | Wheeled Luggage | Wheeled Carry-on Luggage |

Combining & Transforming Values | JOIN_RELATIONS`JOIN` `_RELATIONS("<relation_label>", "<delimter>")` |
Join all product identifiers of targets for a given relation label. | `JOIN_RELATIONS("Parent/Child", " ")` Returns all products listed under Parent/Child, separated by (eg. 11111 343434 454545). |

Pulling Out Values | JOIN_VALUES`JOIN` `_VALUES("<property name>", "<delimiter>")` |
For properties with multiple values, combines all of the values together using the given delimiter. | `JOIN_VALUES("Feature Bullets", ", ")` Where Feature Bullets contains Big, Shiny and Economical, output is Big, Shiny, Economical. |

Working with Numbers | LE`LE` `("<propertyID1>", "<propertyID2 ` |
Less than or equal to. Allows you to compare two numbers/numeric values, and can be used with conditional formulas to evaluate true/false state. Can be used in combination with IF. Returns true if value is less than or equal to the compared value. | `LE(VALUE("Width"),"12")` If Width value is 12 or less, output is True, otherwise output is False. |

Conditionals | LENGTH`LENGTH` `("<propertyID>")` |
Returns the number of characters for a specified argument. | `LENGTH(VALUE("Brand"))` Where Brand is Salsify, output is 7. |

Arrays & Advanced Formulas | LET...IN`let` ` "<variable_name>"="<value or formula>" in` |
Use to define variables to be used in subsequent formulas. Variables can contain fixed values, or can perfom functions. Click here for more information on variables. | `let x="™" in` Where Brand Name is Wildflower, output is Wildflower™ |

Combining & Transforming Values | LOOKUP`LOOKUP` `("<propertyID>", "<url or asset ID>", "<column header for value to return>", "<optional_default_value>")` |
Looks up the value for a specified property, references a table stored in an FTP location or a Salsify asset IDand returns the corresponding value from the table. Table contains all the allowed values for the property in column A. | `LOOKUP(VALUE("Category"),"https://salsify.exavault.com/p/Enablement/Wildflower-categories.xlsx","SuperWidgets Category","General")` Where Category contains Food and in the xlsx file, the SuperWidgets column contains Grocery, output is Grocery. |

Combining & Transforming Values | LOOKUP_FIRST`LOOKUP` `_FIRST("<propertyID>", "<url or asset ID>", "<2nd_column_header>", "<default_value>")` |
Differs from Lookup in that if there are multiple lookup keys found it will return the match for the first value found in the lookup table rather than error. | `LOOKUP_FIRST(VALUE("Category"),"http://salsify.exavault.com/p/Enablement/Wildflower-categories.xlsx","SuperWidgets Category","General")` Where Category contains Food on two rows, output is Grocery where the corresponding value in the lookup table at the URL indicated contains Grocery in the Superwidgets Category column the first time Food is encountered. |

Combining & Transforming Values | LOWER`LOWER("<propertyID>")` |
Converts the given value into all lowercase. | `LOWER(VALUE("Brand")` Where value for Brand is ACME, output is acme. |

Combining & Transforming Values | LPAD`LPAD` `("<propertyID>", "<character_to_pad_with>", <N>)` |
Outputs a value that is N characters long by adding at the front. | `LPAD(VALUE("UPC"), "0", "14")` Where UPC is 987654321, output is 00000987654321. |

Working with Numbers | LT`LT` `("<propertyID1>", "<propertyID2 ` |
Less than. Allows you to compare two numbers/numeric values, and can be used with conditional formulas to evaluate true/false state. Can be used in combination with IF. Returns true if value is less than the compared value. | `LT(VALUE("Width"),"6")` Where Width contains 6, output is False. |

Combining & Transforming Values | LTRIM`LEFT("<propertyID>", <N>) or LTRIM("<propertyID>", <N>)` |
Retains number of characters specified, starting from the left. Trims any additional characters at the right end of the string. | `LTRIM(VALUE("UPC"), 11)` Where UPC is 9876543210000, output is 98765432100 |

Working with Numbers | MAX`MAX` `("<propertyID1>", "<propertyID2>"...)` |
Returns the largest numeric values for selected properties. | `MAX(VALUE("Length"),VALUE("Width),VALUE("Height"))` Where Length is 10, Width is 5 and Height is 2, output is 10. |

Working with Numbers | MEDIAN`MEDIAN("<propertyID1>", "<propertyID2>"...)` |
Returns the numeric value closest to the average for selected properties. | `MEDIAN(VALUE("Length"),VALUE("Width"),VALUE("Height"))` Where Length is 10, Width is 5 and Height is 2, output is 5. |

Combining & Transforming Values | MID`MID("<propertyID>", <N_character>, <string_length>)` |
Similar to MID in Excel. Outputs a substring of a value starting at the Nth character and continuing for length of characters specified. | `PROPER(MID(VALUE("Feature Bullet"),9,23))` Where the value for Brand is Features padded shoulder straps and zippered pouch, output is Padded Shoulder Straps. |

Working with Numbers | MIN`MIN` `("<propertyID1>", "<propertyID2>"...)` |
Returns the smallest numeric values for selected properties. | `MIN(VALUE("Length"),VALUE("Width),VALUE("Height"))` Where Length is 10, Width is 5 and Height is 2, output is 2. |

Working with Numbers | MOD`MOD("<propertyID ` |
Returns the remainder when the value is divided by the divisor. | `MOD("158","10")` Where value is 158, and divisor is 10, output is 8.0. |

Pulling Out Values | MODIFIED_SINCE_LAST_PUBLISHED_TO`MODIFIED` `_SINCE_LAST_PUBLISHED_TO(<channel_id>)` |
Returns true/false for whether any property value for the product has been modified since the most recent publish through the channel indicated. To find channel ID, navigate to the channel and the ID is in URL path after /channels/.Function is not currently available for in-app computed properties. | `MODIFIED_SINCE_LAST_PUBLISHED_TO(11234)` Where the product has been updated since most recently publishedthrough 11234, output is true. |

Working with Numbers | MROUNDUP`MROUNDUP("<propertyID>", <multiple_N>)` |
Returns the value rounded up by a given multiple. | `MROUNDUP(VALUE("Price"),5)` Where Price is 11.99, output is 15.0. |

Working with Numbers | MULTIPLY`MULTIPLY("<propertyID1>", "<propertyID2>"...)` |
Arithmetic function applied to numeric property values to obtain calculated values. | `MULTIPLY(VALUE("Retail Price"),VALUE("Margin")` Where Retail Price is 19.99 and Margin is .8, output is 15.991999999999999. |

Conditionals | NOT`NOT("<propertyID1, N1, string1 ` |
Tests the relation between two numeric or string property values, formulas, or specific numbers or strings and returns a true or false. Typically used in combination with other conditional or numeric formulas. | `NOT(EQUAL(12,6))` Because 12 and 6 are not equal, output is True. |

Conditionals | OR`OR` `("<propertyID1, N1, string1 ` |
Allows testing two values in relation to each other in a formula. If either statment is true, the output is true. | `OR(EQUAL(12,6),EQUAL(6,6))` Because 6 is equal to 6, output is True. |

Pulling Out Values | PARENT_ID`PARENT_ID()` |
If product has a parent, outputs the Parent product's ID. | `PARENT_ID()` If the product's Parent ID is 1111, returns 1111. |

Category Hierarchy Values | PATH_ID`PATH_ID("<propertyID>",numeric_depth, ` |
Outputs the Nth hierarchical level of the referenced property's ID, from top to bottom. Optional index specifies from which property value to return. If index is not included, default behavior returns from the first value. | `PATH_ID("Category",3,2)` Where Category contains the value IDsHousewares > All Luggage > Soft-side LuggageWheeled Luggage > Checked Luggage > Hard-side Luggageoutput is Hard-side Luggage. |

Category Hierarchy Values | PATH_NAME`PATH_NAME("<propertyID>",numeric_depth, ` |
Outputs the Nth hierarchical level of the referenced property's name, from top to bottom. Optional index specifies from which property value to return. If index is not included, default behavior returns name from the first value. | `PATH_NAME("Category",2,1)` Where Category contains the value namesHousewares > All Luggage > Soft-side LuggageWheeled Luggage > Checked Luggage > Hard-side Luggageoutput is Wheeled Luggage. |

Combining & Transforming Values | PROPER`PROPER("<propertyID>")` |
Capitalizes the first letter of each word in the value. | `PROPER(VALUE("Brand")` Where value for Brand is ACME INC., output is Acme Inc. |

Arrays & Advanced Formulas | PROPERTY_IDS_WITH_VALUE_FROM_GROUP`property` `_ids_with_value_from_group("<property_group_id>")` |
By default, returns all the labels of a Property Group as separate values. Combine with formulas like EACH, CONCAT and variables to return sets of labels and values. |
Where the property group ID is Rich Content, property IDs are Material and Fabric Care, property values are 100% Cotton, Cold water machine washable, output is:Material: 100% CottonFabric Care: Cold Water Machine Washable |

Pulling Out Values | PUBLISHED_TO`PUBLISHED_TO(<channel_id>)` |
Returns true/false for whether the product has been published through the channel indicated via the publish button in the channel, through ephemeral publish or marked as a publish event via the readiness report. To find channel ID, navigate to the channel and the ID is in URL path after /channels/.Function is not currently available for in-app computed properties. | `PUBLISHED_TO(11234)` Where the product has been published through 11234, output is true. |

Pulling Out Values | RELATION`RELATION("<relation_label>", <` |
Returns product ID of target for a given relation label. | `RELATION("Other Colors",1)` Output is the product ID for the first related product listed in Other Colors, |

Combining & Transforming Values | REPLACE`REPLACE` `("<propertyID>", "<find_text>", "<replace_text>")` |
For our users familiar with regular expressions, the REPLACE function is just like SUBSTITUTE except it uses a regular expression. | `REPLACE(VALUE("Product Name"), "\n"," ")` Where Product Name value contains a line break, replace with a space. |

Working with Numbers | ROUND`ROUND` `(VALUE("<propertyID>"), <N_decimal_places>)` |
Rounds numeric values to the specified number of decimal places. | `ROUND(VALUE("Cost"), 2)` Where Cost is 19.992222, output is 19.99 |

Combining & Transforming Values | RPAD`RPAD` `("<propertyID>", <N_padding>, <N_length>)` |
Outputs a value that is N characters long by adding at the end. | `RPAD(VALUE("UPC"), "0", "14")` Where UPC is 987654321, output is 98765432100000. |

Combining & Transforming Values | RTRIM`RIGHT("<propertyID>", <N>) or RTRIM("<propertyID>", <N>)` |
Outputs the last N characters of value. | `RTRIM(VALUE("UPC"), 11)` Where UPC is 9876543210000, output would be 76543210000 |

Combining & Transforming Values | SENTENCE`SENTENCE("<propertyID>")` |
Capitalizes the first letter of the first word of a given value. | `SENTENCE(VALUE("Warranty")` Where value for Warranty is ten year limited warranty, output is Ten year limited warranty. |

Arrays & Advanced Formulas | SLICE`SLICE` `("<propertyID>", "<N_start_position>", "<N_values_to_return>")` |
SLICE will allow you to take a section/subset of an array. The second argument is the first value to return and the third (optional) is the number of values to return in the array. (Note that SLICE takes an array and gives you a smaller (or equal size) array in return.) | `SLICE(VALUES("Features"), 2, 2)` Where Features has 4 values: 10% recycled materials, Limited Lifetime Warranty, Made in the USA, Assembled in Mexico, output is Limited Lifetime Warranty Made in the USA. |

Combining & Transforming Values | SPLIT`SPLIT` `("<propertyID1>", "<delimiting_character>")` |
Separates a single value into multiple values, using a specified charcter as the separator/delimiter. | `SPLIT(VALUE("Bullets"), ",")` Where Bullets is 1 Year Warranty,Made in USA,80% post-consumer recycled materials, output is:1 Year WarrantyMade in USA80% post-consumer recycled materials |

Combining & Transforming Values | SQUISH`SQUISH` `("<propertyID>")` |
Outputs value with all leading and trailing whitespace removed, and any extra whitespace inside the value condensed to a single space. | `SQUISH(VALUE("Description"))` Where value for Description is " This is the description. ", output is: "This is the description." |

Combining & Transforming Values | STRIP_HTML`STRIP` `_HTML("<propertyID>")` |
Removes all HTML markup and leaves just the regular text. | `STRIP_HTML(VALUE("Description"))` Where Description is <b>My New Product</b><p>This product fixes all ills!...</p>", output is My New Product This product fixes all ills!... |

Combining & Transforming Values | SUBSTITUTE`SUBSTITUTE` `("<propertyID>", "<find_text>", "<replace_text>")` |
Searches for a string in a value and replaces it with what's defined. Can do multiple subsitutions with the same formula by adding comma-separated pairs of find, replace strings. | `SUBSTITUTE(VALUE("Product Description"), "•", "•") SUBSTITUTE(VALUE("Product Name"), "®", "", "™", "")` Where Product Description is • Durable and Lightweight, output is <ul><li>Durable and Lightweight</li></ul> Where Product Description is • Durable and Lightweight with Velcro™ closure , output is <ul><li>Durable and Lightweight with Velcro closure</li></ul> |

Combining & Transforming Values | SUBSTRING`SUBSTRING` `("<propertyID>", <N_character>, <string_length>)` |
Similar to MID in Excel. Outputs a substring of a value starting at the Nth character and continuing for length of characters specified. | `PROPER(SUBSTRING(VALUE("Feature Bullet"),9,23))` Where the value for Brand is Features padded shoulder straps and zippered pouch, output is Padded Shoulder Straps. |

Working with Numbers | SUBTRACT`SUBTRACT` `("<propertyID>", "<propertyID ` |
Arithmetic function applied to numeric property values to obtain calculated values. | `SUBTRACT(VALUE("MSRP"),"2")` Where MSRP is 19.99, output is 17.99 |

Combining & Transforming Values | TEXT`TEXT` `("<propertyID>", "<format>")` |
Reformats numeric values to values stored as text, with specified format. Include decimal even when returning only whole numbers (ie. to return two digit places like 01, format should be 00.) | `TEXT(VALUE("MSRP"), "0.00")` Where value for MSRP is 19.9999 output is 19.99. |

Pulling Out Values | TODAY`TODAY` `()` |
Returns today's date. Used in combination with date information to evaluate IF statements. Useful for date-based product status. | `IF(GT(VALUE("Active Date"),TODAY()),"Pending","Active")` Where Active Date is today or in the future, output is Pending. Where active date is in the past, output is Active. |

DIgital Asset URLs & Metadata | TRANSFORM_ASSET_URL`TRANSFORM` `_ASSET_URL("<propertyID>", "<transformation_string>","<optional_index>")` |
Inserts transformation string in a Salsify URL to change the size or other characteristics of an image. See Transforming Image Files for available transformations. | `TRANSFORM_ASSET_URL("Main Image", "c_fit,w_2000,h_2000",2)` Where Main Image is http://salsify.com/image.jpg, output is http://salsify.com/image/c_fit,w_2000,h_2000.jpg, transforming the image to fit within 2000x2000, maintaining original proportions. |

Combining & Transforming Values | UPPER`UPPER` `("<propertyID>")` |
Converts the given value into all uppercase. | `UPPER(VALUE("Brand")` Where value for Brand is Acme, output is ACME |

Pulling Out Values | VALUE`VALUE` `("<propertyID>", <optional_N>)` |
Outputs the value of the given property. If the property has multiple values, it returns only one. Specify a number for N to return the value from that position. | `VALUE("Brand")` Where value for Brand is Acme, output is Acme. |

Arrays & Advanced Formulas | VALUES`VALUES` `("<propertyID>","<optional_N>")` |
For use with array formulas where multiple values can be returned for single properties. | `VALUES("Features", 1)` Where the first Features value in array is White, output is White. |

## Formula Guidelines & Best Practices

There are a few basics to keep in mind when using Salsify formulas:

#### Formulas use Property IDs, not Property Names

In most cases, the Property ID and Property Name will be the same, but in some situations they’re different. Use the Product ID when building formulas. Not sure if the name is the same as the ID? Check by going to the More menu, select Properties, and do a search for your property.

#### Formula property references are case sensitive

If the property ID is “Brand”, “BRAND” and “brand” will not work.

#### Formula functions are not case sensitive

`VALUE`

will work the same as `value`

- best practice is to use all caps to make the formula easier to read.

#### Use straight quotation marks

In some cases, if you’re using word processing software like Word to write your formulas, it inserts curly quotation marks rather than straight. If you have a formula that is unexpectedly not working, check the quotation marks to be sure they’re the right type.

#### Use single or double quotation marks

In normal formulas, double and single quotes can be used interchangeably.

#### Quotes around numeric values

In most cases, you do not have to use quotes around numbers when they are a formula function like a position or whole number. But you can use quotes in these cases as well. Use quotation marks around numbers when they are being used as a value or have a decimal point in them. When in doubt, check the table above for syntax.

#### The SALSIFY_ prefix

When using formulas in Excel formatted templates, use `SALSIFY_`

in front of every Salsify formula. So for example, when using `VALUE`

in an Excel template, enter it as `SALSIFY_VALUE`

.

#### NULL vs ""

Use `NULL`

as the last argument for the case where you want to insert “no value”. Use `""`

where you want to insert a blank value. The readiness report interprets `NULL`

as “fill in nothing”, and `""`

as “fill in a blank value”. So `NULL`

doesn’t get counted as a value in the completed percentage, and `""`

gets counted as a value.

#### Special characters

There are some situations where special characters like \ or a double quotation mark need to be “escaped” in order for the formula to interpret them correctly.

## Formula Editing & Troubleshooting

#### Parentheses and quotation marks always come in pairs

When writing and troubleshooting formulas, check where your quotation marks and parentheses are, and whether they are all in pairs. If your quotation marks and parentheses are either misplaced or don’t close properly, your formula will not validate.

#### Look for the red underline in the readiness report formula builder

The formula builder will give you hints about where your formula isn’t validating. If there’s a red underline under a specific part of your formula like `VALUE`

, check the remainder of that section to be sure that the property ID is correct and that your syntax is right.

#### Use formula builder to find the right property ID

An easy trick to finding the right version of your property ID is to start typing it in the bar at the top of the formula builder. Don’t select it, but the formula builder has a list of all the valid property IDs.

**Formula builder can help with templated export troubleshooting**

If you’re working on a templated export and you can’t figure out why your formula won’t validate, try opening up a readiness report in another window, and use one of the source fields. Paste your formula in the formula builder, remove `SALSIFY_`

and see where the error is. It can be a really quick way to see a syntax error or mistake in property ID. It’s often helpful to use the formula builder like a scratch pad, validating your formulas before you add them to your template. Just remember to add `SALSIFY_ `

to each of your formula pieces when you add it to the Excel template.

#### Use line breaks in formula builder to make formulas easier to read

Splitting up a formula makes it a lot easier to both read to figure out what it does, and troubleshoot to make sure you have your parentheses and quotation marks where they belong. The formula builder ignores line breaks in formulas, so split them up however you’d like. Here’s an example:

**Single line version: **

`IF(AND(VALUE("Brand"),VALUE("Category")),CONCATENATE(VALUE("Brand"," - ",VALUE("Category")),NULL)`

**Multi-line version:**

IF( AND( VALUE("Brand"), VALUE("Category") ), CONCATENATE( VALUE("Brand"), " - ", VALUE("Category") ), NULL )

The multi-line version makes it much easier to see where everything should be, and that you have all your syntax correct.

**Comment your formulas to remember what they do**

You can add comments to your formulas to help yourself remember what they do, and to inform others who might be viewing or editing your formulas. Start comment lines with a # and anything on the line will be ignored, or put the comment on a line after a formula by adding the # after the formula part ends. For our example above, we could add comments like these:

#you can comment out an entire line by starting with #, or end a line by adding a #comment behind the formula as below: IF( AND( VALUE("Brand"), VALUE("Category") #if the product has both brand and category filled ), CONCATENATE( VALUE("Brand"), " - ", VALUE("Category") #output will be Brand - Category ), NULL #otherwise no output at all )

Everything after the # on each line will be ignored by the formula builder.