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?
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!
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.
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!
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.
Yes, it is a layout. Thank you for all your help. It is very much appreciated!