trouble when importing dates

[bennygsr]

I am not sure if this is the right section to put it in, however here goes anyway....

I have just using FM for a patient record database for my podiatry clinic. When I try to import patient details from my MYOB, everything works fine except the BIRTHDATE, or D.O.B.

My format in MYOB for birthdates is in day/month/ 2 digit year (eg. 1965 = 65)

My problem is that when I get anything older than 1940 (eg. 1/1/35), the date function in FM assumes that 35 is 2035, not 1935. Is there anyway I can get the date function to change its way of thinking ?

I don't like the idea of putting the 19 infront of all of my 2000 birthdates.

Any help is kindly appreciated. Thanks

[Lee Smith]

Hi Benny,

As you might suspect, this topic has come up in the past. I just did a search for "1900" and received 75 hits. I'm sure if you use a variation on what you need the number would be higher.

Anyway, you can accomplish this with a new calculation field or by replacing the data with a script. Here is a Thread that I thought was a good approach. Click here to go

HTH

Lee

[LaRetta]

I dunno. I simply don't trust 36525. I have the funny feeling that if it is used, your month/days could end up changing and not just the year. I could be wrong, I frequently am.

I would tend to use FM's internal date calculator because of its accuracy. Something like:

If(Year(date) > (Year(Get(CurrentDate)) - 50); Date(Month(date); Day(date); Year(date) - 100); date)

With a calculation such as this, you don't need to perform a find to isolate the older records. Just import and loop through them all. It will only change the correct ones. Even better ... since you use vs. 7, you can add this as an Auto-Enter (Replace) right on your new date import field; no loop required.

LaRetta

[comment]

I agree. IMHO it's good practice to always prefer a general formula that will work with any data, over a shortcut that just happens to work in the current range.

The general formula to add n years to a date:

Date ( Month ( date ) ; Day ( date ) ; Year ( date ) + n )

will work correctly with ANY n and ANY date (within FM calendar range).

[LaRetta]

Ummm, well the last bit I suggested about adding it as an Auto-Enter (Replace)? Don't do it if Users are also typing the date directly into it from within FM.

Since FM adds the default year (and a User types 1/1), the above calculation (using Auto-Enter Replace) would change the date to 1905.

I think it would be wiser to use a loop (on your import set) after all, if that date is shared with regular User entry; or adjust that date calculation further.

LaRetta

[LaRetta]

Well, I see no reason in testing the age of the date in this instance. If it displays as greater than the current date, it needs converted otherwise it doesn't. So ...

If(date > Get(CurrentDate); Date(Month(date); Day(date); Year(date) - 100) ; date)

... should work and allow Auto-Enter (Replace) to function correctly on that date field; and also share it with Users even if they just type 1/1.

If the old date is 1/1/05, what does FM do with it? It will import it as 1/1/2005!! What did your old system mean it to be? It could be 2005 or 1905! I see a problem either direction, I think.

Unless your date is in the 1800's (in which case how would you know?), it should work fine. If the year is supposed to be 1889 and not 1989, your old system would still list 89 and FM would be unable to mind-read.

LaRetta

[bennygsr]

Thank you very much for the quick replies.

However, I am having some difficulty interpreting what it is exactly what I have to do.

For example. I have data of 1000 clients. Due to the importing of my MYOB patient cards, Filemaker registers some clients as having DOB in the future. For example. Joe Bloggs actual DOB is Jan 1 1925.

MYOB has this as 1/1/25. Now, this imported into FM, registers as: 1/1/2025.

Now apparantley I can enter a script that will "automatically" change all of the above described circumstances. Eg. 1/1/2025 becomes 1/1/1925.

I hope you can forgive my naivety, however I do not understand exactly where I should put the script, and how it works.

I hope someone may be able to help me.

Thanks again

[LaRetta]

Hi Bennygsf,

You need to familiarize yourself with ScriptMaker. Create a new script called 'Import MYOB' and it would look something like:

Go To Layout [ Your Client Table layout ]

Import [ ... specify your import criteria & key mapping here ]

Loop

If [ Year(DOB) > (Year(Get(CurrentDate)) - 50) ]

Set Field [ DOB; Date(Month(DOB); Day(DOB); Year(DOB) - 100)

Go To Record/Request/Page [ Exit After Last; Next ]

End Loop

Back up first.

This will loop through your newly imported record set and only change those with a DOB greater than the (current date - 50), ie, 2025 and decrease their age by 100 years.

Now ... if only that could work on real people and not just numbers!!! Well, I guess FM isn't perfect. Yet.

I (or others) will be happy to assist further if need be.

LaRetta

[comment]

A footnote to LaRetta's post:

Dates that do not specify the century are inherently ambiguous. As LaRetta pointed out, FileMaker cannot read minds. Neither can she. All she can do (and has done) is substitute FileMaker's assumptions with her own and hope for better results.

Ultimately, is it up to you to fine-tune the formula for your patients population. If there are no people over 100 years old, it can be simply:

If (

Year ( date ) < Year ( Get ( CurrentDate ) ) - 99 ;

Date ( Month ( date ) ; Day ( date ) ; Year( date ) + 100 ) ;

date

)

But if it is possible for a patient to be over the age of 100, and OTOH there can be also patients that are small children, then ANY assumption you make will fail occasionally.