AnsweredAssumed Answered

Calculated field gives inconsistent result

Question asked by srbridges on Feb 5, 2013
Latest reply on Feb 5, 2013 by srbridges


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):
     Case (
     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?
     Thank you.