Please ignore the pdf page info slipped into the middle of the script…

From: FileMaker Pro Discussions [mailto:[log in to unmask]] On Behalf Of Geoff Graham
Sent: Saturday, January 26, 2013 12:29 PM
To: [log in to unmask]
Subject: Re: parsing a date from a text field

# 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