5 Replies Latest reply on Jan 31, 2012 1:26 AM by leedsman

    Finding 'Dates' Using Wildcards and Full Stops



      Finding 'Dates' Using Wildcards and Full Stops


       I am trying to find data which 'looks like' a date in a text field.

       I can do this when using the Find button by inputting @@.@@.@@ in the text field, and then hitting the 'Perform Find' button.

       However, I cannot replicate the results when setting up a calculation field. I have tried various formats such as "@@.@@.@@" or @@ & "." & @@ & "." & @@ but with no suceess. It is probably very simple, so any help much appreciated. Thanks, Stephen. 

        • 1. Re: Finding 'Dates' Using Wildcards and Full Stops

          What do you mean by "setting up a calculation field"?  Do you mean to set the result as a calculation in a find script step?  So:

          Enter Find Mode

          Set Field (ThatTextField ; "@@.@@.@@" )

          Perform Find.

          If that is the case then it works for me, exactly like that.  The search field is a text field, and the date-like data is xx.xx.xx (not a 4-digit year, for example).

          • 2. Re: Finding 'Dates' Using Wildcards and Full Stops

             What I meant by 'setting up a calculation field' is as follows:

            I have added a field called Temp as a calculation field and the calculation is: Position ( Ord History ; "@@.@@.@@" ;1 ;1 )  

            When Filemaker evaluates the calculation, the field Temp does not find any occurences of the search string "@@.@@.@@" even though I can see they exist.


            • 3. Re: Finding 'Dates' Using Wildcards and Full Stops

              I believe the use of wildcards is a function of the 'find', as you've seen, and is not a function of text calculations.  You could test this by entering the text @@.@@.@@ into the text field and seeing if it finds it then.

              Why do you need to search for a 'date' inside a text field of Order history?

              You could extract the date with the Middle function IF you could guarantee that the 'date' was always in xx.xx.xx format, and there was no other pattern of .xx. in the field, for example.

              But I suspect you should be looking to trap the date independently.

              • 4. Re: Finding 'Dates' Using Wildcards and Full Stops

                A more detailed description of the data inside "ord history" might help us make better examples. Several possible text functions come to mind for this, but all may fail with certain combinations of text and numbers and it would help to know what you want to do next once you have identified the location of this data in the text field.

                If the text is really "messy" in this field, you may have to use either a looping script or recursive custom function to isolate dates inside this field.

                • 5. Re: Finding 'Dates' Using Wildcards and Full Stops

                   The field 'Ord History' does contain 'messy data' and I am trying to tidy this up by breaking the data down into dates and associated narrative. From the comments expresssed in this thread it appears that this is going to be a complicated and time consuming operation. I have decided that the best approach going forward is to leave the existing data in its current format, but any new data added will consist of separate date and narrative fields.

                  Thanks for your advice and suggestions, and I will now consider this thread closed.