0 Replies Latest reply on Feb 5, 2016 3:17 PM by realgrouchy

    Error with field validation on import?

    realgrouchy

      I was having a problem with an import I was performing (File>Import, not via script). I exported a set of records from one of the tables in my DB, changed the year of those records from 2015 to 2016 using MS Excel:Mac 2011, and attempted to reimport them to add new records for this year.

       

      No matter the file format (.xlsx, .xls, .csv), the results were the same:

      - On the field mapping screen, the field titles were recognized, as were the individual values when I scrolled through them. I had "add records" selected.

      - I do not have the "perform auto-enter calculations, etc." checkbox selected.

      - After clicking "import", the confirmation dialog reports the correct number of records as having been imported.

      - However, no new records are created. After the import I am shown a record set of 0 records, and the number of total records (~59000) has not increased.

      - If I do not select "Don't import first entry", the record with the headers appears, but no more than that.

       

      I am trying to narrow down what is happening here:

      - OS X 10.10.5 (I do have a couple OS updates pending install...)

      - Same behaviour on FMP 14.0.4 and FMPA 14.0.4

       

      After fiddling with it some more while typing this up, I was able to get it to import, and with further fiddling I discovered the issue:

      - In addition to the "Year" field that appears in each record, I have a "GlobalYear" field which is a Global field in another table from which I can select the same values as the Year field.

      - The "Year" field has two validation parameters: It must be a member of the "Years" value list, and it must satisfy the calculation of ThisTable::Year = OtherTable::GlobalYear.

      - I had not changed my GlobalYear field from 2015 to 2016.

      - The validation is set to Always validate. If I set it to validate only during data entry, it imports. Both of these settings work the same whether or not "allow user to override during data entry" is selected.

       

      While the fix was to simply change my GlobalYear from 2015 to 2016, it took me much more effort to track that down than it should have because I wasn't given any indication that validation was the problem (as opposed to file format, encoding, etc.). I think the error handling was poor in the following ways:

      - The import confirmation dialog claims that records were imported, but I am brought to a display of 0/59000 records. This makes me worry that I might have imported them into the wrong table or something.

      - The "allow user to override during data entry" validation setting did not allow the import to override when validation is set to "Always"

      - The records are either not created, or are created and then deleted, due to a validation error, however there is no indication that a validation error was triggered (I can certainly see why you wouldn't want to give the user 5000 validation errors, but a message "one or more record(s) was not added due to a validation error in the field Year" would have helped)

      - If the validation had only prevented some records from importing, I might not have noticed because the confirmation dialog would still say total number] of records imported.

      - The documentation (i.e. online) for File>Import should be updated to indicate that records will not

       

      Hopefully something here can be addressed in FMP's error handling. I'll update my documentation to add "make sure GlobalYear is correct before importing", which I should have thought to have done, but at least in making that error I found this issue with error handling.

       

      - RG>