4 Replies Latest reply on Jun 25, 2013 5:56 AM by tweller860

    Importing old records before the new ones. pls help

    marksanchez

      I have a question hopefully it will be a simple one. I have currently 80 records on my contact layout, I am trying to import 120 old records we had on a excel file. The question is :

      1. I will like the old records be behind the new ones, so record 1 will be the oldest but since I import the records they apprear last and the new ones appeared fisrt.

      2. Is there a way to sort records on layout mode so it can sort of on specific field?

      3. how can I import the old records from the excel files so they can appear as the first records and the current new records appear last?

      thank you for your time...

        • 1. Re: Importing old records before the new ones. pls help
          alquimby

          Mark,

           

               In Browse mode (not Layout mode), you can sort on a particular field. You can create a script that sorts on the field you want, then attach the script to a trigger (done in Layout mode) so that the records sort whenever anyone lands on that layout.

           

               However, if your desire is that the "old" records have auto entered serial numbers that precede the "new" records, there is an easy solution, but you should only do it if no related records have been created that relate to the "new" records by the auto entered serial number. First, you need to export the "new" records into a temporary FileMaker file; then delete the "new" records from your file and reset the auto entered serial number to start at 1. Then import the "old" records; then import the "new" records from the temporary file. Now, you have "old" preceding "new."

           

               Like I said, only do this if you have no related records, or you will break the relationships. And make a backup of your main file before you start, in case things go "haywire."

           

          Al Quimby

          • 2. Re: Importing old records before the new ones. pls help
            mikebeargie

            Mark,

             

            First off, a bit of best practice. You should always have these 5 fields in every filemaker table you create:

             

            1) Serial number that auto-increments (primary key)

            2) Creation timestamp

            3) Modification timestamp

            4) Creation user (or account)

            5) Modification user (or account)

             

            see attached sample of these 5 fields.

             

            You can sort ascending based on serial number to get the records sorted based on when they were created/imported.

             

            Then to accomplish what you need, you either need to:

            1) Export the existing records, then delete them (empty table). Then import the old records first, and then reimport the new records you just exported and cleared. With an auto-enter serial, they would not be in order.

             

            2) Clear the primary serial of the new/existing records. Import the old records, which will get an auto-enter serial value. Then find for where serial is blank ("=") and replace (click in field, ctrl + = ) the found set with serial values, check the box that says "update the value of the serial".

             

            Note that you should NEVER mess with serial numbers as noted above anytime AFTER you have established a relationship (parent id key = child id key) to another table based on serial number.

            • 3. Re: Importing old records before the new ones. pls help
              Mike_Mitchell

              See also duplicate thread:

               

              https://fmdev.filemaker.com/message/118746#118746

               

              Mike

               

              Mark - Please only post your questions once to avoid confusion.

              • 4. Re: Importing old records before the new ones. pls help
                tweller860

                Mark,

                 

                When in doubt, FileMaker defaults to creation order when displaying records so changing the order your contact records are displayed will require some action on your part.  As I understand your problem, I believe you have two options:

                 

                1) To overcome the import order issue, you would need to export the existing records from FileMaker into a temporary file, delete all records, import the 120 old records from your Excel file and then re-import the 80 new records from your temporary file.  While this will accomplish what you're looking to do, I would advise against it.  There is always a chance something happens during the export/re-import process that could result in lost data.  Plus, as you add additional contacts, you may find yourself back in the same place you are now with records not appearing in the desired order.  If you decide to proceed with this option, please do so with extreme caution.

                 

                2A)  My prefered suggestion is to write a simple script that sorts the records in the order you want i.e. Last Name, First Name or by Contact ID, etc.  The options for the "Sort Records" script step include "Perform without dialog" and "Keep records in sorted order".  In this instance, I would recommend you checking both options.  That way, you won't be prompted to click "OK" every time the script is exectued and the records will stay in sorted order until you change the found set.  With the script written, place a button object on your data entry layout setup to call the "Sort" script you just wrote.  That way, you can execute it when ever you need to have the records sorted in the desired order.

                 

                2B)  To take the script option a step further, you can also add two script triggers to your layout - OnLayoutEnter and OnModeEnter - and call the same script.  That will automatically execute the script when you navigate to the layout for OnLayoutEnter or when switching from Find mode to Browse mode for OnModeEnter.  I would suggest keeping the button on the layout regardless since you may need to refresh the sort order on occasion.

                 

                I trust you find these suggestions helpful.  Best of luck and happy scripting.


                TW