Ok so I feel like a real dunce for asking this but here goes…
I have three tables and I’m trying to get the relationships between them right.
I have Orders table which is order_id, order_no and supplier.
I realise I should have a separate table for supplier but it is what I have inherited for now. I have an order_pdf table which has 4 cols: row_id, order_id, order no and pdf_url and I have a third table _lines which as all the line details, including (not a total list) line_id, order_id, order_no, qty, amount, received, invoiced etc etc.
What I need to achieve is a table listing of all the details and a link to the order pdf url, grouped by order_no (not order_id).
I have spent all day trying to get the tables linked, reading though this forum, looking at sample apps and I seem to have had partial success but can’t seem to get the details out into the orders table.
I have created two virtual columns in my _order table: order_id which links to related lines in my _lines table and pdf id which links to related lines in my order_pdf table.
This seems to have worked because when I look at the data for the _order table I see the related ids for lines and pdfs.
But how do I link the detail columns into the _orders table like qty, amount, invoiced(status), pdf url for example?