Displaying data in table view from more than one table

[callumgilhooly]

Hi,
I've searched through the forums and haven't seen an answer to this but apologies if I've missed it.

Hopefully I can explain this so it makes sense...

I have a Database with 4 tables in it:
Orders
Clients
Suppliers
Payments

Now this all works exactly as I want it to but I would like a field from the payments table to be displayed in the Orders table view. (it doesn't need to be in the orders detail page) My first thought was to have a look up in the orders table to display the relevant payment reference. When I try this, I get the error message stating that "orders cannot be the parent and a child etc etc."

What's the best way to get this displayed? I'm a designer rather than a coder and not sure if I should be looking to do this in the PHP, DB or JS.. Should I be looking to modify the template or create a hook?

Many Thanks in anticipation

James

[a.gneady]

Hmm .. you might need to add an intermediate table, "order_payments" for example, that contains a lookup to payments and a lookup to orders. You can then use it to link one or more orders to one or more payments. This way, you can see all related payments under each order, and all related orders under each payment.