8 Replies Latest reply on Jul 29, 2010 10:15 AM by philmodjunk

    Problem with auto-enter serials when importing into (empty) clone

    smashingly

      Title

      Problem with auto-enter serials when importing into (empty) clone

      Post

      Hi,

      I've tried to find an answer on the forums but wasn't able to, so hopefully this hasn't been covered elsewhere already.

      I have a client for whom I developed a FMP database.  It's a single-user DB, so for any updating or adding of new functionality, I work on a copy myself, then when it's time to update him, he FTPs his DB to me, I import his data into my updated DB, then email him a link for him to FTP the new database.

      This recently fell apart a bit, when he had added a significant number of records to one of the tables, which has a primary key PersonID which is an auto-enter serial.  I imported his records into my empty clone, but then we started getting "non-unique value" type errors.  Upon investigation I quickly saw in Manage Database that the PersonID field was set to a "next value" that was lower than the highest PersonID number.  Creating a new record in the Person table was therefore trying to auto-enter a serial number that already existed.

      Can anyone tell me why this is happening?  I don't really want to tick the import box which tells FMP to perform auto-enter operations, because won't that completely renumber the PersonID values as they're imported, thus screwing up all my join tables and other relationships using the PersonID as a foreign key???  My current workaround is simply to look at the max ID number in each table and manually tweak the auto-enter serial's "Next value" field.  Bit of a pain!

      Any ideas would be muchly appreciated!  I'm using Filemaker Pro Advanced 11 (latest updates as of 24 Jul 2010) under Mac OS X 10.6.4.

      regards

      Ash

        • 1. Re: Problem with auto-enter serials when importing into (empty) clone
          philmodjunk

          The best approach I've been able to come up with is to script the import process and include steps that use the Set Next Serial Value to update any auto-entered serial numbers after import.

          For a single table import, you script might look like this:

          Go To Layout [//specify import layou]
          Import Records [//specify import details]
          Sort Records [Restore ; No dialog ] //Sort on serial number field in descending order
          Go To Record [First]
          Set Next Serial Value [Table::SerialNumberField ; Table::SerialNumberField + 1]

          • 2. Re: Problem with auto-enter serials when importing into (empty) clone
            smashingly

            Awesome, thanks for the response.  I guess I've been resisting moving to scripted imports for a while - doing it manually allows me to see what's going on, I guess - call me a paranoid newbie :-p  But it's probably time to start automating it to save myself the headache of going through every single table and importing, then updating auto-enter serials.

            Question: That "Sort Records" script step - does that actually mess with the table in question, in terms of reordering its records, permanently?  If so, I guess I'd want to re-sort it after the above script, to put it back in ascending order, yes?

            thanks

            Ash

            • 3. Re: Problem with auto-enter serials when importing into (empty) clone
              philmodjunk

              You can certainly re-sort a given table occurrence's found set as often as you need to.

              You may not realize this, but each "box" (called a table occurrence) in Manage | Database |Relationships has its own current record, found set and sort order. Thus, you can have many different found sets for the same table (and sort orders, current records) as you want simply by defining additional table occurrences in the relationship graph and attaching each to a different layout.

              • 4. Re: Problem with auto-enter serials when importing into (empty) clone
                smashingly

                Interesting!  That is a *really* handy thing to know.  I have two layouts which reference the same table, one is a list view and shows the most recent (sort by auto-enter ID number, descending); the other is a normal layout.  I'd rather keep all layouts in ascending ID-number order (i.e., the order in which they were created) - so maybe I could create another TO of this one table and thus it can have its own sort order.  Brilliant tip, thanks!

                Question about your import script - why sort descending then go to record (first)... why not sort ascending and go to record (last) ?  I've done it verbatim to your spec and it works brilliantly (SO worth the investment of time/effort) but I'm just wondering if there's any reason why ascending/last wouldn't also work?

                Lastly, regarding the import file.  I noticed that the Import Records script step doesn't let you specify field-matching options unless you also specify a specific file to import.  This reduces flexibility a bit because it means my import file always has to have the same name... any ideas on this?

                • 5. Re: Problem with auto-enter serials when importing into (empty) clone
                  philmodjunk

                  "Question about your import script - why sort descending then go to record (first)... why not sort ascending and go to record (last) ?"

                  Either method works. Use whichever method you prefer.

                  "I noticed that the Import Records script step doesn't let you specify field-matching options unless you also specify a specific file to import.  This reduces flexibility a bit because it means my import file always has to have the same name... any ideas on this?"

                  There's a way to do this. Set up your import records script step to work from a specific file so that you can correctly map your fields for import. Then, after you are finished insert a variable into the Specify Data Source dialog so that it looks like this:

                  $PathVariable
                  Filename: YourImportfile.fp7

                  Now you can use a variety of methods to place a specific filepath into $PathVariable and the fields will still be correctly mapped.

                  • 6. Re: Problem with auto-enter serials when importing into (empty) clone
                    smashingly

                    OK thanks for clearing up the sort-order thing.

                    And re: the import records "Specify Data Source" dialog - I did some reading on this, you can specify more than one path, and it will try them in the order listed.  So you're saying that so long as the originally-specified import file remains in the list of paths, the field-matching settings will be retained.  Nice trick!  It would be nice if Filemaker would make this script step a little better implemented - for example, some way of saying "if the specified file has a table by the same name, try to import all records [incl. switchable options such as Match Field Names]...

                    as always, I'm enlightened by your expertise Phil - thanks very much

                    Ash

                    • 7. Re: Problem with auto-enter serials when importing into (empty) clone
                      smashingly

                      Hmm... just thinking... is there any way in FMP to bring up a File browse type dialog box, to let the user select a file to import from?

                      • 8. Re: Problem with auto-enter serials when importing into (empty) clone
                        philmodjunk

                        Yes,

                        What I do is define a container field and use Insert File to pop up a dialog for inserting the file into the container by reference. I can then extract the file path from the container field and put it in a variable that's refrenced in my Import Records script steps.