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.
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.
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?
Thanks ZoocMan85. Nothing came through though.
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 . . .
Post your script 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.
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.
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!
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?
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!
Glad to have helped!