10 Replies Latest reply on Dec 27, 2011 9:59 AM by Snozzle's

    Import Record Question

    Snozzle's

      Title

      Import Record Question

      Post

      Hi,

      I've cloned a file with updated scripts and I will be importing records from the old file to the new cloned ( No Records ) file.

      I have a field that does: Get ( RecordID )

      Quetions:

      When I import, will the records that are being imported retain thier original Record ID or will a new Record ID be created when the record is imported into the cloned ( no records ) file?

      I am hopeful that the imported data will be retained including calculations like Get ( RecordID ).  Again, this would be the Record ID of the imported data.

       

      Thank you so much!

      Tom :-)

        • 1. Re: Import Record Question
          Sorbsbuster

          The record ID is maintained.  You can test that yourself by simply creating a one-field file (RecordID, creating a few records, cloning, and importing.

          • 2. Re: Import Record Question
            Snozzle's

            Sorbsbuster,

            So having a key field as they are called in database lingo collapses when you import records when relationships are build around them, and you start import recordes that have key fields with relationships built around them.  Hehehe ...

            So my thinking now is, record id's (keys) are just really for relative reference only!  Goto ... this record in the moment, of really no importances!

            Ha Ha!

            Merry Christmas.

             

            Thank you 

            Tom :-)

            • 3. Re: Import Record Question
              Sorbsbuster

              Not sure I follow your logic there: "having a key field a [] collapses when you import records when relationships are build around them..."

              I thought you would have been worried that the relationships you have built around specific RecordIDs would have been corrupted by an import. In fact you can show, if you run the test yourself, that the RecordIDs are maintained.

              • 4. Re: Import Record Question
                LaRetta_1

                IMPORTANT:  Get ( RecordID) is not a key field. Get ( RecordID ) should never be used as a key ID.  A calculation should never be used as a primary key (there are a few rare exceptions). 

                Hi Tom,

                You need to instead use an auto-enter serial number.  Create a primary key.  If your table is named Customers, the primary key would be CustomerID.  Go to Options and the auto-enter tab.  From there, set to serial increment by 1 starting at 1.

                In your existing records, Show All Records and then place your cursor in this new CustomerID field (or whatever you call it).  Select Replace Field Contents and by serial.  Be sure the checkbox is checked 'update auto-enter' so that it will bring your last serial in auto-enter to the last number of your existing records.

                Now when you import into that table, after you map the fields, FM will ask if you want to 'perform auto-enter'.  This is important to be checked because you want newly imported records to inherit the next numbers in sequence.  This ID in its own table is called the primary key (or parent ID).  Now in a related table, you can add a CustomerID.  In that table, it is called the foreign key.  When you connect the two keys, you have a relationship.

                • 6. Re: Import Record Question
                  Snozzle's

                  Hi,

                  Thank you.

                  I was wanting to use a field that was not available to anybody for a particular relationship I built and I chose the Record ID because Filemaker didn't allow anybody to change it! The Auto-Enter Serial was accessable. Turned out not to be such a good idea, Oops!

                  Anyway, after testing the Auto-Enter Serial feature, I found that leaving the Auto-Update box unselected maintained the serial count in the option box, having it checked, advanced the sequence by the number of records imported. Even thought the fields in each record remained intact.

                  I am sure there is a reason for this, but at this moment not advancing my Auto-Enter sequence is what I was looking for as well as maintaining my sequence withing each record.

                  So, how can someone protect the Auto-Enter Serial.  Meaning, someone needs Administrative access other than ME!  That field gets cleared or edited for whatever reason and my relationship now will show bad data. Hmm ...

                  Tom :-)

                  • 7. Re: Import Record Question
                    Sorbsbuster

                    Why do you have to show the serial number on any layout?  Or at least suppress it from being accessible in browse mode?  You can import records to the new version and then use the same script to reset the next serial value.  No admin account necessary.

                    • 8. Re: Import Record Question
                      LaRetta_1

                      You can also set the auto-enter serial to 'prohibit modification' (see auto-enter tab at the bottom).

                      There are times that you will not want to check the 'perform auto enter' and that is when the serials increment.  If it is empty clone and you are also importing the serial then no, you do not want it to increment because it will change the serial and that is what your relationships should be based upon.

                      But you must then sort in ascending on the serial number, go to last record, get the serial, add a 1 and as said, set the next serial in your auto-enter value so new records you begin adding in the table manually will inherit the updated new serials and will increment properly.

                      • 9. Re: Import Record Question
                        philmodjunk

                        It would be nice if we could selectively specify auto-enter options during import, but they are an 'all or nothing' proposition. Sometimes you have to import date without the auto-enter option enabled, then use a script to selectively update fields in the found set of newly imported records in order to get the results you need.

                        • 10. Re: Import Record Question
                          Snozzle's

                          Hi everybody,

                          I script everything, I give no access to any pull down menu, grey everything out!  However, this one client wasn't full access privileges, hmm ... I said.

                          I'm doing the best I can to protect certain critical fields,  and that is why I chose RECORD ID because it was not accessable even to ME!

                          However, that idea collapsed when I imported my clients records.  I wasn't think, my fault.  Oops!

                          The only challenge I am having with the Auto-Enter Serial is that it is accessable, that all.

                          I ran into a friend who summed up my development with this phrase and I quote; "Tom, you better be gramma proof" Boy he wasn't kidding :-)

                          Thank you, the auto-enter serial solve my importing issue and then some.

                          Tom :-)