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 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.
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 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!