Filter a dropdown list so the already used options are not shown

[Krasimir_Trapchev]

Hi all,

I have the following use case I am having troubles solve. In the following structure:

• table “Proposals”
• table “Proposal lines” (connected to the Proposals by Proposal ID and containing a property Product ID, loaded from the Products table below.
• table “Products”

In each Proposal details screen, there is a button Add a new product to the proposal. The button opens a new form window, which writes in Proposal lines. In the form screen that opens, there is a drop down that selects the Product IDs (although it displays the Product names) of the records from the Products table.

Once a Proposal line with a particular Product ID has been added, I need to makes sure that next time a user is adding a Proposal line, the Product IDs already selected in the previously added Proposal lines for this Proposal will be filtered, so the user can select a given Product ID only once in all Proposal lines for a given Proposal.

So far I have tried to filter the already used Product IDs with an internal relation in the Proposals line, then compare them to All Products and then remove all used Products with an Array Remove Elements. Then I am trying to set a Filter on the Dropdown, where the Product ID for the new Proposal line row is included in the so refine list of Unused products but it doesn’t;t work. I also tried to convert the same set to a Joined List - still no luck.

I have spent more than 8 hours on experimenting on various approaches.

Any ideas? Thanks a bunch in advance.

[Uzo]

I read 3 times your description, and can’t figure out what you are trying to do…
Can you explain in a real-life scenario what is the process? using actual products and actions that user will do?

[Jeff_Hager]

Couple of ways you could do this, but try this first as I think it’s the easiest.

• First, assuming you already have a relation from Proposals to Proposal Lines, add a Lookup column in the Proposals table that returns an array of Product ID’s from the associated proposal line records.
• Create another lookup column that returns all of the Product ID’s from the entire Products table.
• Create a Remove Elements plugin column. Feed it both lookup arrays and it should return an array with only items that have not been chosen yet.
• Create a relation column that links that final array to the products table.
• Set the source of your choice component to that final relation.

Another thought is this.

• Create a relation from the Product table to the Proposal Line table.
• Create a Lookup column to return all matching Proposal ID’s from the Proposal Line table.
• Create a Joined List column to convert that lookup array to text.
• Then just filter you choice component where the joined list does not include the Screen Value of the Proposal ID.

[Krasimir_Trapchev]

Thanks, @Jeff_Hager. Much appreciated - all works now.