Upvote 1

SubSelect in Data Master

Answered Guido Kuth ● ago

@All
Is it possible to define a filter that filters a data master to a subselect of another data master. To be more precise:
I have 3 data masters Group, Provider, Contractor. Each Provider can be a Contractor of each Group but only once. I want to populate a dropdown for adding an Provider as a contractor to a group but the dropdown should list only these providers that are not a member of the group so far.
Data Master structure looks like this
Group {id, name}
Provider {id,name}
Contractor {id, name, Provider.id, Group.id}
Thanks in advance for any help...Guido

Replies (2)

Chloe Tomlinson ●

Hello,

If you create a 'set value' action and select a drop down as a target, you can control the values of that drop down. So, as the value, you'll need to create something like this:

Select(Filter(Provider, not( (Select(Contractor, Provider.id) has Provider.id)), Provider.name)

You get the list of all the Provider.ids in the Data Master Contractor (that's the internal select), then you filter all the Providers and get only those who are not in that list. That would get you the list of Providers that are not Contractors in any group.

Finally, since the value of a drop down is a list of texts separated by commas, you execute a select on the result of the filter to populate the drop down.

Try this out and let me know if this works for you!

Guido Kuth ●

Hi Chloe,

thank you very much. You've pointed me in the right direction. Works now like a charm.

Chloe Tomlinson ●

Happy to help! Glad it working now.

Thank you,

-Chloe