8 Replies Latest reply on Feb 25, 2011 2:40 PM by philmodjunk

    Import script that imports more files than what is present....

    Mitch

      Title

      Import script that imports more files than what is present....

      Post

      I have a FMP 11 database I have been using for some time, and when I import data using an import script from a back up .USR file from an older runtime version of the SAME database it works fine, importing 19 out of 19 records, plus portal records.

      If I save a back up copy of the FMP 11 database with the new data, in either a .fp7 or rename it to a .USR file, the database will flash a window saying it is importing a thousand or so records with a progress bar, counting down the records, when its finished the window and progress bar appears again saying it is importing several thousand records....  When its all finished, I end up with 19 out of 19 records, plus portal records...

      There has been a minor change to the database and the import script to the FMP 11 database since the runtime version was made. Additions to the database:

       

      Main_Page::Main_Text    

      Main_Page::New_Text

       

      Main_Text is a hidden calculated text field that combines both the data from and existing Text_Body and Text_Summary into a single field. New_Text is merely a new editable text field.  The change to the import scripts is a Loop (after the main import step) as follows:

      Go to Record First

      Loop

       If [not IsEmpty (Main_Page::Main_Text)]

       Set Variable [$MainText; Value:Main_Page::Main_Text]

       Set Field [Main_Page::New_Text; $MainText]

       End If

      Go to Record [Next; Exit after last]

      End Loop

      Replace Field Contents [No dialog; Main_Page::Text_Body;""]

      Replace Field Contents [No dialog; Main_Page::Text_Summary;""]

      Apart from this the additional fields and import scirpt on both verisions of the database are the same.

      The intention is that if the Main_Text field has data in it, the data is copied into the New_Text field.  Then the data in Text_Body and Text_Summary are deleted.  Any other subsequent imports will not alter or update the New_Text field, because the Main_Text will be empty due do an empty Text_Body and Text_Summary... well, like I said, that is the intention.

      I assume I have stuffed up the script, but the solution is beyond me...

        • 1. Re: Import script that imports more files than what is present....
          Mitch

          More...

          I have just created a runtime from the FMP 11 database (with the addition to the import script detailed above).  Then I used this database to import from the back up .USR file of the older runtime version.  No issues, it imports 19 out of 19 records, plus portal records.

          I then made a back up .USR file of the new runtime.  Then imported the back up file into the database.  The database then said it was importing over 30,000 records, when finished it then said it was importing more than 90,000 records.  After 40 or so seconds, it finished and I ended up with the original 19 out of 19 records, plus portal records.

          Obviously there is a portal or some other records that keeps adding records to the database that I do not know of...?

          • 2. Re: Import script that imports more files than what is present....
            philmodjunk

            Several things to check:

            When you import from a closed file, FileMaker imports all the records from the specified table--not just the found set. When you import from an open file, you get just the found set.

            If there is a validation rule such as a unique values setting, you might get a situation where you import 1000's of records but only a few make it past the validation rule and actually get imported into the table.

            With these type of scripts, the Matching Names field mapping option is by far the safest to use. It can help you avoid this truly nasty bug that has existed for many years in FileMaker Pro:    Data loss bug : Spontaneous and erroneous import matching of new fields in specified imports !

            This is one of many acknowledged bugs that can be found in the Known Bug List here in the Report an Issue section of the forum.

            It can also be downloaded as a database file from:   http://www.4shared.com/file/8orL8apk/FMP_Bugs.html

            • 3. Re: Import script that imports more files than what is present....
              Mitch

              Hi Phil,

              I will take a look at all these.  I just can not work out why it will do an import, then another one..?  

              I believe there may be a large amount of records that have been gradually added to the database during the repeated imports to test the database whilst developing it.  These should have have been deleted prior to import and the Serial Number Re-Set imbedded in the import script.  But this does not explain two imports...

              I read the bug report.  I actually have had my 'Matching Names' option change to "Last Order', even though the script was saved "Matching Names'.  This happened all by itself after importing Scripts from an older database.

              • 4. Re: Import script that imports more files than what is present....
                philmodjunk

                this does not explain two imports...

                You didn't post the script that actually performs the imports so it's impossible to tell from here.

                When its all finished, I end up with 19 out of 19 records, plus portal records...

                Are you saying that you are importing 19 records into one table and the "portal records" into another? That would require two imports, one into each table unless this portal either points to previously existing records or is based on a "self join" relationship back to the same table.

                • 5. Re: Import script that imports more files than what is present....
                  Mitch

                  Well, I have found one issue so far.  

                  In the import script one of the portal records was not replacing the Serial ID with the imported value.  I assume this was just accumulating ID Numbers within the portal...?

                  • 6. Re: Import script that imports more files than what is present....
                    philmodjunk

                    There's no way I can answer that from the info in this thread... I'd need to see how the script is set up--particularly what options were specified in the Import Step.

                    • 7. Re: Import script that imports more files than what is present....
                      Mitch

                      Phil,

                      Go to Layout["Items_Portal" (Items_Portal)]

                      Import Records [No dialog; "$FullPath"; Add; Mac Roman]

                      Got to Record [Last]

                      Unsort Records

                      Set Next Serial Value [Image_Portal::Image_ID; SerialIncrement (Image_Portal::Image_ID; 1)]

                      This was one of 10 or so import steps for 10 layouts and portals.  In this particular script the layout set was Items_Portal, however the next serial value was set for Image_Portal.   

                      • 8. Re: Import script that imports more files than what is present....
                        philmodjunk

                        Try:

                        Go to Layout["Items_Portal" (Items_Portal)]
                        Import Records [No dialog; "$FullPath"; Add; Mac Roman]
                        Unsort Records
                        Go to Record [Last]
                        Set Next Serial Value [Image_Portal::Image_ID; SerialIncrement (Image_Portal::Image_ID; 1)]

                        And it looks like the set next serial value refers to the wrong table and field for this set of records just imported.