4 Replies Latest reply on Feb 5, 2013 3:36 PM by srbridges

    Calculated field gives inconsistent result

    srbridges

      Title

      Calculated field gives inconsistent result

      Post

            

           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";
           "T"
           )
            
           "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.
            

      RelationshipGraph.png

        • 1. Re: Calculated field gives inconsistent result
          philmodjunk

               Is this a field of type calcualtion that returns text or is it a text field with an auto-entered calculation?

          • 2. Re: Calculated field gives inconsistent result
            srbridges

                 It is a calculation field that returns text

            • 3. Re: Calculated field gives inconsistent result
              philmodjunk

                   "zmember" is a global field in the CONTACTS table with "Member" as its calculated value.

                   Does that make zmember a text field with global storage specified or is it a calculation field with global storage specified in Storage Options?

                   If it is the latter--a calculation field, I'd go to storage options and remove this option to make it a stored, indexed field to see if it makes any difference. (If this field always holds the same value in all records, it does not need to have global or even unstored specified as a storage option and this might affect how the values update in some circumstances.)

                   If that does not resolve the issue, can you shed any more light on the "sometimes" in which it does not show the correct value? Any pattern in what you were doing just before the field failed to update. Were you editing data in the contacts or the affiliations table? and on what layout did you have this field where you saw that it failed to update?

              • 4. Re: Calculated field gives inconsistent result
                srbridges

                     "zmember" is a calculation field. I have taken your advice and made it a stored indexed value. So far, I have not noticed any "malfunctions".

                     If the problem persists I will make note of when/where/how and post again. The time it did not work today was when I first opened the database - the membership types were wrong. Then, after opening and closing the Define database dialog, they were correct. 

                     Thank you very much for your prompt and thorough reply.