8 Replies Latest reply on Aug 2, 2011 4:15 PM by philmodjunk

    Error on "date" formatted data import but not as "text"

    Grant

      Title

      Error on "date" formatted data import but not as "text"

      Post

      OK this is a crazy one.

      I am importing data from UPS World Ship and one of the items is a date. 

      When I set my Filemaker field type to "date" I get an error even though the date data is formatted the exact same way as the Filemaker field format for date is.  Format Example: 4/15/2010 (no leading zero)

      The error is..... [FileMaker][FileMaker ODBC] FQL0013/(1:25): Incompatible types in assignment.   Code: 80004005

      However, when I set the field to "text", the data moves perfectly without error.

      I need the field to be formatted as a date field.

      Any ideas?

       

      Thanks!

      Grant

        • 1. Re: Error on "date" formatted data import but not as "text"
          philmodjunk

          FileMaker stores dates as a number and we don't know in what format your UPS date is really stored in that software.

          You may need to import first into a text field and then use a second step to move the data into your date field as a date.

          One method is to import into a separate text field, then use Replace field contents with the calculation option and the GetAsDate function to move the data into the date field.

          You can also use getasdate in a calculation field to convert the data into a date and avoid that extra step, but then you cannot directly edit this field, you'd have to edit the text field to make changes.

          If you want to get a bit fancy, you can import this date into a global text field with an Auto-enter calcluation defined on the date field that auto-enters GetAsDate ( globalTextField ) and then you can import and convert the date data during import--provided you enable auto-enter operations during the import.

          • 2. Re: Error on "date" formatted data import but not as "text"
            Grant

            Cool.  So in theory...  I would be able to import the UPS data as a 10 digit number and it would convert to date?

            • 3. Re: Error on "date" formatted data import but not as "text"
              philmodjunk

              It depends on that "10 digit number". A date like 7/29/2011 doesn't have 10 digits so I'm not sure I understand your question. If you mean to import the date in numeric form, then you may still need to use one method or another to convert the value. FileMaker stores dates as a number counting the number of days elapsed since 12/31/0000. The UPS software may be using a different starting value for it's dates and may not count off the dates in days, but in smaller units such as seconds (As is found in TimeStamp fields).

              • 4. Re: Error on "date" formatted data import but not as "text"
                Grant

                OK thanks! 

                I'll talk to my UPS tech rep to see how the data is formatted.  I think he is just using windows scripting host and crossware to bring the data in as a string of characters.  Maybe he can just adjust the script to insert current date rather than paste in a string of characters?

                Wish me luck!

                Thanks again!

                 

                Grant

                • 5. Re: Error on "date" formatted data import but not as "text"
                  Grant

                  My UPS reps seems to think he can reformat the data based on your info.  However he is asking to confirm if the date you provided is supposed to be 12/31/2000 rather than the 12/31/0000 that you had posted.

                  Thanks!

                  Grant

                  • 6. Re: Error on "date" formatted data import but not as "text"
                    philmodjunk

                    Nope. I meant 12/31/0000. Each value in a FileMaker date field is a number counting the number of days elapsed between that "0 date" and the date the data in this field is intended to represent.

                    Here's a further example: If you put 1 in a number field and then define a calculation field to return "date" and use the name of the number field as the sole term in the calculation, you'll see 1/1/0001 returned as the date, one day past 12/31/0000. If I put a 2 in that number field, this calcualiton will update to show 1/2/0001. Please note that we are discussing the value of the data, not the format with which it is displayed. (And I am using MMDDYYYY format for purposes of this discussion.)

                    • 7. Re: Error on "date" formatted data import but not as "text"
                      CarlPotter

                      Phil,

                           I am the UPS Tech helping Grant out with this date issue.  Grant is using Filemaker Pro on a PC, and we are attempting to find a way to convert a standard date such as 8/2/2011 into the integer number that Filemaker Pro wants so that it will provide the proper date in the date formatted field he has setup. 

                           Can you provide me with the calculations I would need to make to convert 8/2/2011 into the proper Integer value to send to Filemaker Pro's Date field to display 8/2/2011 please?  I can code the solution easy enough, however I need to know the calculations I need to make to create the proper value.  Thanks.

                      Carl

                      • 8. Re: Error on "date" formatted data import but not as "text"
                        philmodjunk

                        I think it would be easier to import the date as text and then FileMaker can convert the imported text into a date. This thread spells out how to do just that.

                        Such a calculation, starting from the text 8/2/2011 requires computing the number of elapsed days since 12/31/0000--that's a far from simple algorithm due to months of differing numbers of days and leap years.

                        In the UPS software, how is the date stored internally? Surely it's a number as well? (Most systems use a variation of this method to store dates as it makes calculating the number of days between dates a simple arithmetic operation.)

                        If so, we should be able to construct a very simple conversion (just adding or substracting a constant) to adjust for a different "zero" date. If by any chance this "date" is more of a timestamp where elapsed time is measured by the second, things get a bit more complex, but it's still a pretty simple conversion process. But starting from text? better to just import the text and then A GetAsDate function within FileMaker can produce a true date value that will work with FileMaker Sorts, searches, calculations, date formatting etc.