Removing already used items from dropdown list of a reference column

[cboteros]

Hello everyone,

To give some background, I have 2 normal tables (ORDERS, PRODUCTS) and 1 intersection table (ITEMS) in this way:

• ORDERS -< ITEMS >- PRODUCTS
  • This should read:
    • 1 ORDER can have multiple ITEMS (ITEMS[itemOrder])
    • 1 PRODUCT can be in multiple ITEMS [ITEMS[itemProduct])

Lets supposed that I have 3 PRODUCTS: 

• Apples, Pears, Bananas

What I want to achieve:

• Once a user creates an Item that has "Apples" in "Order 1", the dropdown for the ITEMS[itemProduct] for the next Item the user adds to the same "Order 1" should NOT show "Apples" in the list (it would show only "Pears" and "Bananas") 

I believe that the solution to this is in adding a "Subtract List" formula within the "Valid If" section of the ITEMS[itemProduct] column, but I'm not able to make it work as I expect... Can anyone point me in the right direction please?

[Marc_Dillon]

---

@cboteros wrote:

I believe that the solution to this is in adding a "Subtract List" formula within the "Valid If" section of the ITEMS[itemProduct] column

---

Yep

Products[id] - SELECT( Items[ItemProduct] , AND( [ItemOrder] = [_THISROW].[ItemOrder] , [id]<>[_THISROW] ) )

[cboteros]

Thank you @Marc_Dillon ,it worked like a charm!