Ok so I feel like a real dunce for asking thi...

[Karl_Rolfe]

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?

[Aleksi_Alkio]

May I ask first… what is the main table in your app structure (as a mother table)?

[Karl_Rolfe]

Yup @Aleksi_Alkio that would be _order

[Aleksi_Alkio]

Which one do you want to do? #1 - Bring all the data into your Orders table #2 - Show the data directly from the _Lines table

[Aleksi_Alkio]

I would probably choose the option #2 because you have all the data in that table already

[Karl_Rolfe]

I want to show all details from the details table as well as links to associated pdfs

[Karl_Rolfe]

Therefore _order table is the intermediate table

[Aleksi_Alkio]

IF you add a table view for your _Lines table and you group them with the Order_no, should that be good enough? For the link you just need to add a virtual column with the formula like LOOKUP([_THISROW].[ORDER ID],order_pdf,Order ID,URL)

If you want to show them in your Orders table, you can show them with inline views.

[Karl_Rolfe]

Thanks @Aleksi_Alkio ,thats what I tried originally but couldn’t work out the formula I needed. I’ll try your solution tomorrow when I get back to work.