Sometimes, you want to filter transactions based on dynamic rules.
Do You Need A Filtering Formula?
In most cases, it's sufficient to rely on saved queries to filter transactions. For example, you could define a transaction filter such as this:
And save it as a query called "My Filter", which can then be applied to your plan (on the "Limits" tab):
However, there are situations where you may need to filter transactions in a more flexible way. For example:
- Transactions should be filtered out when one field is equal to another
- Transactions should be filtered out based on a product catalog lookup
- Transactions should be filtered out if commissions have already been paid before
- Etc.
For those cases, you can use a dynamic transaction filtering formula.
Creating a Dynamic Filtering Formula
First, review this article about formulas to understand how they work, and which constructs are available.
Next, create a dynamic transaction filtering formula:
- Edit your plan
- Click on the "Limits" tab (if needed)
- Scroll down to advanced options
- Enter a dynamic transaction filtering formula (examples below)
Your formula's output should be 0 (or less) if the transaction should be filtered out, and strictly more than 0 if the transaction should be processed. For simplicity, you can return a value of 0 to exclude and 1 to include it.
Dynamic Filtering Formula Examples
Assume that you uploaded transactions with the following fields:
- City (a text field)
- WonDate (a date field)
Here are some examples of formulas you could use to configure dynamic transaction filtering.
Exclude Transactions Whose City Starts With 'S' (ex: Seattle)
var $city = [Transaction].[City (Imported)]
--------------------------------------------------------------------
IIF($city LIKE 'S*', 0, 1)
Exclude Transactions Whose WonDate Is Within 1 Month Of The Calculation's Start Date
var $startDate = [Calculation].[start_date_utc]
--------------------------------------------------------------------
var $wonDate = [Transaction].[WonDate (Imported)]
--------------------------------------------------------------------
var $months = DateDiff($startDate, $wonDate, Months)
--------------------------------------------------------------------
IIF($months <= 1, 0, 1)
Exclude Transactions For Which A Commission Has Already Been Paid
var $rep = [Transaction].[Rep (Imported)]
--------------------------------------------------------------------
var $userId = Lookup($rep, 'Id')
--------------------------------------------------------------------
IFEX(LEN($userId) <= 0, 1)
--------------------------------------------------------------------
var $paid = GetCommission($userId, 0, 0)
--------------------------------------------------------------------
IIF($paid <> 0, 0, 1)