Formulas Cheat Sheet 

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 ADDADD("<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 ANDIF(AND("<propertyID1>" or "<formula1>", "<propertyID2>" or "<formula2>") 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_VALUEASSET_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 ATAT(["<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_FROMCHANNEL_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_TEXTCLEAN_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 COALESCECOALESCE("<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 COMPACTCOMPACT("<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 COMPOUNDCOMPOUND("<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 CONCATENATECONCATENATE("<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_ARRAYSCONCAT_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 CONTAINSIF(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_TODATE_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 DIFFERENCEDIFFERENCE(["<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 DIVIDEDIVIDE("<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 EACHEACH(<"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 EQUALEQUAL("<propertyID1>", "<propertyID2 or specific_numeric_value>") 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 FINDFIND("<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 GEGE("<propertyID1>", "<propertyID2 or specific_numeric_value>") 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 GTGT("<propertyID1>", "<propertyID2 or specific value>") 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 IFIF("<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 ININ("<searched_text>", "<propertyID or array>") 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 ISNUMBERISNUMBER("<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_GTINIS_VALID_GTIN(<"property or string">, <"gtin14, gtin13, gtin12, gtin8, or upc ">) 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 JOINJOIN("<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_VALUESJOIN_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_NAMESJOIN_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_RELATIONSJOIN_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_VALUESJOIN_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 LELE("<propertyID1>", "<propertyID2 or specific_numeric_value>") 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 LENGTHLENGTH("<propertyID>") Returns the number of characters for a specified argument. LENGTH(VALUE("Brand"))Where Brand is Salsify, output is 7.
Arrays & Advanced Formulas LET...INlet "<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
 let y=VALUE("Brand Name") in

 CONCATENATE(y,x)
Where Brand Name is Wildflower, output is Wildflower™
Combining & Transforming Values LOOKUPLOOKUP("<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_FIRSTLOOKUP_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 LOWERLOWER("<propertyID>") Converts the given value into all lowercase. LOWER(VALUE("Brand")Where value for Brand is ACME, output is acme.
Combining & Transforming Values LPADLPAD("<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 LTLT("<propertyID1>", "<propertyID2 or specific_numeric_value>") 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 LTRIMLEFT("<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 MAXMAX("<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 MEDIANMEDIAN("<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 MIDMID("<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 MINMIN("<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 MODMOD("<propertyID or specific_numeric_value>", "<divisor_N>") 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_TOMODIFIED_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 MROUNDUPMROUNDUP("<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 MULTIPLYMULTIPLY("<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 NOTNOT("<propertyID1, N1, string1 or formula1>", "<propertyID2, N2, string2 or formula2>") 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 OROR("<propertyID1, N1, string1 or formula1>", "<propertyID2, N2, string2 or formula2>") 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_IDPARENT_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_IDPATH_ID("<propertyID>",numeric_depth, optional_numeric_index) 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_NAMEPATH_NAME("<propertyID>",numeric_depth, optional_numeric_index) 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 PROPERPROPER("<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_GROUPproperty_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.
let rich_content = compact( each( property_ids_with_value_from_group("Rich Content"), external_id => if(value(external_id), concat(external_id,': ',value(external_id)), null) )) inPROPER(rich_content)

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_TOPUBLISHED_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 RELATIONRELATION("<relation_label>", <optional_index_N>) 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 REPLACEREPLACE("<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 ROUNDROUND(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 RPADRPAD("<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 RTRIMRIGHT("<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 SENTENCESENTENCE("<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 SLICESLICE("<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 SPLITSPLIT("<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 SQUISHSQUISH("<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_HTMLSTRIP_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 SUBSTITUTESUBSTITUTE("<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 SUBSTRINGSUBSTRING("<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 SUBTRACTSUBTRACT("<propertyID>", "<propertyID or numeric value>"...) 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 TEXTTEXT("<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 TODAYTODAY() 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_URLTRANSFORM_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 UPPERUPPER("<propertyID>") Converts the given value into all uppercase. UPPER(VALUE("Brand")Where value for Brand is Acme, output is ACME
Pulling Out Values VALUEVALUE("<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 VALUESVALUES("<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.