Filters are a critical tool in Onit app building, which enable you to filter for a subset of transactions or users that meet specific criteria. Filters can be assigned to certain Actions or Business Rules to limit their scope. For example, you can limit which users receive a notification or which transactions will be modified.
Note that Onit distinguishes between Raw Filters used to filter for users and transactions so we'll be referring to Raw Transaction Filters and Raw User Filters separately in this tutorial.
Before We Start ...
This tutorial will assume you understand the following concepts:
Filter Overview
In Onit, Raw Transaction Filters and Raw User Filters are built using JSON, an open-standard format that uses human-readable text to store and transmit data.
For example, the JSON below tells Onit to filter Records against specified criteria:
[{"operator":"like","value":"1000","property":"p_item_price","type":"combo"},
{"operator":"like","value":"Pending","property":"status","type":"string"}]This text may look like gibberish to you, and that’s OK. While you don’t need to be an expert in Onit’s JSON filtering syntax to create Filters, you will need to be generally aware of what JSON is and where to use it. We’ll explain the critical concepts.
When working with Raw Transaction Filters, you can use a handy trick that will, in many cases, prevent you from having to write any JSON yourself; Instead, you’ll simply copy/paste the JSON from somewhere else. However, there are a few use cases, (when working with Raw User Filters or when performing certain “advanced” Record filtering operations), where you will need to write some basic JSON.
Once your JSON is ready, you’ll use it to create a new Filter in your App’s Advanced Designer. You can then assign this Filter to an Action or Business Rule. By doing so, you’re asking the Action/Business Rule to only run against Records/users that satisfy your Filter’s criteria.
Let’s get started, first by explaining how to build your JSON.
Don't Write JSON. Copy it!
In most cases, you don't need to write any JSON yourself when building a Raw Transaction Filter. Instead, you can use a quick trick to have JSON built for you so all you have to do is copy and paste it into the new Filter that you’re creating.
This trick revolves around an app's Dashboard.
You've probably seen your app's Dashboard; It's a grid where you can access all your app's transactions. You may have also noticed that each column in the grid is a Field or variable that lives within your app.
You can apply "filters" to each column on the Dashboard, (similar to how you’d filter a column in Excel), so only transactions that meet some criteria display.
Note that the filtering logic you apply to a Dashboard only affects the Dashboard -- it doesn’t affect any Actions or Business Rules. However, the filtering that you are doing in a Dashboard automatically creates JSON behind the scenes, which we can use to create a new Raw Transaction Filter in the Advanced Designer.
Let's walk through an example to learn how to filter a Dashboard's column. In this example, we'll say we need to filter for all of the transactions currently in a Phase named Financial Approval.
1. Filter a Column
Ensure that the Phase column is visible open the column overlay by clicking the 3 column icon in the top right.
Ensure that the Phase column is visible.
Next, click the Phase column and type in the filtering value. For our example, we’ll type in Financial Approval.
2. Open the Filter Info Dialog
Great! We've filtered which transactions are displaying on our Dashboard. As we mentioned above, behind the scenes Onit has generated JSON that expresses this filtering logic, which we can easily access.
From the column's dropdown, select Filter Info.
3. Copy the JSON
The pop-up that appears contains the JSON that you need to express this filtering logic in a Raw Transaction Filter for an Action/Business Rule. Copy this JSON to your clipboard and skip to our Using the Filter section below to see how to make use of it in a Raw Transaction Filter.
Tip: In the example above, we filtered the Dashboard by a single column. If necessary, we could have filtered multiple columns on the same Dashboard at the same time. If you do so, the only Records that will appear will be those that satisfy all filtering criteria. In addition, the JSON that appears in the Filter Info dialog (regardless of which column it is opened from) will also include all filtering criteria.
Advanced Tip: You can optionally use Regular Expressions to filter your dashboard's columns. To do so, ensure that the first character in the filtering value is # which tells Onit to treat all text after the # as a Regular Expression. If you’ve never used Regular Expressions before, check out 8 Regular Expressions You Should Know and Regex Quick Start.
Can I always use the copy/paste JSON trick?
Unfortunately, no. The trick described above works when creating most Raw Transaction Filters. But when you need JSON for a Raw User Filter, or when building more “advanced” Raw Transaction Filters, you’ll need to write the JSON yourself.
Along those lines, the list below covers the most commonly encountered situations where you’ll need to write JSON. For each situation below, we’ve included sample JSON that you can use as a starting point.
Situation #1: Raw User Filters
This situation only applies to Raw User Filters.
Define which users should be included by filtering for their email address:
[{"type":"string","value":"#([email protected]|[email protected])","field":"email"}]Note: The example above filters based on two different sets of user criteria. Users that meet either criterion will be included.
Situation #2: Checkbox and Combo Field Types
This situation only applies to Raw Transaction Filters.
If you build your JSON using the Dashboard trick explained above, you’ll need to make some adjustments if the filter includes Checkbox, Combo or Listcombo Fields.
For Checkbox Fields, substitute the word “boolean” for “checkbox”:
[{"operator":"==", "value":true,"property":"p_third_party","type":"boolean"}]For Combo and Listcombo Fields, substitute the word “string” for “combo”:
[{"operator":"like","value":"sales","property":"p_department","type":"string"}]Situation #3: Catch-All Filters
This situation applies to both Raw Transaction Filters and Raw User Filters.
Though it may sound odd, at some point you’ll likely need to create an empty filter. That is, a Filter that includes all possible transactions or users and filters nothing out.
To write such a filter, use the following JSON:
[]Note: The text above represents the entire JSON that should be used -- two characters.
Situation #4: Date and DateTime Filters
This situation only applies to Raw Transaction Filters.
If you build your JSON using the Dashboard trick explained above, Date and DateTime Fields in the generated JSON need to be edited slightly before the JSON can be used in a Filter; You’ll need to make use of a comparison operator, as well as reformat the date using Liquid.
In the following example, Records are being filtered based on whether their expiration_date is less than six days from now:
[{"type":"date","comparison":"lt","value":"{{now|subtract_days:6|date:"%m/%d/%Y"}}","field":"p_expiration_date"}]Note that in the example above the highlighted text is Liquid inserted into the JSON. When the Action/Business Rule runs, it will execute this Liquid, which will output a date.
Tip: You can insert Liquid into any JSON Filter, not just the one above.
Also note the comparison operator being used in the example above in bold. Refering to the list of possible abbreviations below, you'll see the comparison operator being used in the example (i.e., lt) means "Less Than". Plug in any of the following abbreviations to Date and DateTime filters:
- gt: Greater Than
- lt: Less Than
- eq: Equal To
- gte: Greater Than or equal to
- lte: Less than or equal to
These filters can also be used on numeric fields – decimal, integer, currency, and multicurrency.
Using Your Filter
Now that we know how to create the JSON, let’s cover where to insert it. This part is easy!
1. Browse to Filters
Browse to an App’s Advanced Designer page. In the left-hand pane, click the Filters node.
From the Add dropdown, select either Raw Transaction Filter or Raw User Filter.
2. Configure Your Filter
Provide your Filter with a Name. Since a single Filter may be used multiple times (by different Actions/Business Rules), be descriptive.
Enter your JSON into the Filter JSON property.
Select OK to save your new Filter.
3. Using Your Filter
Congrats! You now have a JSON-defined Filter that is ready to be applied to an Action/Business Rule.
Along those lines, note that only the following Actions accept Filters:
- Create Transaction from Transactions
- Multi Report
- Recalc Transactions
- Run Action on Related Transactions
- Send Report
- Upsert Other Transaction
In addition, there is one (and only one) Business Rule that accepts a Filter: Daily Schedule. See our Scheduling Email-Based Reports tutorial for more info.
Other Filters in Onit
There are other filters besides Raw User and Raw Transaction Filters available to you in Onit.
Transaction Filter: This filter is functionally the same as the Raw Transaction Filter. Instead of writing JSON the filter provides a dropdown list of Fields and Comparisons to build your filter.
- Name – Name of the Transaction filter.
- Description – A description of the filter.
- Field – The field to which the comparison will be based.
- Comparison – The comparison operator (e.g. Less than, Greater than, Equal, Not equal, etc.).
- Value – The value to compare to the current value of the selected field.
User Filter: This filter is functionally the same as the Raw User Filter. Instead of writing JSON the filter provides a dropdown list of Fields and Comparisons to build your filter.
- Name – Name of the User filter.
- Description – A description of the filter.
- Field – The field to which the comparison will be based. Either Email or Status.
- Comparison – The comparison operator (e.g. Less than, Greater than, Equal, Not equal, etc)
- Value – The value to compare to the current value of the selected field.
Reminder Filter: The reminder filter should be used to create reminders for Records. The same functionality can be achieved with JSON in a Raw Transaction filter, however this filter provides dropdown configuration for the same task.
- Name – Name of the custom Reminder filter.
- Description – A description of the reminder filter.
- Days – Enter the number of days or select an Integer field from the App.
- Before/After – Select ‘Before’ to select transactions where today is a specified number of days before the field below. Select ‘After’ to select transactions where today is a set number of days after the field below.
- Field – Select a Date or DateTime field from the App that will be used to compare the above criteria and return the appropriate Records.
A Reminder Filter can only be used in a Daily Schedule Business Rule.
A Reminder Filter doesn't do quite the same thing as a Raw Filter using the "Days Before Today" or "Days After Today" operators. The Reminder Filter looks for an exact number of days: "2 Days Before" October 31st will only match on October 29th. Contrast with a dashboard grid filter for "2 Days After Today": this will match the 29th, 30th, or 31st.
You can also set the Days control to a Text field. If you do this, be careful that every Record in the app has a number in that field: anything that isn't a number (even a blank) will break the filtering. Using an Integer field is much safer. (An Integer field that doesn't have a value won't break the filter.)