3 Replies Latest reply on Jul 19, 2010 3:09 PM by philmodjunk

    Import ONLY selected record(s) from other table in sep. db?



      Import ONLY selected record(s) from other table in sep. db?


      Hi all,

      I'm using Filemaker Pro 11.  Hopefully someone can (please) help me with this.

      I have a file, say DB1, and it has a table, say DB1Table, that is related to another table in another file, say DB2, table DB2Table.

      These tables are related and all is working just fine.

      At a point in time, when certain conditions are met, specific records from DB2Table will need to be imported into DB1Table. 

      I do not want them removed from DB1Table – the import function is exactly what I need. Problem is, ALL of the records in DB2Table will not meet the specific conditions, and I don’t want all of them imported – only the ones that meet my criteria. 

      This won’t be too often, but often enough that it’s a hassle to enter the info manually into DB1Table.

      I have all of the related records showing in a portal when I’m in a layout in DB1, so I can easily see which records I need to be imported.  I can do them one at a time (they only need to be imported once – they will not need to be updated) when I see the conditions are met, which would be best (maybe a button to run the script in the portal row??), or a script to import the entire found set is fine.

      Can this be done?  If there is another way, not using the import function, please clue me in!  I’ve looked all over and I can’t figure out how to get my script to import ONLY the records I need and not all that exist within the DB2Table. 

      PLEASE help!  Ty!!!

        • 1. Re: Import ONLY selected record(s) from other table in sep. db?

          Import records, when importing records from a file that is already open, imports the current found set of the source table. All you need to do is create a script that sets up the correct found set of records before importing.

          Since you can see the records in a portal. Go To Related Records would appear to be a good option for this. Here's a rough outline of the script:

          1. Use an If statement with IsEmpty ( Portaltable::IDfield ) to make sure that there is at least one record visible in the portal.
          2. If there is, use Go To Related records, specifying Show only related records for the current record and also specifiying a layout based on the portal table.
          3. Then use Import Records to copy the records over to the target table.

          Import records can result in large scale changes and/or additions to your database so make a back up copy before testing your script. There's a known bug in import records that can have a catastrophic effect on your data if your import does not use the matching records option and you make changes to a table design: Data loss bug : Spontaneous and erroneous import matching of new fields in specified imports !

          Scripts that use Go To Related Records without checking and trapping for situations where there are no related records to go to can also result in major, unexpected results for your database:  See filemaker help if you are using filemaker 11. For other versions or to read more about go to related records, see:  The Complete Go To Related Record



          • 2. Re: Import ONLY selected record(s) from other table in sep. db?

            thanks so much for the help, Phil.. i was fiddling with this all last night and had originally tried it this way, but i must be missing something because its still importing all the records.  ive attached a screenshot. bottom right is my script, top left is what opens when i use the go to related records and then constrain the found set to find the related records that meet my criteria. 


            You can see in the background (bottom is my portal to DB2Table, top  is my DB1Table portal) that my script is still importing all of the  records (there are 3 test records in total in DB2Table, only 2 are  visible because the other is unrelated.) in DB2Table.  Could you please  look over my script and tell me what I'm doing wrong?

            Also, ty for  the heads up on the errors.  I incorporated the isempty if statement as  you suggested.

            Thanks again!

            • 3. Re: Import ONLY selected record(s) from other table in sep. db?

              Please read the link on Go To Related records or look it up in filemaker help if you have filemaker 11.

              You script should be something like this:

              Freeze Window
              If [ not IsEmpty ( Incoming Proposals::Proposal No) /* check for the existance of related records */ ]
                 Go To Related Records ( Show only related records; From table: "incoming proposals"; Using layout: "IncomingProposals"
                 //Don't use the new window option, you don't need it and it may interfere with having the correct found set for your import records
                  Constrain Found Set [Restore]
                  Go To Layout [original layout]
                  Import Records
              End If

              On a design note, importing records back and forth between tables can be risky as one of the links above documents. It's very possible that the effect you are after here could be achieved without importing records from one table to the other. You might, for example be able to manage your proposal records simply by adding a status field and managing what records are pulled up via finds and/or relationships that include the status field and also with filtered portals.