# script to find all the dates including 4 digit years and months as text in a text field
# filename and table names are "parse"
# field being read is "In"
# date field "Date" will hold the last date in the text
# text field "DateList" will list all dates in the text
Go to Record/Request/Page
[ First ]
# let's run through the found set of records
Loop
# reset the output field from the last run
Set Field [ parse::DateList; "" ]
# start with this word
Set Variable [ $x; Value:1 ]
# get a copy of our input that we will be free to murder later
Set Variable [ $in; Value:parse::In ]
# how many words do we need to look at?
Set Variable [ $y; Value:WordCount ( $in ) ]
# we're going to loop through each word in the text
Loop
# word ..
Set Variable [ $word; Value:MiddleWords ( $in; $x; 1) ]
# could this word be the year we are looking for?
If [ (GetAsNumber ( $word ) = $word) and ( Length ( $word ) = 4) and ( GetAsNumber ( $word ) > 1900 ) and ( GetAsNumber ( $word )
< 2050 ) ]
# found a 4 digit year
Set Variable [ $year; Value: MiddleWords ( $in; $x; 1) ]
# find our year in the text
Set Variable [ $endpoint; Value:Position ( $in; $year; 1; 1) + 4 ]
# and excise it
Set Variable [ $dateslug; Value:RightWords ( Left ( $in; $endpoint ) ; 3 ) ]
# let's build a date
Set Variable [ $day; Value:GetAsNumber(MiddleWords($dateslug; 2; 1)) ]
Set Variable [ $month; Value:Let ( x = LeftWords($dateslug; 1 );
Case(
PatternCount ( x ; "Jan" ) ; 1 ;
PatternCount ( x ; "Feb" ) ; 2 ;
PatternCount ( x ; "Mar" ) ; 3 ;
PatternCount ( x ; "Apr" ) ; 4 ;
PatternCount ( x ; "May" ) ; 5 ;
PatternCount ( x ; "Jun" ) ; 6 ;
PatternCount ( x ; "Jul" ) ; 7 ;
PatternCount ( x ; "Aug" ) ; 8 ;
PatternCount ( x ; "Sep" ) ; 9 ;
PatternCount ( x ; "Oct" ) ; 10 ;
PatternCount ( x ; "Nov" ) ;11 ;
PatternCount ( x ; "Dec" ) ;12 ;
GetAsNumber ( x )
)
) ]
Set Variable [ $year; Value:GetAsNumber(RightWords($dateslug; 1)) ]
January 26, 2013 12:24:02 parse.fp7 - go -1-
go
# your date, sir...
Set Field [ parse::Date; Date ( $month; $day; $year) ]
# append that date to the end of the list
Set Field [ parse::DateList; parse::DateList & GetAsText ( parse::Date ) & "¶" ]
# chop off the text up to where we just read
Set Variable [ $in; Value:Right( $in; Length ( $in ) - $endpoint ) ]
# how many more words do we need to look at?
Set Variable [ $y; Value:WordCount ( $in ) ]
# since we chopped off the already read part of the text, we need to reset the word we were looking at
Set Variable [ $x; Value:0 ]
Else If [ IsValid ( GetAsDate ( $word ) ) ]
# just in case they entered a real date, our word search for years would have missed it
Set Field [ parse::Date; GetAsDate ( $word ) ]
Set Field [ parse::DateList; parse::DateList & GetAsText ( parse::Date ) & "¶" ]
# and again we have to reset our variables so we can move on
Set Variable [ $endpoint; Value:Position ( $in; $word; 1; 1) + Length ( $word ) ]
Set Variable [ $in; Value:Right( $in; Length ( $in ) - $endpoint ) ]
Set Variable [ $y; Value:WordCount ( $in ) ]
Set Variable [ $x; Value:0 ]
End If
# next word please
Set Variable [ $x; Value:$x + 1 ]
# are we out of words?
Exit Loop If [ $x > $y ]
End Loop
# next record please
Go to Record/Request/Page
[ Next; Exit after last ]
End Loop


Geoff

From: FileMaker Pro Discussions [mailto:[log in to unmask]] On Behalf Of Nick Adams
Sent: Friday, January 25, 2013 10:33 AM
To: [log in to unmask]
Subject: parsing a date from a text field

Would someone be so kind as to give me a hand.
I am an amateur and only use FMP for my own projects.

I am trying to parse the date out of a text field.

The text field contains something like this:
some text, Nov 10, 2006 - some more text

The date is between a comma and a dash.

I have been trying various combinations of text functions but cannot get anything meaningful.


Nick

Karystos, Greece