I would be very grateful for some help from an experienced user. I'm a graphic designer and I use Filemaker for client invoices. I am trying to figure out a way to create a calculation for a field that will add up all outstanding invoices. I have a "Year to Date" calculation that tells me everything that has been invoiced. When an invoice is paid, I enter the date in a "Date Paid" field. So what I need to figure out is a calculation that will add up all the invoices that have an EMPTY "Date Paid" field, and subtract them from the "Year to Date" summary field. I've now spent three days trying to do that, and I feel like a total idiot. Thanks very much for any help you can provide! Please feel free to email me at [email protected], or post here, if you have a solution. Thanks again!

Hi Maya,

Summaries are usually used with reports. You haven't described whether you used Summary or a calculation or what your structure is but this should work for you.

Create a calculation (number) called cOutstanding. This will list all outstanding invoice amounts.

Case(IsEmpty(InvoiceDate), InvoiceTotalField)

Then create a Summary field (number) called sOutstanding and select cOutstanding.

To get all paid invoices, create a calculation (number) called cPaidAmount with:

YourSummaryField - sOutstanding

You could also use a report with sub-summaries to accomplish the same thing much easier and would cut down on the number of calcs needed. You would create one calculation (text) with:

Case(IsEmpty(InvoiceDate), "Outstanding", "Paid")

Place this calculation in a leading sub-summary. Sort your records on this calculation. Create only ONE summary when you create the report and select InvoiceAmount. Place this summary in a trailing sub-summary. You don't need a body at all.

Again, it depends upon your needs, but this should give you some ideas to work with.

LaRetta

LaRetta, I can't thank you enough! That worked like a charm... I almost fell out of my chair in amazement! Thanks so much for taking the time to reply to my post. After three days of trying to decipher the user's manual, your suggestions provided the solution in minutes. I am impressed and in your debt. You are the best.

Maya