How to filter records based on a connected record value?

Here is the problem. There is a “Client” table that is used to display only records that belong to a customer. There is a unique Client record per customer.

For instance, a customer has multiple invention records (“Invention” records connected to the Client record). Employees of the customer (“User” records connected to the Client) can be inventors.

To add an inventor to the invention, it would be good to filter the User records that belong to the client. In other words, how can I filter the “User” records to show only those that are connected to the Invention table’s Client?

1 Like

When adding a connection field to a form, you can double click the field and select what data to select from. If you don’t see the available connection, you will need to add a new connection from the desired datatable.

This may help:

@ivan @SafetyUniversity

This is actually something that came up recently in my conversation with @SafetyUniversity and we will be adding this type of filtering capability to the form. Similar to Data Source in other component where you can filter by records connected to logged in user’s field.

Timeline is about 1-2 weeks.

@ivan, upon reading this again, its not what I had assumed.

If I understand correctly, your table is structured something like this:
(I called it Companies, but same idea).

And when adding a new invention you want to only show Users that are connected to the client this invention belongs to. Right?

Wouldn’t that be your typical dependent dropdown?

Actually the structure almost the same, with an additional “Inventors” table to add information (rank of the inventor in the invention: 1st inventor, 2nd inventor…).

This is still the same issue: when a new “Inventor” is created, the record is empty. It is not * yet * connected to a company. Even if the record is created from an “Invention” table as a connected record (the “inventor” record is connected to a particular invention record), there is still no connection yet with the company.

Here, the company could be set * after * the form is submitted with a record rule. So that won’t help with the filtering of the Users (in the Inventor form creation).

It would be great to be able to filter a field based on the Logged In user. The Logged In user is connected to the Client (his/her company). Therefore, if it was possible to filter only the users connected to a logged in user field (Company field), that would work.

Right now a possible workaround is to use two forms:

(1) The first form to create an “inventor” record. The first form displays nothing other than just the “Submit” button. It creates a new inventor record connected to the company (form rule), and redirects to the second form,

(2) The second form edits the inventor form using the record filtering (because the Company was set with the first form).

This kind of works, but isn’t great in terms of user experience. It forces the creation of empty records. If a user stops in the middle of the process (creates the inventor with the first form but doesn’t complete the process with the second form), then there will be some invalid inventor records.

My original idea was to filter based on a connected table record, instead of just the current record:

in the “Inventors” record creation form, allow to filter records (User records) that are connected to a Company connected to the “Invention” record.

That would be the holy grail.