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)
Solved! Go to 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])))))
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
Thank you so much, it really helps me a lot. How about I donโt want to see the โinvalidโ value on the list?
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])))))
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
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.
User | Count |
---|---|
40 | |
34 | |
29 | |
23 | |
17 |