Hi,

I have a whole stack of information that gets uploaded to an ftp site which is really strict on its data format.

I have to display in filemaker the amount incl $ and thousand separators so it's easy to read, but when i export it, it is without formatting.

Similarly, date formats must be with dd/mm/yyyy

I have formatted the fields, but my problem is i have some users who ignore what i say and are causing issues such as:

dd.mm.yyyy - since filemaker recognises this as a date, it allows it. I format it on screen to it appears as dd/mm/yyyy but because i can't export with formatting it resorts back to .

500,000.00 - filemaker recognises this as a correct amount, but I need it to be entered in as 50000.00

Is there a way around this? I know i can be strict with 4 year date, and other forms of display formatting, but is there a control for when users use . or , if technically the format (whichever way) is a correct one.

Hope that makes sense!

Cheers

Create a calculated text field that generated the date text in the format you require. Something like

Day(datefield) & "/" & Month(datefield) & "/" & Year(Datefield)

will probably do. If the date format needs leading zeros, try

Right("00" & Day(datefield), 2) etc

Hi kiwiora, I believe that Vaughan is suggesting you export your calc fields instead of the field data. It's easier to control a calculation for export than to control User data-entry and I agree. However, there are times in which the data should be altered to suit Owner needs. Techniques are around to accomplish it. It depends upon how important it is to you.

One 'series' of Custom Date Validation Techniques on Ray's NighWing website covers manipulation of the field data (ie, converts any type of date entered into a proper 'legal' FM date), including dd.mm.yyyy. It offers protection for dates entered in global, Custom Dialog and Value Lists, as well as accepting '.' '-' and 2-digit year entries and more. It's the best example of handling dates that I've seen. The overall principle of preconditioning text dynamically is quite interesting. Ray also has some great text formatting demos.

Another terribly interesting piece of work can be found on Andy Gaunt's FusionDZine Website. Check out the downloads section - Central Florida FileMaker Developer Group CFL FMG December presentation files ForMAT Your dATa!!!. He suggests a dynamic approach to field formatting which provides User-forgiving techniques but also provides the User and/or Management a method to control the output by clicking a checkbox of the formatting attributes desired on each field, or dynamically. I'd kill to peek inside it. The rest of his site rocks too!

"I believe that Vaughan is suggesting you export your calc fields instead of the field data."

Oh yeah. Thanks MoonShadow, I forgot to say that.

Ok, I've found a really really simple way to fix this. Or am i missing something?

(Substitute({fieldname},".","/"))