4 Replies Latest reply on Dec 4, 2011 3:17 AM by Mitch

    Import (Update and Add New Records) Not Working



      Import (Update and Add New Records) Not Working


      The database I that records documetns and articles.  The main table, TITLES containes the ID number (TITLE_ID) and the title of each record (TITLE_TITLE).  Related to this table through ID's is two other tables DOCUMENTS and ARTICLES. These contain most of the record data feild for each article and documents added to teh database, with the expeption of the TITLE and ID field, which is from table TITLES. When I add a new document record in either the DOCUMENTS or ARTICLES layout, a record is automatically created in the TITLES table.

      The process that has led me to this strucure is mentioned here: Change value list based on another field value

      I have now created an import script to update exisiting records and add records from a laptop running a copy of the database.  The function is a typical import loop.

      First, the import loop goes to the TITLES layout and imports records. It then proceeds to the DOCUMENTS layout and imports records.  This works fine as all existing records are updated and new records are added.  Including all the portal records associated with the DOCUMENTS table.  

      The next step in the import loop is to go to the ARTICLES layout and import records.  However, even though there is are new records in the file used for the import, the new ecord does not appear.  But, if I open the ARTICLES layout, and select NEW RECORD, the title data that should have been imported (TITLE, TITLE ID) appears, without the remain data from the ARTICLE table.  Therefore the import did sort of work...well, the TITLES table at least, however it is not shown until I select NEW RECORD.  

      I can not understand why the data is imported ok into the TITLES and DOCUMENT tables, but the ARTICLES table.

      My import Script:


      Go to layout ["TITLES" (TITLES)] 

      Import Records [No dialog; "File"; Uodate exisitin; Mac Roman]

      Unort Records

      Got to Record/Request/Page [Last]

      Set Next Serial Value [TITLES::TITLE_ID; SerialIncrement (TITLES::TITLE_ID;1)]

      Go to layout ["ARTICLES" (ARTICLES)] 

      Import Records [No dialog; "File"; Uodate exisitin; Mac Roman]

      Unort Records

      Got to Record/Request/Page [Last]

      Set Next Serial Value [ARTICLES::ARTICLES_ID; SerialIncrement (ARTICLES::ARTICLES_ID;1)]

      I have tried a number of things to fix this:  

      I have cut down the import script to only import by updating records and adding new record the TITLES table first, then the ARTICLE table second. The same probelm exiists.

      I have changed the imprt scirpt to a plain import records, TITLES table, then ARTICLES.  Same problem exisits.

      I have also removed the 'Set Next Serial' set.  Same problem exists.

        • 1. Re: Import (Update and Add New Records) Not Working

          If I recall correctly, you can manually import the records from Articles and they import successfully? YOu don't see any error messages from the manual import that indicates some records weren't imported? Just checking to see if I am remembering correctly.

          What happens if you take a copy of this script and delete everything but the import records step that imports into articles and run that script?

          Does it work?

          You might try adding a script to "file" that goes to a layout for each of the three tables and does a show all records. Then modify the above script so that it uses a Perform Script step to perform this script in "file" before importing the records. I'm thinking that the first import step opens "file" in the background and that it has an empty found set for articles--which keeps any records from that table from being imported...

          • 2. Re: Import (Update and Add New Records) Not Working

            I have an import scirpt I do use in this database that does work.  This import deletes all records prior to importing.  Then becuase the change to the new database structure (inorporating TITLES and ARTICLES for the puropse of creation a contional value list) has only recently occured, I need to cater for importing old versions of my database that do not yet have this srtucure. Therefore, the import scirpt will import the DOCUMENTS table (old database structure) first, then look for a trigger in one of the fields that will tell me if the import should then either import data into the TITLES table for the very first time, or conitune and the import into TITLES and ARTICLES.  This import works.

            I have edited the new import script that is meant to update records and add new ones; the one I am having issues with, to just do a striaght import, but it does not work.  Records only import into the TITLES table.  No records are imported into the ARTILCES table.

            I believe you are right with the database beliveing that there are no found reocrds.  Hence the data being imported into the TITLES tables and not being visible until I open a new record.  But not sure what you mean with regard to the addtional script.


            • 3. Re: Import (Update and Add New Records) Not Working

              Open the file from which you want to import the data.

              Create this script:

              Go To Layout [//specify layout to table from which you will import the data]
              Show All Records

              Go to your import script in the other file and add a Perform Script step at the begining of it.

              When specifying the script to run, use Add Filemaker data source to find and open the file from which you will import the data. Select this newly created "prepare for import script" as the script it will run.

              Now, when you run the script, it will open the source file and run this script in it to prepare it for your import.

              Please note that I have my doubts this will work. I suspect one of two things has you stymied:

              1) Some detail of the import records step isn't specified correctly

              2) A validation rule on a field in the articles table is keeping any records from importing.

              • 4. Re: Import (Update and Add New Records) Not Working

                I have just imported the data file used to test the new import script (update and add new records) and the data all imported correctly.  

                So, I am going to copy the import loop from the working import script, remove the section that looks for the trigger, then change the import settings to Update Found Records and Add new Records.  Hopefull it should work.