Jump to content

Exporting Dates


This topic is 3102 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Hi,

I am experiencing another issue when exporting a cvs file.

For some reason ( please see pic ) I am getting my date fields exporting incorrectly.

I am in the US ( MMDDYY ) and My client is in Dubai and they are DDMMYY.  Even thought the user is selecting from the "Calendar" drop down which is MMDDYY and that is the way the date field populates and I validated the entry and I've double checked the "Field" format ... when I import the .csv file into an spreadsheet the dates that have aligned to the "Left" the spread sheet sees them as "Numbers" and the dates that have aligned to the "Right" the spread sheet see them as "Dates" but the "Day" value is the "Month" and the "Month" the "Day" so Aug 6, 2015 ( 6 / 8 / 2015 ) is exported as Jun 8, 2015.  The fields that are being exported by a "NUMBER" aligning to the "Left" I have no idea why.  And the date alleging to the "left" even the cell formatting in the spreadsheet had no effect on them.  Point to what I though was in the FM application solution but all my date fields are set to "DATE" not "NUMBER" ?? strange.

So I went back into the "Export" script step and against my better judgement selected the "Maintain Field Formatting" when exporting and this worked when the User is coming into the solution via the "Application" however, when the user is on "WebDirect" and they download the .csv to their desktop, I am back into my problem with dates exporting incorrectly.

Any suggestion, I am at a loss as to how to correct this.

Does this have anything to do with US - MMDDYY and the customers region being Dubai - DDMMYY

FYI - The field heading you see "Date Inv Post" in the pic is a calculation field.  Meaning I tried pushing the date field of the solution into a calculation  ...

Date ( month ; day ; year )

and it still didn't correct the exporting issue.

Thank you

Tom

 

Screen_Shot_2015-09-03_at_5.12.09_PM.thu

 

Link to comment
Share on other sites

This is quite incomprehensible. Let's start at the beginning:

  1. Are both you and your client using the same file? Or is your client using a file you have developed for them, and you are out of the picture at this point?
  2. What is selected in File > File Options... under Text / Data Entry?
  3. Are you 100% sure that the field in question (I am referring to the original field, where data is being entered) is a Date field?
  4. What is the result that you want, when exporting this field?
Edited by comment
Link to comment
Share on other sites

Comment,

I apologize for the delay, I was unable to respond.

1. We host the file from Canada, I am in the US and my client is in Dubai.

2. I've doubled checked this ... the Filemaker field is set for "DATE".

3. I've double checked this too, meaning I literally tried to enter in a bad "DATE" combination of values and the "validation" would pop every time.  I was unable to enter into the "Date" field bad data.  So I thought that maybe the data was previously entered incorrectly before my validation came into the file when I was contacted two years ago.  But the issue is current, on records created this month so the validation has been in for a long time ... so I deleted several of the dates and reentered them.  And it did not matter.  The export on those records I edited where the same, the date fields exported to the left and are considered "Numbers" in "Open Office".  Weird.

4. This is what is strange, my client is in Dubai and they use the British date system DDMMYY,  I'm in the US and I use the MMDDYY and the file is "HOSTED" in "Canada"  not sure yet what date format Canada uses on the server ... working this.  Would this matter ??  Filemaker by default on my computer use MMDDYY and my client user DDMMYY so we've decide to user a custom date ... 25 Jun 15 so everybody can read the date.  Confused ...

Something else is strange.

I've opened the cvs file in both "NUMBER" ( Mac ) and "OPEN OFFICE" and "NUMBERS" defaults to "Automatic" when you look at the "Cell" format but "OPEN OFFICE" when you look at the "CELL" format list the fields that are aligning to the left as a "NUMBER - General" and when I click into the field I saw the following character in front of my date value ..." ' " a hyphen??  Now I went back into "Filemaker" with the expectation of removing the hyphens by my date fields entries have no "Hypens", not spaces, not odd characters" ... the hyphen is no longer present on the export ?? weird.

Also I can not change the cell format to "DATE" either in "NUMBERS" or "OPEN OFFICE".

The field that format to the "Right" or not correct, All date field were export with a date range of "Aug 4, 2015 to Sept 3, 2015  

I've remove the fields with sensitive data and attached the actual file with the date field values.  Some are number some are date and even those with a date are number.  Also, the "DAY" is getting confused in the export and end up being the "MONTH" so 10 should be the "DAY" in August not October.

Any assistance here I would be grateful.

Thank you.

Tom

InvoiceDates.csv

PmtDates.csv

Edited by Snozzles
Link to comment
Share on other sites

1. We host the file from Canada, I am in the US and my client is in Dubai.

This doesn't answer my question. Are you also a user of the file? Do you - or anyone else whose date format is M/D/Y - enter data into the file?

 

2. I've doubled checked this ... the Filemaker field is set for "DATE".

This answers my 3rd question. My 2nd and 4th questions remain unanswered.

Link to comment
Share on other sites

Comment,

Gotcha ...

1. Both me and my client are "Users" of the file that is hosted from the server in Canada

2. See Attached picture.

4. Well, I would like to think and expect a "DATE" to be exported.

Comment, I have not yet discounted the "User" since I've seen users including myself do things differently.  However, I have yet to cause bad date to be entered into this date field at least on my side of the database.

Thank you for you assistance.

Tom :-)

 

 

Screen Shot 2015-09-06 at 3.30.32 PM.png

Link to comment
Share on other sites

Well, that's a small progress. Here's something you need to know: when a file is set to "Always use current system settings", each user can (and must) enter dates in their own format - you as M/D/Y, your client as D/M/Y. Also, when the date field is formatted to display dates "As entered", each one of you will see the dates formatted in your own format, no matter who entered them or how.

However, when you export the dates, the exported file will have the dates in the format that is saved with the file. In your case, I presume that format is M/D/Y. Therefore, if you want your client to export the dates in a different format, they need to export from a layout where the date field is formatted to display the way they want it (you still haven't told us what that might be), and select the option "Apply current layout’s data formatting to exported data" when exporting.

Edited by comment
  • Like 1
Link to comment
Share on other sites

This topic is 3102 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.