Multiple find conditions in a calc

[ljm]

Hi -

I am trying to set a caluclated temporary number field equal to an amount of a donation if the donation meets a number of criteria

date>5/31/05 and <10/31/06

amount>249

status>new

I am using an if statement with Criteria #1 and Criteria #2 etc. but can't get all the conditions evaluated, particularly the date. I think I am missing something basic. Does someone have a suggestion?

Thanks

[Ender]

Can you clarify? Are these criteria meant for Find Mode, a scripted Set Field[], or some sort of calc? Can you show exactly what the calc/script steps are that you are having trouble with?

[ljm]

This is a calculated field, which I would like to summarize, but I can't get all the criteria to work. Using find they all work together. In find I can express the date range as 5/31/05..10/31/06, but don't seem to be getting the calc to read the whole expression, particularly the date. Maybe the If can't have more than one field being evaluated.

Here is one example, there are a series of these evaluations.

If(JT Returning Donors="Full Value" and Date>5/31/2005;Amount;"" )

Edited October 31, 2005 by Guest

[Ender]

You have to use the correct syntax for a calc:

If(JT Returning Donors="Full Value" and Date>date(5;31;2005); Amount;"")

or simplified a bit:

Case(JT Returning Donors="Full Value" and Date>date(5;31;2005); Amount)

[ljm]

Thanks!

[ljm]

Would you mind helping me once more, it is related to the above.

I am trying to create different summaries of giving over a couple of different time spans, Calendar year, fiscal year, event seasons etc. but I can't figure out how to create summaries that are conditional.

Ideally there would be four year comparison donation summaries. The conditions are all involving date ranges and usually one or two other criteria that are basic for example:

if (date>date(5;31;2004) and date

I think I can't use Sum this way, is there something you would suggest?

If I use a temporary summary field and produce a report with a sub-summary by ID and I preselect these records I can then get the summary and write it to a number field to hold for other finds.

[Ender]

It sounds like you are trying to do this all within one table. It would be much easier to use a separate table to hold those totals for each particular date range.

For this to work you'd relate a "SubTotal" table to your current Donation table by the Date Range (and possibly Status or Type)

Donations <-> SubTotal

Donation::Date >= SubTotal::DateRangeStart

AND Donation::Date <= SubTotal::DateRangeEnd

AND Donation::Status = SubTotal::Status

Then in SubTotal, add a calc to get the total for that range (for that Status):

Total (calculation, number result) = Sum(Donation::Amount)

In this way, you can have a total for each range (for each Status,) as records in the SubTotal table. You can then view these records in a List View or in a portal.

If you have thousands of Donation records, then this calc may be too slow to have it calculate on the fly. In this case, make the Total field a regular number field, and use a script to set its value using a Set Field[]:

Set Field [ Total ; Sum(Donation::Amount) ]

[ljm]

Thank you again, you are really good with this. Your solution makes sense, but I haven't totally made it work.

Is this correct?

The subtotal table will have a one or more records for each id that contains the appropriate subtotal.

For example the

Mr. Smith's subtotals:

1998 giving year status x

1998 giving year status y

1999 giving year status x

1999 giving year staus y

etc.

0r does the subtotal table actually contain donation records and totals?

I think it should be the former, but have not totally made that work yet.

What I screwed around with was making a bunch of separate dbs which had only the records for the subtotal groupings, ran the totals in each of them related them back to the master file then read them into the Donor record. It worked in the short run, but obviously not a solution.

[Ender]

Sure, one record per Donor per Date Range per Status. So adding the Donor into the picture, the relationship would look like:

Donations <-> SubTotal

Donation::DonorID = SubTotal::DonorID

AND Donation::Date >= SubTotal::DateRangeStart

AND Donation::Date <= SubTotal::DateRangeEnd

AND Donation::Status = SubTotal::Status