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?