All Collections
Import / Export
How to Write a Filter Formula for Exports
How to Write a Filter Formula for Exports

Review how to write a formula to allow you to filter out or exclude certain products from your reports

Michael Goldmeier avatar
Written by Michael Goldmeier
Updated over a week ago

Filter formulas can be used to tell 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 to filter a report:

  • Go to Export > Download Report. You must be downloading a report.

  • Under Select Report Type, click on the the report template you wish to run with the filter.

  • Click on Advanced Filter.

  • Use the insert symbol "buttons" below the formula field to write the formula.

Read below for an explanation and example of each 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.


Attribute Name

Whenever you are writing a formula and need to fill in the correct "field", you can simply click on this button to open a pop-up box where you can search for the correct attribute name. Selecting an attribute will paste the name directly into your formula, eliminating the possibility of any spelling errors.


Comparisons

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

Starts With

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(supplier_product_id, "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".

Contains

CONTAINS(field, "string", [caseInsensitive]) - passes if the given field contains the specified string. The third option can be true or false and is optional.

Example:

CONTAINS(supplier_product_id, "-Shopify", false)

Matches any product that has a SKU that contains "-Shopify" but not "-shopify".

Equals

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

Examples: 

EQUALS(supplier_product_id, "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 Operators

These operations are used to combine other operations.

Not

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

Example:

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

All Of (AND)

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

Example:

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

Any Of (OR)

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

Examples:

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

OR(EQUALS(supplier_product_id, "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(supplier_product_id, "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 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?