Button to populate a separate table from another table (and sub table)

Hi,

I hope you can help. I'm new to Ninox and have read a lot of the manual and watched some youtube examples but I'm still struggling with a little of the syntax.

I have 3 tables

Purchase Orders

PO Items (composite sub-table of Purchase orders)

Stock table

When a New Purchase Order is created (including PO line Items) I want to create a new record in the Stock table using some of the fields on Purchase Orders and PO item. I have managed to get a lot of it working with the following code on a button...

let xPODate := 'Purchase Date';
let xPONum := 'PO Number';
let xPOID := number(this.Id);
for p in select 'PO Items' do
let xProduct := p.Product;
let xQuant := p.Quantity;
let xPricePerUnit := p.'Price Per Unit';
let xPandP := p.'P&P Per Unit';
let xSup := Suppliers.Name;
let i := (create Stock);
i.(Product := xProduct);
i.('Date Purchased' := xPODate);
i.('PO Number' := xPONum);
i.Seller.(Name := xSup);
i.(Quantity := xQuant);
i.('Price Paid' := xQuant * xPricePerUnit);
i.(Status := "AWAITING DELIVERY")
end

The Stock table update works fine, but I cannot restrict the PO Items on my select to be just the PO Items for the related Purchase Order. The above code populates all records from PO Items into Stock. I have tried adding a where clause to the select but am unsure what field to use to join between Purchase Order and its many PO Items.

How does a table link to its sub table? I cannot see any field on the PO Item table that shows the parent table id. I assume this field is hidden from view - is it possible to see the raw database info somehow?

Thanks,
Robin