FIltering M:N rows in a drop-down field by another relation

pixelplumber

Hi all.

Coming back to an old app that needs to be updated. It was a call logging/tracking app that allowed an operator to log information of interest within a call record. Each bit of info to log was a 1:N drop-down list relation off that central call record. V1 was for just a single 'campaign' and now I'm now looking to re-tool this to support multiple campaigns for multiple stakeholders at the same time.

I want to allow run-time configuration of which information logging relations are valid for a specific campaign . So I'm assuming I'll need M:N links from a campaign relation to all of the info logging realtions (specific rows) , then I'll have to make sure that the call record filters the display of fields and valid choices within those fields based on the campaign that a user/operator is assigned to as a member.

I'm looking for any examples in the current demo apps that filter a N:1 or M:N drop-down type fields by membership in another relation but not having any luck finding anything.

gabriel

Filtering by anything, regardless of it´s 1:n, n:1 or n:m, can directly be done in the corresponding query filters (in relationship inputs, lists, calenders and so on). In cases where you need to filter by multiple values, you might need to use the = ANY or <> ALL operators as well as sub queries.

As for examples, you will find them in pretty much all our public apps. Like in the app REI3 Tasks, on the task form for the task owner input:

pixelplumber

Cheers Gabriel, I realise I misunderstood the subquery feature quite a bit; I didn't see that the UI can nest down multiple levels with the little eye icon. That makes some of the more complex filters in the example apps make sooo much more sense now... 🤦‍♂️ 😄. I so far only used simple single attribute filters.

A couple more questions:

If I filter the records in M:N fields on my phonecall relation to only show those entries associated with a specific call campaign, is that filtering lookup done for all fields when the form is loaded or as the individual field is clicked on?
Is this a use case for a collection to speed up the filtering lookups or would just making sure the M:N relations are indexed be enough? Its going to max out at 10 concurrent users (will re-up the pro licenses shortly) so not a heavy load if the extra complexity won't be a noticeable performance improvement.
I'll probably try at least one collection just to learn them.

Thanks again, really appreciate it.

gabriel

pixelplumber

A field filter is always applied to the data in the field - both the selected data and the data being shown in the selection dropdown. If, for some reason, the currently selected data becomes invalid (eg. filtered out), it will be removed when you open the affected record on the form.
Collections have multiple purposes. You can use them to speed up lookups, as default values in fields, as data sources in functions, to show notifications in the header or menu and so on. But they have a very important downside: They are not updated automatically - so if your data changes (and therefore the filter would change), you need to update the affected collections (via backend/frontend functions). Lookups via regular queries (eg. just working live with data) are perfectly fine - that is what DB systems are made for. Relation attributes are also indexed by default. The things you might want to index in addition to that, are non-relation attributes that you filter with (like text, numbers or booleans).