[jas.]

Can any of you tell me if this formula is correct or not? It seems to be working, but I have had formulas that seemed to work before, but were actually incorrect.

I'm trying to report on the membership as of the gift date. So I queried for the gifts I wanted, then did a gift export with the gift into, the constituent info, and then four membership transactions. What I want the formula to do is cycle through the membership transactions in reverse date order, find the first one that isn't "Dropped" where the transaction date is before the gift date.

So if the gift date is 9/1/06, and there is a membership transaction of Renew Upgrade - Household 12/1/06, and another transaction of Renew Same - Individual on 12/1/05 -- I want it to pull Individual.

So I created this formula:

WhileReadingRecords;
if ({Gf.Gf_Date} > {GfCnMem_1Hst_1.GfCnMem_1Hst_1_TransDate}
    and {GfCnMem_1Hst_1.GfCnMem_1Hst_1_Type} <> "Dropped")
then stringvar memcat := {GfCnMem_1Hst_1.GfCnMem_1Hst_1_Category_description}
else stringvar memcat := "No Membership";

And then I grouped on it -- and it seems to be working, except for the fact that "No Membership" isn't showing up when there's a null in the transaction date (i.e. people who actually have no membership).

But is there something else I'm missing? Anyone?

[DrewAllen]

If a field can be null, you need to check for that before using it in any comparisons or your formula will return NULL. Your first test needs to be IsNull({Gf.Gf_Date}).

It's not clear whether you did one instance of the membership with four records or four instances with one record each. If it's one instance with four records, your formula will not work. Grouping can only be done on Context Free formulas, but you need to compare multiple records in order to determine the correct membership. Comparing multiple records is a Context Sensitive operation.

Drew

[jas.]

thanks Drew. that gives me a lot to look up.

I did indeed export one instance of the membership, with four records--and I'm not sure how to go about it the other way--so I guess it's no good.

Back to the drawing board.