How to make a Dynamic Dropdown work for both New entries and Edits?

[WillowMobileSys]

I know this must have been encountered but I am not finding any discussion on the boards. so here it goes.

I have a Pricing table that allows entering Prices for all items in the Inventory. As I enter and Save Prices, I want those already priced items to drop off the Product selection list.

I have this part working by implementing the following Valid_If expression on the Ref column for Product selection:

SELECT(Inventory[Product Code], NOT(IN([Product Code], SELECT(Pricing[Product Code], true))))

Image of Dropdown showing the only Product remaining out of a list of 32

The problem I am facing is that when I bring up a row for Editing the price, I am presented a message that the Product item is not valid. This makes sense based on the selection expression I am using.

Selecting a row and then Tapping Edit, opens the form and shows the Product selection as invalid

How do I implement the Valid_If such that on New entries it shows the possible items remaining to be selected and when I select a row to Edit the items already entered are known as valid items as well?

[Fernando_Lopez1 · Accepted solution]

WillowMobileSystems:

SELECT(Inventory[Product Code], NOT(IN([Product Code], SELECT(Pricing[Product Code], true))))

TRY THIS:

SELECT(Inventory[Product Code], NOT(IN([Product Code], SELECT(Pricing[Product Code], [Product Code] <> [_THISROW].[Product Code]))))

[WillowMobileSys]

That was it!! Thank you!!