[SOLVED] Working with Dates

[North2Alaska]

I've been reading a lot about dates and date formats.  I believe at this time date formatting does not work in FMT.  So, I am trying to create a work around to a problem I am having.  I have a relationship built on the Year of a birthDate.  Of course, simply getting

Code:

Year ( Birthdate )

does not work in FMT because it does not support the Year function.  OK, I can deal with that by forcing Year to be dealt with as a string by using

Code:

Left ( Birthdate ; 4 )

.  This works in FM but does not work in FMT as FMT's default for dates is YYYY/MM/DD instead of FM's default of MM/DD/YYYY (this could be a localized issue.  I'm in the US.)

I have to come up with a solution that works both in FM and FMT.  I thought I might be able to use an IF statement to figure out which function to use, Year or Left depending on if it is FM or FMT.  I just can't seem to figure out a solution.   Suggestions?

[rrenfrow]

What about changing the date format in the FM file?  I know it is not "American" but it would make them compatible.  Format the field as a date, then use the custom formatting choices to format it as YYYY/MM/DD.

It's working backward, but, hey, it should work.

Raylene

[North2Alaska]

I don't think it is a formatting issue from the individual layout perspective.  FM stores dates as a number and then displays them by a default format.  I don't find anyplace to set a default format for a data globally for the whole database.  I can set how the layout will display the date, but that doesn't help when FMT uses a different default format.  :-(

[rrenfrow]

OK.  You could add one more data entry field for year.  Not great, but it would give you the year data.

Stabbing in the dark here, but wishing I could help.

Raylene

[North2Alaska]

That is really the only option at the moment.  So, what I have decided to do is create a calculated field that is = 2010 with the hope that by the end of the year we will have the date manipulation functions and I can replace my hard code value with Year ( Birthdate ). 

Thanks all for the attempts... 

[rrenfrow]

One more wild and crazy idea.  What if you had two fields for year each calculated appropriately for its use....one you use for FMP and one you use on the layouts for FMT.

Raylene

[North2Alaska]

In some cases this may work.  My problem is I'm using the results as a join and as such two columns would not work.

[rrenfrow]

OK.  I'm giving up and leaving you alone.  I'm not helping.  Sorry!!!  I wanted to.  You have a really complex solution!  Hope you figure something out.

Raylene

[North2Alaska]

Thanks so much for your attempts.  Yes, it is somewhat complex and gets pushed over the top when working with FMT and it's subset of FM functionality.

What I ended up doing is hard coding a value of "2010".  I'm betting the farm that FMT will have the date Functionality included by the end of the year.

[North2Alaska]

Thanks, this worked...