"show" vs "filter" in the form builder

Fairly simple database structure (bit like a to-do list, although this is actually an activity-tracking database). Three tables:

  1. Clients
  2. Projects
  3. Activity

I’ve connected Activity to both Clients and to Projects, per the instructions in the video on creating dependent dropdowns in a form. When I’m entering an Activity record, after connecting to the Client, I want next to see only Projects that are connected to that client.

In the form builder – for entering a new Activity record – there seem to be two ways to do this.

  • In the Edit Field dialog for the Projects connection field, on the General tab, there’s a Show option, with two choices: “All records” or “Projects (Client) connected to this form’s Clients”. Which I take to mean “Show only Project records whose Client value matches the Client value already selected in this form.” See screen capture pasted below.
  • But the Edit Field dialog also has a tab named Filter Record. On that tab I can create a new filter and configure it with the condition “Client / is connected with current record.” The word Client there clearly means “Client in the Project record”, because the other fields that I could filter on are all unambiguously fields in the Project record.

Now the Show option actually works, while the Filter option does not. But I can’t figure out why the filter option doesn’t work; and I can’t find any info explaining when I’d want to use one option or the other and what the difference is between them.

William

No responses at all, in over a week. I’m still wondering about this. What is the difference between the Show option and the Filter option when placing a field on a form?

Or if this is simpler, since I now understand what the Show option does, what is the point of the Filter option?

The general difference is the filtering on filters tab is based on values in the connected (parent/remote) table, while the show is based on filtering data based on either the form or filter by related records.

You can see very detailed docs here with the 3 different types of filtering:

But I’ll try and clarify here as well.

Filter Records:
In your Add Activity table if you wanted to only show Projects that have a Status (field) set to “Active” you would do that in the Filters tab. Since this is filtering based on fields in the parent table. In other words, filter based on values in the remote/parent table.

Show Filters:
Show filters are based on restricting dropdown connected values based on other form or page values.
A Basic example is Dependent Dropdown. A more complex example, is to filter based on the page you’re currently on.

Here’s I hope a relatable example for that.

If you’re a Company with Users (Each user belongs to a Company). Now, suppose you’re inside the details page and you’d like to add a new Job and assign it to a user. But since you’re on a company called “Google” you should only see the Users who belong to this page’s Company. Not Users that work form Microsoft.

There is one more filter which we call limit, to limit records that this user connects to. For this lets assume we have 3 tables. Users, Companies and Jobs. Each User belongs to a Company.

So now when we add a new job and assign it to a company, we only want to limit the companies to records Logged In User is connected to. Again, this we can’t use the filters tab, since each user connects to a company, not the other way around. If the Company connected to Users then you would use the Filters tab.

Rules of thumb:
If you’re filtering by an actual field in the remote table, use the filters tab. Otherwise, you must use the other options.

Thanks for the explanation, Moe. I understand it now!

For my current use case, I added a select field Status to the Clients table and populated that field so client records are now marked either Active or Inactive.

Then on the form for creating new Project records, I use the Filters tab to restrict choices to accounts that are active. This is a filtered dropdown.

But on the Activity form, I needed a dropdown to select a project based on the previously entered client value. So here I used the “Show” option to show only projects linked to the current record’s Client.

Brilliant. And it’s working great!

William