9 Replies Latest reply on Oct 11, 2011 2:29 PM by philmodjunk

    Test if Field contains ANY text

    SeanMann

      Title

      Test if Field contains ANY text

      Post

      Hi.

      I'm trying to write a calculation that involves a field that may have text in it. The data is exported from one database into excel and then imported into Filemaker for creating reports. 

      I currently have the field set so that its format is number, however if the data in that field is a mixture of text and numbers, Filemaker automatically removes and non-numerical text and reports that the field contains a number.

      For example:

      The field actually contains

      "This gift will have anotehr $500 payment on 10/10/2012"

      and Filemaker reports this number

      50010102012

      I need to use this field in numerical calculation and this causes issues.

      What I thought to do would be set up validation on the field, but it doesn't seem to work since the data is already imported.

      So then I thought there must be some function I can use to test wether that field contains any text and then if that test returns true I can stop the calculation from occuring.

      Any help is welcome.

      Thanks!

        • 1. Re: Test if Field contains ANY text
          philmodjunk

          I would use a field of type text for this.

          Using the data as it currently exists, You might set up a calculation field that makes this test:

          DataField = GetasText ( Datafield )

          I believe that this field will return 1 ( True ) only if the data in the field is strictly numeric.

          • 2. Re: Test if Field contains ANY text
            DavidJondreau

            Exact ( GetAsNumber ( field ) ; field )

            • 3. Re: Test if Field contains ANY text
              philmodjunk

              Exact makes the comparison case sensitive. That will work here, but you don't need to use that function.

              • 4. Re: Test if Field contains ANY text
                DavidJondreau

                Exact() isn't the only solution, but it works.

                DataField = GetasText ( Datafield ) doesn't work.

                • 5. Re: Test if Field contains ANY text
                  philmodjunk

                  But Exact has nothing to do with the results produced here. The difference lies in the data type of the field and the particular "getas" test used.

                  Our OP specified that the field is of type number. My tests show that both your and my tests will fail if the field is of type number. My orignal thought was that forcing the type back to text (getastext) would work, but it does not.

                  Change the field type to "Text" and then GetasNumber (field ) = Field

                  will work, no need for the Exact function here at all, but it does no harm as it does not affect the results. (Numbers don't have case anyway.)

                  • 6. Re: Test if Field contains ANY text
                    SeanMann

                     Yeah, thanks Phil, but GetAsText just returns the text, essentially telling me that the field contains what it contains which is always true...

                    David has it right, Exact returns a true or false, however I'm still getting some oddities.

                    I'm not sure if it is a logic error on my part or my lack of Filemaker knowledge.

                    Thanks for the speedy help guys!

                    • 7. Re: Test if Field contains ANY text
                      philmodjunk

                      Please read my last post again. I changed to GetasNumber after finding get as text does not work. There are two key details, the field should be changed to type text and Getasnumber(field) should be compared to the text field to identify records that contain non numeric characters.

                      • 8. Re: Test if Field contains ANY text
                        DavidJondreau

                        Phil,

                        The relationship between Exact() and case is a red herring, Exact() simply compares two strings.

                        Why is using the Exact() function worse than comparing with an equals sign? With mine, you don't even have to change the field type ( though the OP probably should ).

                        And I think you need new tests, mine show my solution works (with the field typed as number). Too bad we can't upload a sample file here.

                        David

                        • 9. Re: Test if Field contains ANY text
                          philmodjunk

                          I do need a better test file. Was about to upload a link to a share site where you could download the file when I spotted a typo that was keeping exact from working with the number field. That lead me back to looking at the Help file for Exact as I understood that little function to be strictly a way to make compare text while taking case into account. It turns out that the help file is a bit fuzzier than that as you can use Exact to compare container fields.

                          What's odd is that there is no documentation that says that what you have here will work, but it does.

                          My only objection to using Exact was that it seemed unecessary, but I now withdraw that objection... Embarassed