Unique reference

[bammed]

Hi guys. So I have order capture form.
Product (Table)

Product ID (column)
Order Details (Table)
Order ID (key)
Products (column/ref to Product ID)

What I want to achieve is, if Product ID1 is already referenced to Order ID1, then the Product ID1 won’t be available for selection anymore for another Order ID.

Here’s what I have:
(2 selection of the same Product at the same order/invoice. It should only be one because the products only have one quantity each)

[Suvrutt_Gurjar]

Hi @bammed,

WelcomtoAppSheet community.

If I have understood your table structure correctly , you may wish to have an expression something like below in the Valid_if field of the Product ID column

NOT(IN([_THIS], SELECT(Order Details[Product ID], AND([Order ID]=[_THISROW].[Order ID],NOT(IN([OrderDetail Id], LIST([_THISROW].[OrderDetail Id])))))))

The assumed table structure of Order Details table is

OrderDetail ID- Key column
Order ID- Referenced from the Order Table
Product ID- Referenced from Product Table
Other columns such as
Quantity
Total Cost etc.
The referenced Product table is assumed to have [Product Name] as label column and [Product ID] as key.

The expression is based on the concept of " Preventing Duplicate Field Values" in the help article below

help.appsheet.com

List Expressions and Aggregates

List expressions use operators that result in a list or numeric value.

[bammed]

Thank you so much, it really helps me a lot. How about I don’t want to see the “invalid” value on the list?

[Suvrutt_Gurjar · Accepted solution]

Hi @bammed,

Could you please try an expression something like below in valid_if of Product ID column

SELECT(Products[Product ID],TRUE)-SELECT(Order Details[Product ID], AND([Order ID]=[_THISROW].[Order ID],NOT(IN([OrderDetail Id], LIST([_THISROW].[OrderDetail Id])))))

[bammed]

Wow. It works the way I want it, wow. Thank you so much for the effort and help but I have one last request, can you please tell me how it happened using that “formula?”/code? I really want to understand that certain group of expressions

[Suvrutt_Gurjar]

Hi @bammed,

Thank you for the update and nice to know that the expression was useful to you.

As I mentioned in my earlier post , the expression uses list expressions. You may wish to refer the article I mentioned on list expressions and particularly section " Preventing Duplicate Field Values" within that article.

In the above particular expression , we have used that concept.

In the expression ,

SELECT(Products[Product ID],TRUE) - SELECT(Order Details[Product ID], AND([Order ID]=[_THISROW].[Order ID],NOT(IN([OrderDetail Id], LIST([_THISROW].[OrderDetail Id])))))

the part SELECT(Products[Product ID],TRUE) constructs a kind of master list of all the products

and the second substracted part, that is SELECT(Order Details[Product ID], AND([Order ID]=[_THISROW].[Order ID],NOT(IN([OrderDetail Id], LIST([_THISROW].[OrderDetail Id])))))

keeps deleting products from the master list as the user keeps selecting a product. Please note the use of [Order ID]=[_THISROW].[Order ID] part of expression ensures that the subtraction is applicable for the particular order only.

Hope this helps to explain.