Calculated field gives inconsistent result
In my database for a non-profit I have a CONTACTS table and an AFFLIATION table. A person has one "Contact" record, but can have many "affiliation" records- one for each year of affiliation. I have defined a field called "MembershipType" that calculates a value for each Affiliation record using this logic:
If the contact has declined to renew his memberhip this year = "X"
If the contact's affilation is something other than member (like Sponsor, Friend, etc.) = blank
If this is the first year the contact has joined as a member = "N" for new
If the contact was a member last year and has joined for this year = "R"
Otherwise the contact has been a member some year in the past, but not last year, then the membership type = "T"
Here is the field definition (The calculation result is unstored):
Affiliation = "Declined"; "X";
Affiliation ≠ "Member"; "";
Min (contacts_AFFILIATION__membersOnly::MembershipYr) = MembershipYr; "N";
PatternCount ( List ( contacts_AFFILIATION__membersOnly::MembershipYr) ; (MembershipYr - 1 )) > 0; "R";
"MembershipYr" and "Affiliation" are fields in the AFFLIATIONS table. In the relationship graph, "zmember" is a global field in the CONTACTS table with "Member" as its calculated value.
My question: Sometimes this calcuation gives the wrong result, like a "T" when it should be "N". If I open the Define Database dialog, click on the field definition, and close the window, the correct result appears. Why is it doing this, and what can I do to fix it?