2 Replies Latest reply on Jun 23, 2015 6:06 AM by OliverMartin

    Compare and display records from two tables on one layout

    OliverMartin

      Title

      Compare and display records from two tables on one layout

      Post

      Hi,

      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.

      Any suggestions?

       

      Thanks in advance

        • 1. Re: Compare and display records from two tables on one layout
          philmodjunk

          The companies don't necessarily create one payment per invoice, they may pay for several in one go.

          Isn't it also possible that a payment will only partially pay off an invoice or that they might overpay due to a mistake?

          You need a relationship that links payments to invoices and if your answer to my question is "yes", it's a many to many relationship where you can link one payment to many invoices and also one invoice to more than one payment. If the answer is "no, this never happens", then you have a one to many relationship, one payment to possibly man invoices.

          • 2. Re: Compare and display records from two tables on one layout
            OliverMartin

            Thanks for you response Phil,

            The answer is Yes, but...

            I can sit and manually try to tie invoices to payments and payments to invoices, use calculations to work out how much is outstanding from a part-paid invoice and how money in compares to payments outstanding, but what I really need is a running total of:

            Invoice1 +£1500 +£1500

            Invoice2 +£500 +£2000

            Invoice3 +£750 +£2750

            Payment -£2000 +£750

            Invoice4 +£750 +£1500

            So I can see at any point each transaction and a running total. This would save all the manual matching of payments to invoices and would give me a nice statement page that I can produce for any companies that want to know their current balance.

            I was thinking perhaps of a 3rd table where a record is create automatically from the OnRecordLoad script event trigger in either the Invoices or Payments tables which links the +/- amount based on the creating table and loads the related company ID at the same time. This could then be viewed as a single list of transactions in and out.

            But I wonder if this is a smart way to do it? Do you have experience of this kind of solution?