Compare and display records from two tables on one layout
I'm sure it has been asked before, but I couldn't find this discussed on here before.
I have created a table for invoicing, each record is one invoice and contains, amongst other things, a total field and a company name (ID).
I have another table of payments that allows me to log any payments received from any companies. The companies don't necessarily create one payment per invoice, they may pay for several in one go.
What I'd like to create is a single layout (probably from the Companies table) which shows all invoices and all transactions, but ideally this needs to be shown together in chronological order, so I can see invoice, invoice, invoice, payment, invoice, payment...etc.
I know I can do this by putting two portals on the same screen, one for all the invoices, one for all the payments, but it will be very hard to relate or to create a running balance.
Thanks in advance