6 Replies Latest reply on Aug 18, 2011 8:49 AM by LarryMastromatteo

    Trailing Grand Summary and a summary field

    LarryMastromatteo

      Title

      Trailing Grand Summary and a summary field

      Post

      I have a work on hand table in my invoice database that I am trying to put a summary field that will add up all of the invoices and subtract them against the contract amount to give a total of work on hand. But when I try to put the summary field in the grand summary part it does not add the invoices properly. So, for example,  on a contract I invoiced $1,000 - 3 times it gives back a result of $9000. What am I doing wrong? Any help would be appreciated! Thank you!

        • 1. Re: Trailing Grand Summary and a summary field
          philmodjunk

          Please tell us more about your database. Describe that "contract amount". Is this a field with a different value in every invoice record such that you need the difference of the invoice and contract amounts on each record or is this contract amount stored in a global field or in a related table?

          If a related table, how have you linked that table to Invoices?

          • 2. Re: Trailing Grand Summary and a summary field
            LarryMastromatteo

            Hello PhilModJunk:

             

            Thanks for answering my querry! 'Contract Amount' is a field in a related table called 'Job List'. The 'Invoice' table is linked to the 'Job List' table by 'PO Number'. No 'Contract Amount' can be the same for 2 to 30 invoices depending on how long the job takes to complete. We invoice monthly. Thanks!

            • 3. Re: Trailing Grand Summary and a summary field
              philmodjunk

              I can see where that's going to be a problem. You can't just look this value up into each invoice record (or use a calculation field to refer to it) in order to compute a total as this would add up the same contract amount more than once when you have multiple invoices for the same PO Number.

              This method will require sorting your Invoice records by PO number to group invoices with the same PO number together:

              Define a summary field, sTotalInvoice as the total of your invoiced amount.

              Define a Summary field, sInvoiceCount as the Count of any non blank field such as the PO field.

              Define this calculation field, cJobBalance_Fract as:

              ( GetSummary ( sTotalInvoice ; PO ) - JobList::ContractAmount ) / GetSummary ( sInvoiceCount ; PO )

              Define yet another summary field, sTotalBalance as the total of cJobBalance_Fract

              All these fields would be defined in your Invoice table and you'd display your results in a trailing grand summary on a layout based on this table.

              Note: In re-reading all the posts in this thread, I notice that you refer to a "work on hand" table but you do not describe what it is. How it is structured and how it might be related to Invoices and JobList. My suggestion here avoids the use of any such table, but that may not suit your ultimate purposes here.

              • 4. Re: Trailing Grand Summary and a summary field
                LarryMastromatteo

                Hello PhilModJunk:

                 

                Work on Hand is a table in the invoice file, it is a summary of the work on hand. In a Sub Heading is Job Name, Po Number and contract price sorted by Job Name. Under the Sub Heading is the Body where the invoices are listed by date, invoice number, and amount. Under the Body is another Sub Heading lising the total amount invoiced and the remaining amount of Work on Hand per Job Name also sorted by Job Name. Finally is a Trailing Grand Summary where I would like the calculation of the Work on Hand of all Contracts left to invoice. Thanks Again!

                • 5. Re: Trailing Grand Summary and a summary field
                  philmodjunk

                  Is "work on hand" a table or a layout? What you are describing is a layout, not the table on which it is based. If this is a layout and you see "invoices" listed in "show records from" on Layout setup, then what I have recommended should work for you.

                  • 6. Re: Trailing Grand Summary and a summary field
                    LarryMastromatteo

                    Hello PhilModJunk:

                     

                    Yes, it is a layout. Thank you for all your help. It is very much appreciated!

                     

                    Larry Mastromatteo