5 Replies Latest reply on Mar 10, 2014 9:41 AM by philmodjunk

    Auto-generate serial number in related field upon Excel file import

    JohnFrankson

      Title

      Auto-generate serial number in related field upon Excel file import

      Post

           Hi. I'm having some trouble with relational fields. I have a table with basic info (auto-generated ID number, first name, last name, DOB) and a second table that has other additional info related via the ID number. I'm trying to import new records from an Excel file into the second table and have it also create new records in the first table (more specifically to create that auto-generated ID number). I've got the tables connected via the ID number and clicked the "allow creation..." In the relationship dialog box. 

           I'm running 13 on a Mac. Any ideas? Is this even possible?

        • 1. Re: Auto-generate serial number in related field upon Excel file import
          philmodjunk

               It's possible, but "allow creation.." has other purposes. It doesn't automatically create records in a related table. The exact details can depend on the data that you are importing. Is the data that you are importing 100% all the time data that needs to link to new records in your first table? Or does some of this data need to link to existing records in your first table?

          • 2. Re: Auto-generate serial number in related field upon Excel file import
            JohnFrankson

                 Good question. The data will be a mix. Some will need to link and some will be new.

            • 3. Re: Auto-generate serial number in related field upon Excel file import
              philmodjunk

                   And how, just by examining the data in your excel file for rows that should link to an existing record in your first table, can you identify which record in Table 1 should match to it? Are names the only data in this table that let's you determine this? (Names are not unique so this can be a problem.)

              • 4. Re: Auto-generate serial number in related field upon Excel file import
                JohnFrankson

                     I was going to run a match on email address to determine if the person was already in table 1. These are folks who've filled out an online application (built before I got here) for one of our university programs. I don't have student ID numbers yet as their not students yet. It a relatively small number of folks…maybe 40 a year…so I can error-check. I think I'll just suck it up this year and build a new application that requires a unique login.

                      

                • 5. Re: Auto-generate serial number in related field upon Excel file import
                  philmodjunk

                       Your email address should work fairly well and much better than names.

                       Define a new third table that has a field for every column of data in your excel file that holds data you need in either table 1 or table 2.

                       Ad a field to table3 for your Serial ID number from Table 1.

                       Set up this relationship:

                       Table1::emailFIeld = Table3::EmailField

                       After import, you can perform a find for all records in Table3 that do not have a matching record in Table1. You can then use Import records to import selected fields from this Table3 found set into Table1 to generate the new Table1 records and their SerialID numbers. Be sure to click the checkbox to enable auto-enter options for this import.

                       Then you can show all records in Table 3 and use Replace Field Contents to copy the ID number from table 1 into the field in table 3 that you set up for this ID number.

                       Now you can use a third import records to move this data into Table 2 now that is has an ID number.

                       Final step is to delete all records from Table3 so that it is ready to go for the next Import from your excel spread sheet.

                       Please note that this entire process can be scripted so that all you do is click a button and FileMaker takes it from there.