Formula Filter Examples

Email or Attachment filename formulas:

Formula value: join(["Report", lookup("printDate")],"_")

The file name will be: Report_6/21/2023

NOTE: the slashes "/" in the date are not valid filename characters for most computers so you need to remove them. See the formula below.

Formula value: join(["Report", replace(lookup("printDate"),"/","-")],"_")

The filename will be: Report_6-21-2023

You can substitute any value for the "/".

Formula value: join(["Report", dateFormat(lookup("printDate"),"YYYYMMDD")],"_")

The filename will be: Report_20230621

TIP: Use the preview button to see how your formulas work to produce values.

"page: " + pageNumber

Concatenates a constant string with a variable. Variable can be a string or a number.

if(0,"Elephant","Giraffe") ===> 'Giraffe'
if(1,"Elephant","Giraffe") ===> 'Elephant'
if(NaN,"Elephant","Giraffe") ===> 'Giraffe'
if("","Elephant","Giraffe") ===> 'Giraffe'
if("True","Elephant","Giraffe") ===> 'Elephant'
if("False","Elephant","Giraffe") ===> 'Elephant'

includes(["a","b","c"],"b") ===> True
includes(["a","b","c"],"z") ===> False
includes(["sublocation-A", "sublocation-B"], lookup("facilityName")) ===> will return results for only those specified sublocations

Truth table for the if function is consistent with JavaScript.

conditional(0,1,"Elephant",2,"Giraffe",0,"Banana") ===> "Banana"
conditional(99,1,"Elephant",2,"Giraffe",0,"Banana","Apple") ===> "Apple"
conditional(99,1,"Elephant",2,"Giraffe",0,"Banana") ===> NaN

The conditional function is useful for selecting from a list of options.

coalesce(number(42), -1) ===> 42
coalesce(number("42"), -1) ===> 42
coalesce(number("banana"), -1) ===> -1

The coalesce function is useful handling unexpected input to formulas.

fiHtmlEntitiesEncode(productId + "-" + fiJoin(fiSlice(fiSplit(shipmentItemLotId," "), 1), " "))

Remove the leading label for the lot identifiers (e.g. "Lot:"), concatenate with the product identifier, and then escape special characters so that it can be safely embedded in a page template.

join(compact([email("Fred","fred@gm.com"), email("",""), email("","joe@ma.com")]), ", ") ===>
         "Fred" <fred@gm.com>, joe@ma.com

The email function makes it easier to write a reliable formula for customizing default recipients for emails.

join(compact(["payables@acme.net",conditional(lookup("orderDestination"),
        "Main", email("Steve", "steve@acme.net"), 
        "Hdqtrs", email("Ana", "ana@acme.net"), 
        "South annex", email("Robert", "robert@acme.net"))]),", ")

Combining conditionals and lookups produces an email formula that can include different address depending on the destination for a purchase order.

lookup("facilityName") < "L"

This is a formula filter for the sublocation labels. The filter allows you to create greater than / less than statements for alphanumerical values. The formula above would generate all sublocations A-K