1 of 1 people found this helpful
Copy the primary key, go to the other table, set a foreign key and set the fields via a utility relationship.
Or use the Magic Key technique to create one new related record per iteration and "push through" the values. (Basically the same method as above, just "from the other side".)
Or simply import the found set from the scratch table; that should also be the fastest solution, all else being equal.
Export the records to a scratch file and then import the record to the table where you need them. This is pretty fast.
If you want to speed up the script process you can do that as well. Is this process of copying the only way new records get into the destination table?
Setting up the fields with auto enter calcs for the variables saves all the set field steps and requires only a new record be created. It's a little faster. You can also pull all the data in with eSQL and parse that faster that jumping layouts/tables back and forth in the script.
Looks like importing from a table would suit, given the data structure I already have.
In the script however, the import records command only has options to import from a file, as bigtom mentions.
Is there a way to 'import' from one table to another?
My other concern would be what happens to the exported scratch file if I chose that option as there is a large volume of imports happening daily from users.
In this case the export files can be local and unique to each user. That solve the multi user issue.
When you are are done with the import just trash the file. create a new one on the next export.
Do you have the the ability to run the process via PSOS. No need to wait at all then.
I have thought about PSOS yes, so this gives an option as well. Something I will look at.
Couple of ideas here, thanks for the help
Define an External Data Source. That can be (and in your case, is) the very same file you're working in.
Now after you've selected that new data source, you can choose a scratch table TO as the import source.
There is no need to export just to be able to. Import. import records can move records from one table to another in the same file. When asked to specify the file from which to import, select the file you already have open.
Ah, making a little sense now, although not 100%, apologies.
Something like, very simply:
Set Variable [$File ; get(FileName)
Import Records ["$File" ; etc]
Would set the filename initially
I think I have that although will defer if that's wrong.
The issue I'm having next is the actual import mapping dialog screen. I can obviously set the destination table and fields to the right side of the dialog, but the left side representing the source has no options. I'd would have imagined I'd need to do something here so as to get (sic):
Source field 1 > Destination field 1
Source field 2 > Destination field 2
simply because FM at this point would not know which source fields to map to which destination fields?
The script step can't use $path to map the fields. Since the script isn't running yet $path has no value. Click Add File and use it to select the file. After you finish mapping fields, you can go back and put in the $path variable.