Selecting free records on related tables

[Enzo_Zoncheddu]

Good morning, everyone,
I am developing with AppSheet a program that will have to manage orders to suppliers. The procedure is mainly based on two related tables, one of all orders handled and one of the rows that make up the individual order.
When loading the order rows I need to display only the items that for the same order have not yet been included in the document...
I know that I need to enter a formula in the "Valid if" field of the item code, but since the order rows are not yet recorded on the database, I don't know how to proceed.
Can anyone help me with this?
Thank you very much!!!
Enzo

[SkrOYC]

---

@Enzo_Zoncheddu wrote:

since the order rows are not yet recorded on the database, I don't know how to proceed.

---

I lost it there.
You need to have the items somewhere for the app to know.
Unless you create a valid if that prevent duplicates of new stuff.
But, again, I'm a little bit lost

[Enzo_Zoncheddu]

Hi SkrOYC.
I noticed that until I also save the order table the data in the order rows table is not saved. I have to do the check while loading the order rows....

[Steve]

Your problem description is a bit confusing. I'm going to guess you're doing your data entry in a nested form: you open a form to add a new order, then before saving that form, you open another form to add individual items. As you enter those items, you want the list of options generated with Valid If to exclude the items you've just entered, to avoid entering duplicates. Is this interpretation correct?

[Enzo_Zoncheddu]

Hi Steve, using the translator, I actually have trouble being understandable...
Other than that, you understood perfectly what I meant.
Thank you

[Steve]

I try to avoid nested forms, so I haven't actually done what you need, but I suspect you can use the Related ... column of the order row to do it.

My assumptions:

• Orders are stored in the Orders table, with the key column named Order ID.
  
  
• Order items are stored on the Order Details table, with a key column of Detail ID.
  
  
• In the Order Details table, there is a column named Order ID of type Ref that contains the key column value of the row in the Orders table to which the details row is associated.
  
  
• The list of all possible items are in a table named Items, with key column Item ID.
  
  
• In the Order Details table, there is a column named Item ID of type Ref that contains the key column value of the row in the Items table, identifying the item of the order.

In Valid If for the Item ID column of the order Details table, try this expression:

(Items[Item ID] - [Order ID].[Related Item IDs])

Please adjust the table and column names in this expression to match those in your app.

[Enzo_Zoncheddu · Accepted solution]

Hi Steve,
the expression you suggested didn't work because I don't have an "[Order ID].[Related Item IDs]" column, but it finally allowed me to understand additional uses of lists by solving my problem!
I modified your expression as follows:
"(Items[Item ID] - SELECT(Order Details[Item ID],[Order ID]=[_ThisRow].[Order ID]))"

It is probably an improvable expression but it works... 🙂

Thank you very much and sorry if I was initially unclear in the exposition of my problem.
Enzo

[Steve]

Well done!