13 Replies Latest reply on Nov 24, 2015 1:32 PM by keywords

    Export a Record From One Table to Another

    jprochniak

      Ok Filemaker folks, I've combed the community and have been unable to come up with the solution to what I'm trying to do and hoping that you can help. I'm basically trying to export a record from one table to another through use of a button.

       

      Context:

      I have a table, let's call it Table A, in which pilot training records, both data (various demographics) and containers (scanned PDFs of documentation), are kept. The data kept in Table A is only the most current training information. I have also created a second table, let's call it Table B, in which historic pilot training data is kept. This historic data must be kept for regulatory reasons.

       

      Problem:

      What I'd like to do is create a button by which the data and containers in Table A are moved to Table B so that it can be archived, leaving the exported fields blank on Table A and able to be filled with the new information.

       

      Does anyone have any thoughts? Thanks in advance!

       

      John

        • 1. Re: Export a Record From One Table to Another
          keywords

          In my view this would best be accomplished working from within Table B, as importing is easier to manage than exporting—you have to export records as a file of some sort, whereas you can import records directly from another file or table.

          Your requirements would include:

          1.     In Table B, setting up Table A as an external data source.

          2.     Create a script in Table A to find the set of records you wish to move to Table B.

          3.     Create another script in Table A to delete the found set of records—if in fact that is what you intend to do.

          4.     Create a script in Table B to run the whole process. This script will: (1) call the Find script in Table A; (2) import the records, as per your preset import mapping; (3) call the Delete script in Table A.

          If you wish to run this process from Table A, then do the following in addition:

          5.     In Table A, set up Table B as an external data source.

          2.     Create a script in Table A to call the Import script described above in Table B.

          I think that should work.

          • 2. Re: Export a Record From One Table to Another
            monkeybreadsoftware

            you can check our plugin command FM.InsertRecordQuery which works very efficient to copy from one table to another.

            No relation needed and can be in different files.

             

            http://www.mbsplugins.net/archive/2015-04-30/Quickly_copy_lots_of_records/monkeybreadsoftware_blog_archive

            • 3. Re: Export a Record From One Table to Another
              jprochniak

              Thanks Keywords.

               

              That's kind of the plan that I was formulating in my head. As for importing the records versus exporting them, for my application I'm thinking that exporting them would be the way to go mainly for the reason that there are many different training records, medical certificates, etc associated with our numerous pilots, all of which are superseded at various times of the year, vary from person to person, and are all part of one record. That being said, I'm thinking it would be a lot more user friendly to just be able to hit the "export" button on a person's medical certificate (selecting only certain cells to create the new record in the new table), for example, and fill in the details with the new info than go to the target table and search for the various cells. Does that make sense?

              • 4. Re: Export a Record From One Table to Another
                ZoocMan85

                Here is a script i just wrote for something similar. Substitute my layout names and fields. You will get the idea.

                 

                 

                 

                Check out PDF i exported for you.

                • 5. Re: Export a Record From One Table to Another
                  jprochniak

                  Thanks ZoocMan85. Nothing came through though.

                  • 6. Re: Export a Record From One Table to Another
                    RickWhitelaw

                    I'm missing something here. Why have Table B at all? Why can't all the data be kept in Table A? Like I said, it could be that I'm missing something . . .

                    • 7. Re: Export a Record From One Table to Another
                      ZoocMan85

                      Post your script Jprochniak.

                      • 8. Re: Export a Record From One Table to Another
                        jprochniak

                        Sorry time-saver, I may not have explained myself well.....it sure sounded good in my head. The main purpose for the second table (Table B) is to act as an archive and store historic training records while the purpose of Table A is to store only the most current training records, personnel details, contact information, etc. Since the number of training records associated with an individual increases based on the number of years that individual is employed, it seemed far easier to make a separate table for the archived records than anually adding fields in Table A to accomidate individuals' growing list of historic records. I'm definitely open to suggestions on how to tackle this issue.

                        • 9. Re: Export a Record From One Table to Another
                          BruceRobertson

                          Why add fields? How many fields have you got now?

                          Sounds like quite possibly a design problem, and perhaps a need for an EAV data model.

                          • 10. Re: Export a Record From One Table to Another
                            jprochniak

                            Basically so I'd be able to search for the individual pilot records in the future and differentiate one document from another. For example, a pilot has at least two flight evaluations in a year, at least one medical certificate, and numerous training certificates (i.e. ground training, flight training, hoist training, etc). These documents are currently scanned in and kept in a container field in Table A. The most current of all of the pilot records is kept in Table A and immediately accessible as it is the only one that is currently relevant. The expiration/supercede date on all of these pilot records is different from person to person. Once a person obtains a new medical, flight check, etc I need to archive the old record/document and basically keep it forever for later reference. In the historic record table, Table B, I need to be able to search for pilot medicals, for example, and in turn generate a list of just the pilot medicals. Same thing with flight evaluations. I'd like to be able to pull up each individual record rather than one record with a bunch of containers holding the associated PDF documents. In that regard, I'm essentially taking a few different fields at a time from Table A and using that data to creat another record in Table B.

                             

                            I hope that makes sense!

                            • 11. Re: Export a Record From One Table to Another
                              keywords

                              Another two bob's worth from me:

                              It seems, from this latest description, as if it would be worth considering a different model. As I see it, you need at least—

                              1.     A Pilot table, which holds all the personal details (this may also hold contact detail—address/phone/email—but there could be an argument for this to be in another table)

                              2.     An Examination table, which holds all the details of training that is undertaken—type, date, expiry, marks, notes, etc

                              3.     A Certificate table, which holds only the scanned documents and any relevant foreign keys—there may be a case for this table to be in its own separate file, but with external storage that's probably not necessary

                               

                              Well set up, this would allow you to see, in the Examination table only current stuff (i.e. expiry date later than current date) or any other found set you want to view. In the Pilot table you would be easily able to view all Examination history in a portal, which you could sort to show current stuff at the top.

                               

                              Unless there is really good reason to remove expired records to their own file (like the file getting bloated with masses of records) then why not consider just keeping it all together, and using expiry date as a way to make current data as your basic found set?

                              • 12. Re: Export a Record From One Table to Another
                                jprochniak

                                Thanks keywords. We're actually going through the process of splitting the tables up as you recommended. I seem to recall having a thought about doing this initially but, for whatever reason, opted to not follow through with it. Once we started laying everything out on paper your suggestion made a lot more sense than what I was trying to do. I guess I should have stuck with my original thought.

                                 

                                Thanks for the nudge back in the right direction!

                                • 13. Re: Export a Record From One Table to Another
                                  keywords

                                  Glad to have helped!