Devin Posted May 21, 2014 Posted May 21, 2014 I'm doing an XML import of data. In the date field of the XML it's formated as YYYY-MM-DD. we want the date to be formatted in Filemaker at System Short.. In doing this we get the ?. Why can't Filemaker understand the date and reformat? How can I go about changing the format on import or right after import? Thanks
Davesmind Posted May 21, 2014 Posted May 21, 2014 Are you getting any sort of error importing the data into the field in the first place? If you enter a YYYY-MM-DD into a date field it automatically tells you the format is wrong. If you import the data as MM-DD-YYYY you will have no issue and then can work with the data any way you want. All you need to do is change the data formatting on the Inspector/Data Tab while selecting the field. Just set the format to Custom and change the arrangement of the date to your specific format.
Davesmind Posted May 21, 2014 Posted May 21, 2014 If you cannot alter how the data is imported, then another option would be to import the data into a holding field and parsing the YYYY-MM-DD format into the correct date field. When importing any outside data into any of my FM systems, I've always built a importing holding table, especially when data comes from outside the company. In this way if an error comes across i can easily check the data, clean it up and then import it into my live data. Once you get the process down you can make this as simple as clicking on button. 1
Devin Posted May 21, 2014 Author Posted May 21, 2014 There is no error on import. If I leave the date filed as entered is will show the date as YYY-MM-DD but we want it to show as DD/MM/YY. I've not been able to get custom to work.
Davesmind Posted May 21, 2014 Posted May 21, 2014 Can you check the field type of the field you are importing the date data into? I am going to guess that it is a text field.
Devin Posted May 21, 2014 Author Posted May 21, 2014 The field is a date field. I will bring the data into a temp filed for importing, that's a great idea. The import is an ODBC import will happen several hundred times a day. You have a calc that will parse this to the right format. It's mind blowing that filemaker can not re-parse it on it's on. YYYY-MM-DD is an ISO standard. (Other words correct way to transfer date information)
Lee Smith Posted May 21, 2014 Posted May 21, 2014 YYYY-MM-DD is not a date, it is text. Your import field has to be type of Text. Once it is in the field, you can then change the format to what you want. See if this helps. Let ( [ Parts = Substitute ( YourDatefield ; [ "/" ; " " ] ; [ "-" ; " " ] ) ; Month = RightWords ( Parts ; 1 ) ; Day = MiddleWords ( Parts ; 2 ; 1 ) ; Year = LeftWords ( Parts ; 1 ) ] ; Day & "/" & Month & "/" & Year ) Automatic message This topic has been moved from "Conditional Formatting" to "Calculation Engine (Define Fields)". Conditional Formatting is for formatting a text field.
Devin Posted May 21, 2014 Author Posted May 21, 2014 Lee, YYYY-MM-DD is a date. Who would look at that and say it's just text and has nothing to do with a date To get back to fixing my issue. I'm going to import the records into holding fields.. AS TEXT. To Parse the data, should this be done a script or as a calculation on the filed on import. Thanks for your help
Davesmind Posted May 21, 2014 Posted May 21, 2014 What Lee is referring to is the field that you are importing into within your Filemaker database. The field on the database structure side is a text field as it is the only field that would except the data you are importing without generating an import error. Current Import ---------------------------------------------------------- XML(data is text) >> FILEMAKER date information myfmfield (text) Correct Import ---------------------------------------------------------- XML >> FILEMAKER date information myfmfield (date) If Filemaker is set to treat the data as a date only field, the import would have failed. Because you are importing XML text into a text structured field it will accept almost any data. Unfortunately a text field doesn't automatically understand what a date is only each character. A date field on the other hand will see and expect MM/DD/YYYY. Your data will need to be scrubbed and changed over to the MM/DD/YYYY setup. Once it is inputted into a date structured field in the expected MM-DD-YYYY format, you can display the data in any format you deem correct including the YYYY-MM-DD.
comment Posted May 21, 2014 Posted May 21, 2014 I'm doing an XML import of data. In the date field of the XML it's formated as YYYY-MM-DD. we want the date to be formatted in Filemaker at System Short.. The simplest way to do this would be to alter your XSLT stylesheet (you do use an XSLT stylesheet in your import, don't you?) so that it transforms the YYYY-MM-DD date to the format used by your file. YYYY-MM-DD is a date. It is a date only if Filemaker will recognize it as such. If your file is set to use another date format, then YYYY-MM-DD is a meaningless text string. The field on the database structure side is a text field as it is the only field that would except the data you are importing without generating an import error. I am afraid you are mistaken about that: Filemaker allows you to import any kind of junk into a date field, resulting (as in this case) in invalid dates. 1
Davesmind Posted May 21, 2014 Posted May 21, 2014 I am afraid you are mistaken about that: Filemaker allows you to import any kind of junk into a date field, resulting (as in this case) in invalid dates. True, i guess it will ignore the extra data and form strange dates. On the XML side, good call on the stylesheets, perfect avenue to prevent bad data importation.
Devin Posted May 22, 2014 Author Posted May 22, 2014 I got the issue resolved. The issues was that when we ran the SQL Query we applied a TRIM the date field. We are using a 360works plugin for our SQL query and import and I think that's using a stylesheet for it's import. Thanks guys for helping!
Recommended Posts
This topic is 3895 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 accountSign in
Already have an account? Sign in here.
Sign In Now