YTD giving calculation

[laker_42]

We need some help trying to figure out how to get our year to date calculation field working properly. We have two databases (accounts & transactions). We have created a YTD giving calculated field in the accounts db and we want it to sum the values in the transactions db for giving for the current year. Here is the calculation that we currently have:

If(Year(Status( CurrentDate)) = Year(Transactions::DateAmountReceived), Sum(Transactions::Gift),0)

This works if we have only the current year giving transactions in the db. If we put in the previous years giving transactions it gives us a 0. We have been working on this quite a bit but we don't know what to try next. Any ideas what we are doing wrong? Thanks for any input you can give.

John

[danjacoby]

You're skipping a step. First, in the Transactions file create a field that returns the transaction if it's the current year, then create a field that returns the sum of the first field.

If(Year(DateAmountReceived) = Year(Status(CurrentDate), Amount, TextToNum(""))

then in the Accounts file

Sum(Transactions::FirstField)

N.B.: This hasn't been tested, so there may be minor changes, but essentially this is the idea.

[laker_42]

Thanks for the help!

John