3 Replies Latest reply on Jun 17, 2015 11:09 AM by philmodjunk

    Dates do not import from Excel

    FilmUser

      Title

      Dates do not import from Excel

      Post

      Windows 7 Enterprise, FM 13 Advanced

       

      The data base is of products, each of which has a unique and identifying code number.

      I receive emails regularly, into which someone has entered a long list of code numbers. The purpose of the email is to let me know that today's date needs to be entered into a date field in each record. The email creator has entered the codes with a return between each code, and nothing else, so the vertical list pastes nicely into excel.

      I have an excel (.xlsx) on my desktop that I use to create an import source, from the emails, with the first row having field names created by pasting them from field definitions in the data base. The two fields (columns in the excel sheet) are the code and the date field I need to enter. By "finding all" records in the data base and running the import (update matching. . ), it executes the find and enters the date.

      I have been using this successfully with FM12 for months. When using it for the first time in FM13, the import works as far as finding the correct records (match field works) but does not enter the date. After some experimenting (including making a FM13 file of the excel sheet), I find that dates are what doesn’t enter. Text fields will enter with the import.

      The target fields are of type Date, are auto enter at creation to empty themselves, if that matters, and are formatted the same as the excel cells.

      Is something different in FM13 that would affect this?

      .

        • 1. Re: Dates do not import from Excel
          philmodjunk

          Did you enable  auto-enter options during the import? What happens if you remove the auto enter field option?

          You may have something to report as a bug in Report an Issue--though this doesn't match any other reports that I've read and I would think that failure to import a date from Excel would have produced such a report a long time ago.

          I'd not use an import file for this. I'd just paste the return separated list int a global text field in FileMaker and set up a script that first uses the pasted list to find the records (can be a simple Go TO Related records step) and then uses replace field contents to update the dates.

          • 2. Re: Dates do not import from Excel
            FilmUser

            I'll try your suggestion (haven't done anything like this before, so I may have another question), and I forgot to say, I hadn't tried clearing the auto enter option, but I just did and the import worked. Why? Something about 13?

            • 3. Re: Dates do not import from Excel
              philmodjunk

              My guess is that the data is being imported and then the auto-enter option clears the imported data.

              This won't happen during an import unless:

              "do not replace existing value..." is cleared

              The "enable auto enter options" check box has been selected in the import records settings.

              The key trick behind my suggestion is that if you put a return separated list of values into a text field and use that field as a match field in a relationship, records on the other side of the relationship will be related records if their match field matches any one of the listed values.

              So if I put:

              Apple
              Orange
              Kiwi

              into such a match field. Records on the other side of the relationship will link to this record if their match field is apple OR kiwi or orange....

              That allows you to basically do what you need in two steps:

              Go To Related records
              Replace Field Contents