Filter formulas are essential in Honeycode and one of the most important ways to get data from your tables into your apps. Filter formulas allow you to customize what you display to your app users and are often the key to building impactful apps.
=Filter(Table, "conditions", condition parameters)
Through these challenge exercises, you will get to flex your filter formula writing muscles and practice some basic syntax. We’ll kick things off with an overview of filters. Then we’ll introduce the challenge exercises that build off of each other using some of our templates. If you’re new to writing filter formulas, we advise going through this article’s content in order, which follows this format:
Filter formula syntax
Here you’ll learn about the value of filters and the nuts and bolts of how they have to be written. We will keep things pretty high level.
If you’re feeling ambitious and want all the details, do know we have a couple other resources to help sharpen your filter formula skills. Here are the links:
- Filter() article
- Formula syntax article
- Making the App You Want in Honeycode Course
- Harnessing Data in Honeycode Course
- Filter and Sort a List (this feature can sometimes be used instead of writing filter formulas)
Challenge Exercise 1: Quiz time!
Using your knowledge of filter formula basics, your challenge is to put your skills to the test to see if you can ace a quiz.
Challenge Exercise 2: Dream Teams
You get to the play the role of App Builder Extraordinaire at a new company, where you will be challenged to modify Honeycode’s Team Task Tracker template. Two recently established teams, the Green Dream Team and the Orange Dream Team will be taking on some tasks. They need you to build out two new screens to the existing app that require filter formulas.
Challenge Exercise 3: Director’s Cut
Building off the previous exercise, the Director has asked for their own screen in the app that highlights some of the high priority projects. You will be challenged to build the screen to their specifications and write filter formulas to satisfy their request.
Filter formula syntax
Filter formulas are super powerful in Honeycode, especially when used in your apps with Lists and Data cells. Filter formulas allow you to set conditions, and when used in apps, often specify which rows to grab from a table. This comes in handy when app users might only find value in a subset of a table, as seeing an entire table on an app screen can often be overwhelming. For example, maybe your app users only want to see the rows with tasks that are Overdue, the projects that are In progress, or the fruits in an inventory that are Red.
Whatever your case may be, Honeycode filters need to be written a certain way or (to be technical) written according to the filter formula syntax. Depending on which rows you’re trying to grab, filter formulas can look very different. They all, however, have to follow this syntax for the filter formula to work:
=Filter(Table, "conditions", condition parameters)
Builder tip: Conditions and condition parameters are technically optional, but if you want some of the fancy stuff, then conditions (condition parameters some of the time) will likely play a role.
Example
To illustrate, we will walk though a filter formula from our Simple To Do template. If you want to bring the filter formula up yourself, here’s how you locate it:
Open the Simple To Do template > select Builder > Todo app > My Tasks screen > OverdueTasks list > Data tab:
=Filter(Tasks,"Tasks[Done]<>% ORDER BY Tasks[Due]","Yes")
Before we jump in, can you tell what this filter formula is instructing Honeycode to do?
If not, no worries. Here’s a high level explanation. We can see that this filter formula is acting as the data source for a list. This means that it is instructing Honeycode which table to go to (the Tasks table) and to only grab certain rows that meet a set of conditions.
The conditions, in this case, are the rows that have a value that is not equal to “Yes” in the Done column. It’s also instructing to order these rows based on their values in the Due column (in this case, that’s chronologically, as the Due column contains dates).
Let’s break it down even further by going through each piece of the formula by order of appearance. Sometimes it helps to work through these as if you’re speaking to Honeycode with natural language, for example:
=Filter(Tasks,"Tasks[Done]<>% ORDER BY Tasks[Due]","Yes")
| Filter formula | Natural language description | Syntax component |
|---|---|---|
Filter() |
Hey Honeycode  I need you to grab some specific rows for me. |
Filter function |
Tasks |
First, go to the Tasks table because that's where the rows are located. | Table |
Tasks[Done]<>% |
Once in the Tasks table, look at the Done column...specifically for values that are not equal to something % (we'll tell you what the something % is at the end of this formula, so hang tight, Honeycode!). |
Conditions |
ORDER BY Tasks[Due] |
I'm also going to need you to order these rows based on the Tasks table’s Due column (Since this column is formatted for dates, please do so chronologically). | Conditions |
"Yes" |
Now, back to that something %. Replace the % with the word Yes, so the condition reads like this: Tasks[Done]<>Yes. Basically, go to the Tasks table's Done column and grab rows that have something other than the word Yes. |
Condition parameters |
One last time: Go to the Tasks table and grab the rows that don’t have the word Yes in the Done column and order those rows chronologically based on their values in the Due column. Now show me those rows, Honeycode! 
Now that we’ve walked through an example, let’s take another look at the core pieces of Honeycode’s formula syntax:
=Filter(Table, "conditions", condition parameters)
=Filter()
The filter function is used primarily to grab a certain set of rows from a table. It always starts with an equal sign =, which activates the filter formula. Wrapped inside of the parentheses, is where you can add the Table, Conditions, and Condition Parameters which define the rows you want to grab. Each of these are separated by commas.
Table
The table is the first thing you need to place inside of the parentheses, and it tells Honeycode where to start. It’s the bucket of data you are looking to grab from. Do know that you don’t necessarily have to provide a table, as you can get fancy and use things like another filter formula, but we’ll keep it simple for now. Remember that the table needs a comma , after it. Then you start adding the conditions.
In the example, the Tasks table satisfies this part of the formula**.** It means we’re telling Honeycode that the Tasks table is where we want to start at and grab data from.
Conditions
After telling Honeycode which table to grab from and placing the comma, you can now begin the conditions, which begin to specify the set of rows you want to grab. Conditions are always wrapped inside a set of quotation marks. Conditions give you the ability to truly customize and show your app users exactly what you want them to see in the manner of your choosing.
Conditions can have several different things show up in them. Here are some of the most common:
-
Columns: When telling Honeycode which rows to grab, you will often want to specify a column(s) in your conditions. Whenever you do this, list the table and then immediately after place the column in square brackets like this:
Table[Column]. In the example above, we see the Done and Due columns from the Tasks table being referenced:Tasks[Done]andTasks[Due]. -
Operators : If you choose to use them, operators can do several things. Operators are things like
ORDER BY,AND,OR,<,>,<>,=,ASC,DESC, etc. TheORDER BYoperator can arrange data (alphabetically, chronologically, or numerically) and is often used with a column, just like we saw in the example above. TheANDas well as theORoperators can be used to create rules around what must be true for a row to be grabbed. See the additional example #1 below. -
Variables: You can use variables inside of conditions. For example, the
$[InputRow]variable is quite handy if your user has navigated from a list to the detail screen of a particular row. Also, the$[SYS_USER]variable can be used if you need to reference the specific app users name or items that are assigned to them. -
Functions: You can get creative with different functions by adding things like
FINDROW()orTHISROW()to your filters, but that’s a bit advanced for what we are covering here today.
Condition Parameters
Conditions parameters come at the end of a filter formula after the conditions. The condition parameters are the criteria the filter will check for; furthermore, in the conditions, we can instruct Honeycode to scan a column for something specific by placing a % in the formula. We can later define what the % actually is after the conditions in the condition parameters at the end of the filter formula. Once your filter formula runs, it replaces the % in the conditions with whatever you defined the % as in your condition parameters.
For example: =Filter(Tasks,"Tasks[Done]<>% ORDER BY Tasks[Due]","Yes")
The "Yes" is the condition parameter and will replace the % when Honeycode runs the filter. This means that Honeycode will only grab the rows that have something other than Yes in the Done column. Remember <>% translates to: not equal to. And in our case <>Yes translates specifically to: not equal to Yes. Furthermore, Honeycode ignores the rows that have Yes in the Done column and will grab the rows that have something else in the Done column. For instance, words like No or Maybe.
Also note that condition parameters must be wrapped in quotes when they are a specific value like: "Yes" or "No" or "Strawberry" . If the condition parameters are something else, for example, sorting a column in descending order, then they don’t need quotes and could look like this: Produce[Vegetables] DESC .
Additional Examples
Here are a couple more examples if you need a little more practice. See if you can make sense of the filter formula before looking at the chart.
Additional example # 1
=Filter(Tasks,"Tasks[Due]<TODAY() AND Tasks[Done]=%","No")
| Filter formula | Natural language description | Syntax component |
|---|---|---|
Filter() |
Hey Honeycode I need you to filter out and grab some rows for me. |
Filter function |
Tasks |
First, go to the Tasks table becuase that's where the rows are located. | Table |
Tasks[Due]<TODAY() |
Once in the Tasks table, look at the Due column for dates that are prior to today... | Conditions |
AND |
AND meaning that the previous condition about the Due column and the next condition I'm about to say both need to be TRUE in order for you to grab | Conditions |
Tasks[Done]=% |
In the Tasks table, look at the Done column for values that are equal to something (we'll tell you what the something % is in just a second, so hang tight, Honeycode!). |
Conditions |
"No" |
(Back to that something % ) Replace the % in the conditions with the word No so that it reads look at the Done column and find the rows that have a No and run the formula. |
Condition parameters |
Additional example #2
=Filter(A_POs,"A_POs[Status]<>""Pending"" ORDER BY A_POs[PO Date] DESC")
| Filter formula | Natural language description | Syntax component |
|---|---|---|
| Filter() | Hey Honeycode I need you to filter out and grab some rows for me. |
Filter function |
| A_POs | First, go to the A_POs table because that's where the rows are located. | Table |
| A_POs[Status] | Once in the A_POs table, look at the Status column... | Conditions |
| <>""Pending"" | ... specifically, for values that are something other than Pending. Note: You might have noticed the double quotes around ""Pending"". When you place text values directly inside of conditions as opposed to the % condition parameter technique used with the Simple To Do template, the text value requires the double quotes. | Conditions |
| ORDER BY A_POs[PO Date] | I'm also going to need you to order these rows based on the PO Date column (In this case chronologically, as that column contains dates). | Conditions |
| DESC | And not just chronologically, but chronologicaly in a descending fashion. Meaning the top of the list should be the row with date furthest out in the future and the one at the end of the list to be the most upcoming. | Conditions |
Now, get ready for your challenge exercises!
Challenge exercise 1: Quiz time!
Using your knowledge of the filter formula basics, your challenge is to put your skills to the test and see if you can ace this eight question quiz.
Quiz time!
-
What must be placed in front of
FILTER()in order to activate it? -
What are the three main structural components of a filter formula? Also, what order must they be placed in and with what do you use to separate them?
-
What must be wrapped around conditions?
-
What punctuation do you use inside of the conditions to tell Honeycode that you have condition parameters? Where do you place condition parameters and when do they require quotation marks wrapped around them?
-
If you were going to reference the column titled Vegetables in a table titled Produce, how would write it?
-
Why will this filter formula
=Filter(D_Properties, D_Properties[Customer]=[Customer])get an error message? Hint: there are four reasons. -
How would you explain this filter formula to a friend using natural language:
=Filter(Purchase_Orders,"ORDER BY Purchase_Orders[Date of Purchase] DESC")? -
How would you explain this filter formula to a friend using natural language:
=Filter(Inventory,"Inventory[Assigned To]=$[SYS_USER] ORDER BY Inventory[Status])
Bonus: What’s missing from question 8’s filter formula?
Challenge Exercise 1 Answers.xlsx (9.4 KB)
Challenge exercise 2: Dream Teams
You’ve just joined a new company as App Builder Extraordinaire and have been challenged to modify Honeycode’s Team Task Tracker template. Two recently established teams, the Green Dream Team and the Orange Dream Team will be taking on some tasks. They need you to build out two new screens to the existing app that require filter formulas.
Modification 1: Currently the tasks in the A_Tasks have individuals in the Assignee column. These assignees need to be switched to teams as opposed to individuals. Specifically, they want the Newsletter project’s tasks to be assigned to the Green Dream Team, and the Web Redesign project’s tasks to belong to the Purple Dream Team. Note: This modification doesn’t require a filter formula, but is necessary for the filter formulas in Modification 2.
Challenge Exercise 2 Modification 1 Answers.xlsx (157.6 KB)
Modification 2: They want you to create a two additional screens in the app, one containing a filtered view of the Purple Dream Team’s list of tasks and one containing a filtered view of the Green Dream Team’s list of tasks. They also asked that the tasks be listed chronologically by due date with most upcoming tasks at the top of the list.
They mentioned that styling the screens (borders, colors, etc.) is optional, as they are looking primarily for simple lists on each screen with columns for: Task, Project, Assignee, Due, Status, Order, and Priority. No Detail screen is necessary.
Hint: think about the filter formula like a sentence. First you need to tell Honeycode where to grab the data from then tell Honeycode the specific rows to grab (based on a column) as well as the order.
You’re end result should look something similar to this:
Challenge Exercise 2 Modification 2 Answers.xlsx (8.9 KB)
Challenge exercise 3: Director’s Cut
Building off the previous exercise, the Director has asked for their own screen in the Team Task Tracker app that highlights some of the high priority projects. You’re challenge is to build the screen to their specifications and write filter formulas to satisfy their request.
A_Taskstable and make sure at least four tasks have a status ofNot Started. With those same four tasks, also make sure that two have a priority ofHighand that two have a priority ofMedium.
Modification 1
Your Director has asked that you create a screen to the existing app called Director’s Cut. At the top of the screen, your Director wants a data cell that counts the number of tasks from the A_Tasks table that have a status of Not Started and a priority of High. They said a content box with a label would be nice too.
You’re end result should look similar to this:
Challenge Exercise 3 Modification 1 Answers.xlsx (8.8 KB)
Modification 2
Also on this screen, underneath the data cell, you have been asked to add a list sourced the A_Tasks table that only shows the Tasks that have a status of Not Started that also have a priority of either High or Medium. They also wanted this list ordered chronologically by due date.
They mentioned that styling the screen (borders, colors, etc.) is optional, as they are looking primarily for a simple list with columns for: Task, Project, Assignee, Due, Status, Order, and Priority. No Detail screen is necessary.
You’re end result should look similar to this:
Challenge Exercise 3 Modification 2 Answers.xlsx (8.8 KB)
Great job on these Challenge Exercises! Now see if you can write some filter formulas for your own use case! If you need help building, feel free to ask any questions on our Community here.
| Was this article helpful? |
|---|
- Yes
- No
0 voters