Filters are a critical tool in building an Onit app. They enable you to filter for a subset of transactions or users that meet specific criteria. To limit their scope, filters can be assigned to certain Actions or Business Rules. For example, you can restrict which users receive notifications or which transactions are modified.
Note that Onit distinguishes between Raw Filters used to filter users and transactions. Therefore, in this tutorial, we'll refer to raw transaction and user filters separately.
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 stores and transmits data using human-readable text.
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 performing certain “advanced” Record filtering operations) where you must 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 by explaining how to build your JSON.
Don't Write JSON. Copy it!
In most cases, you don't need to write JSON 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 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 also have noticed that each column in the grid is a Field or variable 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 are displayed.
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 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 the transactions currently in a Phase named Financial Approval.
1. Filter a Column
Ensure that the Phase column is visible. Then, click the three-column icon at the top right to open the column overlay.
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 the transactions displayed on our Dashboard. As mentioned above, Onit generated JSON behind the scenes expressing this filtering logic, which we can easily access.
From the column's dropdown, select Filter Info.
3. Copy the JSON
The pop-up contains the JSON 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 use 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 simultaneously. 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, 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 you'll need to write the JSON yourself when you need JSON for a Raw User Filter or when building more “advanced” Raw Transaction Filters.
Along those lines, the list below covers the most commonly encountered situations where you need to write JSON. We've included a sample JSON for each situation below, which 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 adjust 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, you’ll likely need to create an empty filter at some point. 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, the Date and DateTime Fields in the generated JSON need to be edited slightly before they can be used in a Filter. You’ll need to use a comparison operator and 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 is used in the example above in bold. Referring to the list of possible abbreviations below, the comparison operator 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.
Select either Raw Transaction Filter or Raw User Filter from the Add dropdown.
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 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, one (and only one) Business Rule accepts a Filter: Daily Schedule. See our Scheduling Email-Based Reports tutorial for more info.
Other Filters in Onit
Other filters besides Raw User and Raw Transaction Filters are 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. However, instead of writing JSON, it 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 a 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.)