3 Replies Latest reply on Jul 19, 2017 6:05 PM by philmodjunk

    Some records invisible after table-table import


      I'm writing a script to manage two sets of employees - active and those that have left (but records need to be preserved because they might come back and because some of their data feeds into historical records of the institution).


      I've created a separate table for the employees that have left. I've tried two different ways of writing the script and also just manually performing an import and the result is always the same: some records are imported just fine every time while others are imported as ghosts every time I try: a record is created but I can't see any data.


      I know some data is in there because, when I sort by ID number, the invisible/blank records sort with the visible records correctly according to their ids. Also, when I try to import one of the invisible records a second time, validation stops me because I set up the ID number field as a unique value.


      I don't think my script is the problem, because the manual import produces the same result. I also checked to see if the problem was only with older records but, judging by the ID numbers, when the record was entered doesn't seem to be the deciding factor.


      The only clue I have is that, when lining up fields to import, some records match perfectly (the fields in the two tables are identical) and some have a <field missing> line at the top that appears to put data in the wrong fields. I haven't been able to find an explanation of why this is happening.


      This database was recently converted from FMP 5 through FMP 7 and it's now in FMP 14. Could this be a side effect of the conversion process? Is some of the data corrupt?


      I've been tearing my hair out all day. Any help is most welcome!

        • 1. Re: Some records invisible after table-table import

          Yes it could be corrupt. Use FMP 14 to run a recover on the file and see what is reported. Even if recover does not report any problems, test the new recovered copy to see if it shows this behavior. If the recovered file works and is produced with an "OK to use" message, you can then try using advanced recover options to only rebuild the indexes for this file to see if that works. A file with just rebuilt indexes is preferable to one that needs a full recover.


          THen you might consider keeping all employees in one table instead of copying them to another. I see no real reason to copy them to another table given the admittedly limited info of your first post here.

          1 of 1 people found this helpful
          • 2. Re: Some records invisible after table-table import

            Thank you! Embarrassingly, after I posted my question, I discovered that the problem was caused by user error - I had copied some layouts from one table to the other but forgot to change which table the fields were pulling data from. There is something mysterious going on that has to do with relationships to other tables, but it's moot in the big picture. No corruption in the database.


            I wrestled a lot with keeping all the records in one table. I agree, that would be best. However, I couldn't come up with an acceptable solution that would keep the inactive records out of view of my client. the most active user has admin permission, so it would be hard to segregate records for her. Plus, they like seeing the number of records at the top of the layout and knowing that's the total number of active people in their organization. So importing between tables was the best solution I could find (better that separate dbs, which was their old solution).

            • 3. Re: Some records invisible after table-table import

              You can create your own "active" widget on your layout to show the total number of active records and how many are in the current found set.


              Even as a full access user, you can set them up with custom menus that automatically omit inactive records from any found set they might otherwise pull up--whether from a scripted find, a user initiated find or via "show all or show omitted" menu selections.