Here is the attachment
When you refer to the value of a summary field in a calculation, you get a result based on the grand totals, not sub totals located inside sub summary layout parts.
To get the sub totals so that you can use them in a calculation, use the getSummary function. The "break field" parameter should be the same field that you have specified in the sub summary layout part that shows the sub total you want to use in your calculation.
I cannot seem to get it to work. This is what I created:
GetSummary ( amountPaymentSummary ; amountPayMinusInv )
- amountPaymentSummary = Total of amountPayment
- amountPayment = If ( transactionType="Payment" ; transactionAmount ; "" )
- amountPayMinusInv is the field that shows the calculation for the balance amount in the report.
I also created a GetSummary for the invoices. Then I created a new field: getSummaryPaymentsMinusInv (getSummaryPayments - getSummaryInvoices). Nothing shows up no matter where I place the fields.
amountPayMinusInv may not be the correct field to specify as the break field.
What field did you select as the "when sorted by" field for your sub summary layout part where you can see the total you want to use in your calculation?
That's the "break field" to use in the getSummary function call.
And where exactly on your report are you using this value? (You may be in the wrong group of records to get the correct value via GetSummary)
I finally had time to work on this some more. I created Get Summary fields to obtain sub-totals for invoices and payments with the break field of transaction type. Those fields produce the correct result. However, the balance field returns the same number as the summary for payments. My formula for the balance is: GetSummary Invoices - GetSummary Payments.
That is to be expected. Get summary evaluates from the context of the current record and the group of records with the same value in the "break" field that are contiguous with it. Thus GetSummary invoices will be zero as you are calculating this in a group of Payment records.
It looks like you need to use Get Summary with the Company field as your break field.
With company as the break field I now get 0 for the summary amount for payments and invoices.
Does your current sort order include that field?
I can see a "company" based sub summary layout part, but I don't know exactly what field is specified as the break field for it. Whatever field that is should be the field used with GetSummary and you must have your records sorted in an order that includes that field in the sort order before it will return a sub total.
See attached. Fields are ...
1 = Company ; 2 = transaction type; 3. date 4. GetSummaryInvoices; 5 GetSummaryPayments; 6. GetSummaryInvoices-GetSummaryPayments
Sort order = 1. Company; 2. Transaction Type; 3. Date
I have tried using as the break field Company, Transaction Type and Date. Company and date return no data. Transaction Type returns the correct amounts, but the balance field (GetSummaryInvoices-GetSummaryPayments) is not correct.
But do you sort by a company name field or a company ID? Which do you specify for the upper most sub summary part? (Can't see that in your post )
What version of FileMaker are you currenlty using? (I may choose to upload a demo file that you can compare to yours and don't want to use a file format that you can't open.)
Sort by a company name.
The report is set-up as follows:
Sub-summary by company
Sub-summary by transaction type (Invoice, Payment)
Sub-summary by transaction type (totals)
Sub-summary by company (Balance)
On Filemaker Pro 12
Sometimes I can't see the obvious. I tend to set up revenue, expense type systems with separate fields for income and expenses. Am I correct that you use the same field for both income and expenses?
I think that's the issue here. If so, I suggest adding a calculation field such as:
If ( TransactionType = "invoice" ; -1 * amountField ; amountField )
A summary field that summarizes that field should return the desired balance.
Success! Thank you. I should have mentioned that they were not separate fields.