Calculating totals

[Dataslinger]

Apologies if this posts twice - I thought it went through - but now do not see it.

Given a dataset of Donors and Payments on one flat file. So you might have:

Steve Smith - $200

Steve Smith - $150

Steve Smith - $100

We need to total the amounts for each donor.

Steve Smith - $450

Can do with a sub summary field when sorted by "Donor" and it looks good in FileMaker - but want a field that holds total so this can be exported to an Excel file that simply has one record for "donor" and total "amount".

Is there an easy calc field to total the amounts by Donor?

TIA - Dataslinger

[js22]

Hello,

I have an idea that might work for you. You need to set up two tables. The first table has your donor name and a calculation field named totals.

The second table consists of donor name and amounts

Link the two tables by the donor name.

Then set up a layout that displays information from the first table with a portal with the amounts field.

Here is a template that I think does what you want. Look at the buttons as well. If you export to excel it also displays each donation in a column and in a separate column it displays the totals.

If you export as a tab delimited file it only displays the donor name and the total donation amount.

feel free to ask any other questions

js22

Calculation.fp7.zip

[Dataslinger]

Hi JS22 - this seems to be on-track and it appears to work just fine as I enter fresh data in your sample database... where I think this breaks... is importing existing data sets into it... the table with Donor and Total - has the correct total - but there are multiple instances of the Donor (name). I need one donor - matched with an accurate total amount. Still playing with it though - thanks - Dataslinger.

[mr_vodka]

If you create a self join relationship keyed on the donor field, you can have a calc field of Sum (SelfJoin::Payments).

Then you can run a scipt to omit the duplicate records and export out.

Do a search in these forums for +duplicate +records and there should be lots of postes dedicated to different techniques including ones for FM5.