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

(Karl Rolfe) #1

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) #2

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

(Karl Rolfe) #3

Yup @Aleksi_Alkio that would be _order

(Aleksi Alkio) #4

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) #5

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

(Karl Rolfe) #6

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

(Karl Rolfe) #7

Therefore _order table is the intermediate table

(Aleksi Alkio) #8

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) #9

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.