4 Replies Latest reply on Oct 30, 2009 4:13 PM by philmodjunk

    Calculation Should Be Blank But Queries As Though It Has Value

    dataWolf

      Title

      Calculation Should Be Blank But Queries As Though It Has Value

      Post

      I make a field that calculates integrity problems with the record. Then, I search for any records that have something in the error field. All the records that have data in the email field come up as having an error, though there is no text in the error calculation field when displayerd.

       

      I've run into this problem before and can't quite identify what is going on.

       

      It is of type text, unstored, self-references, no repitititions. I am using FMP9

       

      Once again, my field "Error" is a case statement, the last field is "", when i do a find with * in this field, it finds all records that have text in a particular text field, but when I examine that calc field there is nothing in there. So why is the find behaving as though there is text in this field when there isn't anything in the results.

        

      Thanks for any help! 

        • 1. Re: Calculation Should Be Blank But Queries As Though It Has Value
          ninja
            

          Howdy datawolf,

           

          Have you considered what the "@" might be doing in your case statement?  Email add's would all have the symbol and it is a special character for some FM functions (like find for example).

           

          Try taking the @ out of some of the emails and see if they disappear from your error list.  If you confirm that that is the issue, post up your case statement and we'll see if we can't work around the problem.

          • 2. Re: Calculation Should Be Blank But Queries As Though It Has Value
            philmodjunk
               If that doesn't work, you can post the actual calculation here so others can take a look at it.
            • 3. Re: Calculation Should Be Blank But Queries As Though It Has Value
              dataWolf
                

              Case (EMAIL = ""; "";

              EMAIL > "" and not (EmailAddedDate > Date(1;1;1)); "Email has not been added."; 

              EMAIL > "" and not PatternCount (EMAIL;"@"); "Email missing ampersand";

              EMAIL > "" and Count (Prospects by emails::EMAIL) > 1; "Duplicate Email";

              LAST NAME > "" and Count (Prospects by Name::LAST NAME) > 1 and not (notaDupe > ""); "Duplicate LastName";

              "")

               

              okay thanks!

              yeah, looks like i am trying to handle the ampersand so who knows what it is really testing. but i can't see either cause it results in blank :( 

              • 4. Re: Calculation Should Be Blank But Queries As Though It Has Value
                philmodjunk
                  

                Actually & is called an Ampersand, not that it matters here :smileywink:

                 

                There's some tricks you can use to simplify your case statement:

                 

                Case (IsEmpty(Email) ; "";

                         IsEmpty (EmailAddedDate); "Email has not been added."; 

                         not PatternCount (EMAIL;"@"); "Email missing ampersand";

                         Count (Prospects by emails::EMAIL) > 1; "Duplicate Email";

                         Not IsEmpty ( LAST NAME ) and Count (Prospects by Name::LAST NAME) > 1 and IsEmpty (notaDupe); "Duplicate LastName";

                         "")

                 

                The @ shouldn't be a problem here. That's an issue if you try to find fields containing that special symbol, but that's not what you are doing here.

                 

                "when i do a find with * in this field, it finds all records that have text in a particular text field, but when I examine that calc field there is nothing in there."

                That really sounds like your calculation's result type is set to "number" instead of "text". What happens when you click in the calc field? Does text appear?