Filtering on connected field values

This question was asked but not answered last year here. I think I have the answer. No, take that back. I have an answer, but it’s not a very good one and I’m hoping that somebody will either show me a better way or perhaps the devs at Tadabase can improve this.


My example

My example app is a simple to-do database. There’s a Tasks table, and a child Notes table. Notes are connected to Tasks. Most of the time, users will view the notes in a table at the bottom of a detail or edit view for the task record.

But I do have a dedicated Notes page that shows all the note records in a table. And sometimes users looking at the Notes table on the Notes page will want to filter note records on some value in the Task record. This would allow them more easily to print those notes out or export them. To take an easy example, user wants to find notes relating to a task whose description (primary field) = “Call Jim in Atlanta”. User would like to filter for tasks that contain “Atlanta” (or “Jim”) and find the connected notes.


The problem: It looks like it’s impossible

Initially, it simply isn’t possible to filter on connected fields. There is a connection field in the Notes table that I call TaskConnected. But when I click on the Add Filters button above the Notes table, that connection field simply isn’t displayed at all.

So the first thing I tried was to create an Equation field that could capture the Description field from the connected task and which could then be used to filter notes. Nope. Apparently an Equation field cannot reference a connected field.

But turns out that a Text Formula field CAN reference a connected field. So I created a Text Formula field that I initially called LinkedTaskDescCaptured. It has this formula:

Notes (Linked Task) > Tasks > Description

Unfortunately, this still doesn’t quite solve the problem. The text formula field does appear in the list of filterable fields for the Notes table — which is some kind of progress — but the menu of comparison operators is limited, and doesn’t quite seem to work. In any case, the menu of comparison operators doesn’t include “contains” which is what I really want here. So I can’t ask if the Task Description field contains “Atlanta”.


The solution (or if you prefer, workaround)

The solution requires the creation of TWO fields — both of the two fields I have just been talking about:

  • First, create the text formula field that simply references the connected field
  • Second create an equation field that references the first field

And miraculously, this works! I named the first field “utility:TaskDescCaptured” and I named the second field “TaskDescFilterable”. In my Notes table, when I click on the Add Filters button, I can define a field that looks for TaskDescFilterable contains “Atlanta” and find the notes for the task I’m interested in.


Yeah, it’s nuts. But I’m glad that there is a way to do this. And if there is a better way that I haven’t found yet, I beg someone to share it with me!

1 Like

@WilliamPorter -

I haven’t tried this myself but would a rollup field work to apply a filter too? The rollup field can concatenate all of the notes entries into the tasks table.

Just a thought…

I looked at rollup. Perhaps I’m doing it wrong but I don’t think so.

I defined a rollup field in the Notes table, used the Concatenate formula to get the connected Task value. (For what it’s worth there will only be one. Notes > Tasks is of course a many-to-one.) The field thus defined does work. In the Data Builder’s Records tab, I can see that column displaying the text of each parent note record’s description field. So far, so good.

But it doesn’t solve the problem I was trying to solve. Over on the page where I want to filter a Notes table, a rollup field can’t be filtered with the operators I want. The only operators available are is, is not, is blank and is not blank.

Since task descriptions typically involve many words, usually a sentence or two, filtering this way would require the user to know the exact text of the task description, which is not practical.