calc total for charges after a date

[Andrew5]

I have a portal in a child table that lists charges and dates. There is a field in the parent table that lists the end of the project. How would I define a calculated field that will give me the total charges AFTER the end of project? Thanks.

[comment]

There are (at least) two ways to do this:

1.

Define another relationship to a new occurrence of the child table as:

Parent::ParentID = Child 2::ParentID

AND

Parent::EndDate < Child 2::Date

Then define a calculation field in Parent (result is Number) =

Sum ( Child 2::Charge )

2.

Define a calculation field in Child, cLateCharge (result is Number) =

Case ( Date > Parent::EndDate ; Charge )

Then define a calculation field in Parent (result is Number) =

Sum ( Child::cLateCharge )

[Andrew5]

I think I will try the second method you have, but I need you to be more explicit. Here are simplified versions of my tables

CLIENT TABLE

key_client

name

address

project_end_date

charges_calculated

CHARGES TABLE

key_charge

date_of_charge

charge_amount

f_key_client

I see I can use "case" but what EXACTLY would that line be? I just dropped 20 years of programming for Filemaker!

Also, what book would anyone recommend to a former programmer to learn scripting and calculation tables?

[comment]

In CHARGES, define a new calculation field named cLateCharge, and enter this formula =

Case ( date_of_charge > CLIENT::project_end_date ; charge_amount )

This field returns the charge amount only if the charge date is greater than project end date - otherwise it's empty.

In CLIENT, add a new calculation field =

Sum ( CHARGES::cLateCharge )

This sums the previously defined conditional charge field. Since Filemaker doesn't have a SUMIF() function, we are forced to do this in two steps.

---

Incidentally, your structure seems to indicate that Client and Project are the same. If a client can have multiple projects, you should have THREE tables, and the project end date would be an attribute of a Project, not Client.

[Andrew5]

THANKS! IT WORKS PERFECTLY! I wish I had tossed out using regular programming years ago. Filemaker is an amazing program.