A READER ASKS: Extracting FileMaker Year Data

From Dwayne Wright PMP
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

I imported data without thinking (go figure). I created a field (“Date”) and imported data (“1st Qtr. 08”). Unfortunately, it made it impossible to organize chronologically. I now created “Day” “Month” “Year” fields.

Is it possible to create a script so that if any data under the “Day” field that equals “1st Qtr. 08”, upon running the script, data will be entered into the “Year” field as “2008”?

Not a problem and I’m sure there are a dozen different ways to handle this. This is what I used ...

If [ IsEmpty(temp::Year) ]

Replace Field Contents [ temp::Year;

Replace with calculation:

PatternCount ( temp::Date ; "Qtr" ) ≥ 1; "20" & Right(temp::Date; 2);
Year ( GetAsDate ( temp::Date ) )) ]

End If

I put in an IF statement so that we don’t replace year data if it is already there. Then used the Replace command to update all the records in the found set. The replace command uses a calculated value because I wanted to branch the replace if the data had a “QTR” value or a typical date.

PatternCount ( temp::Date ; "Qtr" ) ≥ 1; "20" & Right(temp::Date; 2)

PatternCount is used to see if a pattern appears in a piece of text. Here we are saying if QTR appears just once, take the right two characters and place them behind “20” literal text.

If the text pattern of “Qtr” does not appear, I’m assuming a date value is there and I’m extracting the Year information by using the Year function.
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2009 - Dwayne Wright - dwaynewright.com

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.