restore "19xx" dates to "20xx"? (time-urgent)

[Tony Tweedale]

Because I have to leave the country Saturday for an emergency operation on my father, it'd be great if someone familiar w/ FMPro 4.3 can help me Thu or Fri, June 29 & 30th.

I'm a fairly novice FM user, entering member contributions to our small non-profit into FM databases. FM converted the year in our datefields back one hundred years, e.g. 2004 became 1904.

This happened when I invented a procedure to move several hundred members who haven't contributed for a while to a FM database of such members (with identical fields). To do so I first found the date range I wanted in that field and exported those records in FM format, then imported them into a pre-existing database of such non-renewing members (created before my or my predecessor's time).

To finish the separation, I then found & exported our other (ie current) members in the same manner into a new FM database w/ the same fields, and that became my new database of current members.

I believe in both export operations that FM made the unwanted conversion of the year, perhaps because my FMPro 4.3 is a version created before 2000.

I just discovered the problem, and FM Tech. Support is unrained in anything earlier than v. 6, and suggested I contact you FMforums.

To fix, I tried exporting this date field for import into Excel to do a 'find & replace text' function, then importing the fixed data back in. But I can only get the data to export in the format: '25-Nov-05' rather than in a format with all four year numbers. That's whether I export with "use current format" or not; and no matter how I display the data for this date field in Layout mode.

I'm very unfamilier with this complicated FMforums site, but will try to check for responses if they aren't sent automatically to my email address. Or contact me at 406-721-5420 or [email protected] if you have a suggestio--but by Friday. Thanks!

--

[Lee Smith]

Hi Tony, and welcome to the forum.

Without seeing the actual data, or you files, I'll make some assumptions here, so correct me if I'm not understanding your problem.

FYI, if you enter a 01/01/04, manually, FM will assume you mean 2004. However, if you import a date fo 01/01/04, FileMaker will revert it to 01/01/1904.

The best way to fix this problem is to catch it at its source if possible, and export the year as four digits.

The next best thing is to clean it up proir to import using a text with Grep Pattern Find and Replace ability. I use BBedit Lite in OS 9, and TextWrangler in OS 10. The Grep shown below will find either 01/01/04, or 1/1/04 and replace it with the same pattern, except changing the year to 2004.

FIND:

(d+/d+)(/04)

REPLACE:

1/2004

Result is

01/01/2004

Always do this with a copy, and not the original.

If the data is already in FileMaker, then it becomes a little more tricky.

Again, always make a backup of your file before attempting this. The procedure that I'm recommending is not reversible (i.e., it is a permanent change to the records, if you error, you will need to start over with another copy.

with that said, the easiest way to change this is to use the Replace Function on the found. Main Menu >> Records >> Specify.

To start with, I would change the Field from Date to Text Temporally. You do this in the Define Fields. Next, put your cursor in the date field, then you can either follow the path I gave above, or use the key combination [color:blue]Command =. Next hit the button [color:red]Specify, and then enter this calculation.

Substitute (Date, 1904, 2004)

After you are done with the changes, be sure to go back into Define Fields and change the field back to a date. When you make this change, will get an warning, about Text Lenght in a Date Field, but just click [color:red]Okay.

HTH

Lee

[comment]

I believe Filemaker - even version 4 - exports dates with full 4 digits year. I suspect somewhere along the line your dates became text. You should check if what you have now are valid dates. First, make sure your field type is defined as Date. Then go into Find mode, and type a question mark into your date field. Any found records have invalid dates.

[Tony Tweedale]

Thank you, responders. In reply to others, the field in question is still a date field.

And the bad 19xx data is already in the database. So I'd like to follow Lee's final suggested fix, and have made a copy to experiment on and coverted the field to a text field.

But this command path:

"Main Menu >> Records >> Specify" .

...doesn't exist in my version.

...and am in in Find mode if I instead try

"Next, put your cursor in the date field, then you can either follow the path I gave above, or use the key combination Command =. Next hit the button Specify, and then enter this calculation..."

(I assume on an Apple I use: "open-apple key-=" instead of 'command =').

Thanks! -t.t.

[Tony Tweedale]

Sorry I garbled my question in my last post. I meant to ask:

...and if I instead try your suggestion:

"Next, put your cursor in the date field, then you can either follow the path I gave above, or use the key combination Command =. Next hit the button Specify, and then enter this calculation..."

...do I do place the cursor in the filed while in Find mode? I don't have a "specify" button, unless it comes up in a dialogue box in this procedure.

Perhaps FMPro v.4.3 doesn't have these commands?

[comment]

In version 4, the Replace command is under Mode (you need to be in Browse mode for this). However, if your (by now text) data is in the format of '25-Nov-05', this can get quite tricky. It's a pity you did not report whether the dates were valid before you converted them to text, as this could make quite a difference regarding the best way to fix them.

[Tony Tweedale]

Thanks. I only converted it to a text field in my test database. By formatting this date field in Layout mode, I can display the date anyway I want before converting it to text. I assume you're thinking I should display it as, e.g., '11/24/1903'?

[comment]

If you can get them to display as either '25-Nov-05' or '11/25/1905' or whatever you choose in Layout mode, that's an indication that the dates are PROBABLY valid. They ARE valid if changing the format does not affect the actual date - only its display.

If the dates are valid, you can find any records where the date is less than say 1/1/1950, and replace the date with a calculated result =

Date ( Month ( yourDatefield ) , Day ( yourDatefield ) , Year ( yourDatefield ) + 100 )

[Lee Smith]

Hi Tony,

Sorry for not giving the full path via the menu. It should have read Main Menu >> Records >> Replace Contents >> and then you will get the Menu to click on specify. I also didn't mean to leave you in the lurch, I have been out of the office all day.

Thank you [color:blue]comment for following up, and providing the right answers.

Lee

Edited June 29, 2006 by Guest

[Tony Tweedale]

Others and especially Lee: Thanks for your prompt repsonses. Lee's complex specified formula in the 'Replace' command WORKED PERFECTLY in my test database. Thank you!