Filtering Fields by criteria and then summing

[meganswiderski]

I would like to be able to sum certain values of a field based on a specific criteria. For example, I have orders that were placed in the years 2001 and 2000. I would like to have the sum of the charges for the order in the year 2000 show in one field and the sum of the charges for the orders in the year 2001 show in another field. I thought I would use the IF clause to do this for example if Year=Year[order date] then sum[total charge] however this summed all of the charges. I think I need something like a where clause. Sum Total Charge where year=year[order date]. Has anyone figured out how to do this in File Maker Pro? Any ideas would be very helpful!

[LiveOak]

There are may ways, some with and some without relationships.

Option 1:

Charge (number)

ChargeDate (date) <--- you might already have a date field for each charge

Charge2000 (calculation, number) = If( Year(ChargeDate) = 2000, Charge, 0)

Charge2001 ((calculation, number) = If( Year(ChargeDate) = 2001, Charge, 0)

Total2000 (calculation, number) = Sum(Charge2000)

Total2001 (calculation, number) = Sum(Charge2001)

This is a little clunky, as you must add fields for each year. Not a professional FM solutions (but does demonstrate a technique).

Option 2:

Create the fields:

Charge

ChargeDate

YearOfCharge (calculation, number) = Year(ChargeDate)

TotalCharge (summary, Total of Charge)

If you create a layout with a Subsummary Part (when sorted by YearOfCharge), place the Total Charge in the Subsummary part of the layout, sort by YearOfCharge, and enter Preview Mode or Print, total will appear for each year of records in the file.

Option 3 (using relationships):

Create the fields:

Charge

ChargeDate

YearOfCharge (calculation, number, indexed) = Year(ChargeDate)

Create a self relationship (from your file to the same file) with YearOfCharge matching YearOfCharge. Call the relationship "YearRel".

TotalForYear (calculation) = Sum(YearRel::Charge)

TotalForYear will display the total for the year of the record you are on. For instance, if you are on a January 15, 2000 record, TotalForYear will display the total for the year 2000. If you are a record for August 23, 2001, TotalForYear will display the total charges for 2001. This happens because each of the records for a year is related to all the other records for the same year.

Option 4 (almost like 3):

Create a field:

gYear (global, number)

Create the same RelYear relationship, but put gYear on the left side. When you enter a year into this global field, the total for that year will be displayed in TotalForYear.

-bd