6 Replies Latest reply on Mar 16, 2017 7:01 AM by AdamReed_1

    calculation from related records

    AdamReed_1

      I'm unable to wrap my head around this.  I have a database which handles publishing contracts and books, and am trying to handle a single payment that is split between multiple authors (who may have written a book together).

       

      I've attached a quite simplified version of the database, which seems to be working correctly except that when I try to calculate the payment due to each author, I get no result.  I do need these many to many relationships as multiple authors write multiple books together (or not) which are included in multiple contracts with multiple payments, etc.

       

      The desired result is in the "author payment" field in the portal on the layout "final", for 500 to appear for Adam, and 600 to appear for Elizabeth and Marianne.

       

      I'd be quite grateful if someone could take a look at this and let me know what I'm missing.

       

      Thanks in advance,

      Adam

        • 1. Re: calculation from related records
          philmodjunk

          What is the difference between a "payment" and a "disbursement"?

           

          AT first, I assumed that each time funds are transferred from publisher to author(s), a new disbursement record is created while a "Payment" documents the contractual amount, with a Join table record providing the "break down" for each disbursement, but after looking at the design more carefully, I'm not sure that this is what you intend.

           

          So please explain in more details how this is supposed to work.

           

          And your relationships do look very problematic for what you want to do, but first things first.

          • 2. Re: calculation from related records
            AdamReed_1

            I should have been more clear, but your assumption is correct: 'payments' are set in the contract, and are then disbursed at various stages (often a year or two or three after the contract is signed).  So a 'disbursement' is the record of the issue of that payment (and also includes deductions such as taxes, fees, commission, etc.).  So I think do want to different tables?

             

            I hope that's helpful?

            • 3. Re: calculation from related records
              philmodjunk

              How many disbursements to a payment?

              • 4. Re: calculation from related records
                AdamReed_1

                Just one disbursement for one or more payments.

                 

                The disbursement is like a single action (writing a check), but may include multiple line items (payments, etc.).

                • 5. Re: calculation from related records
                  philmodjunk

                  That still leaves things unclear.

                   

                  If you have two authors, does a single disbursement get split into two payment records, one for each author?

                  That would make a field named "author payment" the wrong place for such a field as you'd need a flexible number of such amounts, one for each author. (And I am assuming that the percentages for how the income is divided up is specified in the contract.)

                   

                  Your payments don't link to authors, they link to a contract, BTW. Do you have one contract record for each author for each book? or one Contract record for each book?

                  • 6. Re: calculation from related records
                    AdamReed_1

                    Your first question about splitting disbursements is actually the subject of some debate about the design, and ideally it will eventually work both ways.  A single disbursement should reflect payments to both authors, but down the road I would like to be able to generate separate statements for each author.  So we'd like it both ways.  For the time being, though, we do want to represent the complete disbursement of a payment (i.e. to both authors) in the same disbursement.

                     

                    We do need separate "author payment" amounts as these amounts will sometimes be different (not all splits are 50/50, in some cases one author may have an additional expense deducted from their check, etc.).

                     

                    The payments are linked to the contract, and the contracts are linked (I think appropriately) to books.

                     

                    I did some experimenting and did come up with the attached (which includes a script to handle the calculation instead of a field).  I wonder if this is a step in the right direction?