Filter formulas can be used to tell bulk actions or feed exports what to export and what to ignore. Formulas in Zentail are very similar to Excel formulas. You will see an error in red below the formula field if the entered operation is not valid.

To enter a formula:

  • You must be downloading a report

Use the command "buttons" below the formula field to write the formula

See below for an explanation and examples of all of the functions Zentail currently provides.

Note: If you get "Invalid argument provided to equation, expected bool got field", try eliminating the brackets around the boolean (true, false).

Insider Tip: if you're more comfortable with Quick Edit filters, you can create a filter there, click the Advanced Filters dropdown, then copy and paste the filter here.

Comparisons

These operations are used to check the value of a certain field.

STARTSWITH

STARTSWITH(field, string, [caseInsensitive]) - passes if the given field starts with the given string. The third option can be true or false, but it is optional.

Examples: 

   STARTSWITH( sku, "TEST-", false )
Matches any product with a sku that starts with "TEST-", matches "TEST-123" but not "test-123" or "somethingelse--"

   STARTSWITH(title, "test title", true)
Matches any product with a title that starts with "test title". This time, it's case insensitive so it will match "test title 1" and "tesT TitLe 2".

EQUALS

EQUALS(field, string [, caseInsensitive]) - passes if the given field equals the given string. The third option can be true or false.

Examples: 

   EQUALS(sku, "TEST-123")
Matches the product with sku "TEST-123"

   EQUALS(title, "test title", true)
Matches any product with a title that equals "test title". This time, it's case insensitive so it will match "test title" and "tesT TitLe" but not "test title 123".

Greater Than/Less Than

GREATERTHAN( field, "string" [, orEqualTo] ) - passes if the given field is greater than the given string. The third option can be true or false.

LESSTHAN( field, "string" [, orEqualTo] ) - passes if the given field is less than the given string. The third option can be true or false.

Examples:

GREATERTHAN( Item Price, "20", true )
Matches products with a price greater than or equal to $20

LESSTHAN( Quantity, "25", false )
Matches products with a quantity less than 25

Compound

These operations are used to combine other operations.

NOT

NOT(fn) - Will pass if its sub-condition fails.

Examples:

 NOT(EQUALS(sku, "test-123"))
Matches any product that does not have the SKU "test-123".

OR

OR(fn [, fn [, fn]...]) - Will pass if ANY of its sub-conditions pass.

Examples:

   OR(EQUALS(sku, "test-1"), EQUALS(sku, "test-2"), EQUALS(sku, "test-3"))
Matches the 3 SKUs - test-1, test-2 and test-3

   OR(EQUALS(sku, "test-1"), STARTSWITH(title, "test title", true))
Matches the SKU test-1 or any product with a title that starts with "test title" (case insensitive).

You can also use words like AND or NOT as sub-conditions:

   OR(NOT(STARTSWITH(title, "test title")), EQUALS(sku, "test-title-sku"))
Matches any product that does not have a title starting with "test title", also will match the product with SKU test-title-sku regardless of what its title value is.

AND

AND(fn [, fn [, fn]...]) - Will pass only if ALL of its sub-conditions pass.

Examples:

AND(EQUALS(color, "red"), STARTSWITH(title, "model-1-"))
Matches any product that has color red and a title which starts with "model-1-"

AND and NOT  can also be used in compound ways like OR.

Valid Fields

Each account has its own set of valid fields based on the SMART Types being used and which integrations are on the account.  To see which fields are available to you, you can check Zentail's data dictionary.

Did this answer your question?