12 Replies Latest reply on Mar 13, 2014 9:34 AM by CarrieB.

    Copy record from one file to another



      Copy record from one file to another


           I need a little help getting started here. I want to be able to copy one record from one file to another file and I'm not sure how to get started. I know that you can import records from other files, but I want to make sure I am only importing the one record I want by running a script in the file I'm pulling the record from. Currently, the files are linked via external data sources, but don't have any relationships. Is this pretty basic scripting or will it be very complicated?

           Any tips are MUCH appreciated! :)


        • 1. Re: Copy record from one file to another

               I did figure out a way to do it with a new related table, though I'm sure I probably made it much more complicated than it needed to be. So, I am still interested to hear how other people would do it. :)

          • 2. Re: Copy record from one file to another

                 Often, the best solution is not to copy the record at all. I don't know that this is the case here, but this question is often asked in situations where the developer is trying to keep different groups of records in different tables when they do not need to be, so it is a point that I have to raise.

                 What you describe can be done with import records. A script can isolate the selected record in a found set of just that one record and then import records can import a copy of it into another table. That table can be in the same file or a different file.

                 But you can also move the data via a defined relationship--which appears to be what you are describing and it's a bit of a "toss up" as to which approach is better.

            • 3. Re: Copy record from one file to another

                   I do use "status" fields and things like that to keep records all in the same table where possible. But, in this case, I want to move a record from a completely different FileMaker file. I couldn't figure out how to use a script to isolate the one record that I wanted to import, so that is why I created a related table in the source file. There is always only 1 record in that table.

              • 4. Re: Copy record from one file to another


                     Thank you for your posts.

                     For clarity, assume the record is in your "MAIN" file, and you want to move it to an "ARCHIVE" file.  Here are steps:

                     1. In the ARCHIVE file, create a script called "Import from MAIN" with the following script step:

                     Import Records [ No dialog ; "MAIN.fmp12" ; Add ]

                     2. Switching back to the MAIN file, perform a find so only the current record is found.  One possibility is to create a script with the following three steps:

                     Show All Records
                     Omit Record
                     Show Omitted Only

                     (This shows all records, omits your current record, and then shows only the record you omitted).

                     3. To the end of this script, add the script step "Perform Script".  When you click "Specify...", at the top where it shows "Current File ("MAIN.fmp12"), change this to "Add FileMaker Data Source...".  This will bring up an "Open File" window, where you select the "ARCHIVE.fmp12" file.  Then, select the "Import from MAIN" script selected in step #1.  Since there is a found set of one record, the import will only import the one record.

                     Let me know if you need additional clarification.

                     FileMaker, Inc.

                • 5. Re: Copy record from one file to another

                       Thank you TSGal. I will test out that scenario!

                  • 6. Re: Copy record from one file to another

                         TSGal has shared much of what I was alluding to. But in a hosted database, the Show All/omit/show omitted only method can, if your luck is bad, fail to isolate a single record. This can happen if someone else creates a new record in the same table just after your script omits a record. At that point the new record and your record are omitted and then both appears when Show Omitted Only happen. This is very rare, but can happen.

                         You can also script a find based on a field such as an auto-entered serial number field that will only find the current record and this will not have this slight vulnerability:

                         Set Variable [$ID ; YourTable::serialNumberFIeld ]
                         Enter Find Mode []
                         Set Field [YourTable::Serialnumberfield ; $ID ]
                         Perform Find[]

                    • 7. Re: Copy record from one file to another

                           Phil, I assume I'll avoid that vulnerability if I leave it the way it is with the related table as well, right? Maybe I should leave well enough alone. :)

                           Thanks again for all the advice! I don't know what I would do without the people at this AWESOME forum!

                      • 8. Re: Copy record from one file to another

                             I don't really follow all the details of how you are using that related table, but since the whole point is to set up a single record found set for import records and you aren't using that method with the related table, I see no reason why that would be an issue. But if you want to use import records, note that my example script to handle that issue is quite simple.

                        • 9. Re: Copy record from one file to another

                               I would love to be able to learn to do things more effectively and efficiently instead of feeling like I'm bumbling around inside my database all the time.

                               So any feedback you have on how I did this is appreciated! Following is how I set it up:

                               I have 2 files. I'll call them File 1 and File 2 (which you will see referenced as "Seller Prospecting").

                               In File 1, I created a table called Export Record that has a relationship with Contact Management (original table) via a Contact ID field. In this table, I added all the fields I wanted to export to File 2. Every field looks up it's contents from Contact Management. Then I run the following script. Please let me know if you see something wrong in here or if you think I should convert it to the scripts you all mentioned above. Thanks!

                          • 10. Re: Copy record from one file to another

                                 The only possible issue would be if two or more users tried to run these scripts at the same or almost the same time. You'll have to decide if that's possible.

                                 And isolating the record, then importing would seem simpler to set up.

                                 Out of curiosity, why is it necessary to export records to this separate file? What happens to them next?

                            • 11. Re: Copy record from one file to another

                                   I just typed several paragraphs of answer that got lost in cyberspace. And now I am out of time. Oops! Will reply as soon as I get a chance. Thanks again!

                              • 12. Re: Copy record from one file to another

                                     It's highly unlikely that two users would run the script at the same time. But, not impossible, of course. Which makes me think I will change it to the scripts mentioned above. I've used the "omit/show omitted only" script in other places. I think I will change those as well.

                                     To answer your question about why I want to export the record to another database, let me see if I can explain:

                                     I have a main contact management database that we use to track a potential client from the first appointment, to engaging them, to closing a deal. This database is pretty robust and has a lot of functions.

                                     I have a secondary contact management database where we import prospecting lists from a third party and use them to call on people, trying to get an appointment. There are thousands of records in this database. Only 1 in hundreds will turn into an appointment. So, I suppose I didn't want to gunk up my main database with thousands of records, so I created a separate file to house them in. When we make an appointment with a prospect, they go in the main database, so the export just makes it so you don't have to type the information in manually. :)

                                     Hopefully this makes sense. Thanks again for all the advice!