Can anyone share any examples of this, just to see some of the principles?
There may be no special technique, need to import on each table.
First import master table(s)
Then import other tables having foreign keys
Yes, I understand the to get Master < child tables.
1. It may be incorrect, but my current method of exporting exports 1 or more master files together with dependent child records. I get the need to do an import for each separate table, but my first attempt imports the lot into the Master table. How do you extract records specific to each table? Always assuming it's correct to Master/Child records within the same file.
2. How would the direct the script to import 1 or more files within a folder?
I export multi tables to one file only the case 1:1 or n:1, for external use (not FM).
For FM import, need only left side of table (child) exported. For right side (master), use other files exported from each master files.
Exporting 1:n is not useful for import, since 1 side of fields become empty.
I'm new to this, so just to check what you're saying:
1. External (eg. Excel) use: You can export multiple tables from an FM where child:master is 1:1 or n:1,
2. FM use: Are you saying that you should export a file for records at each 'level'. ie. If I represent Master as level 1 and a, b, c etc as different tables on a particular level I need to export:
1a < 2 < 3< 4a & 4b
1b < 2
In this case would I need to export FM files for:
i) 1a & 1b
iv) 4a & 4b?
3. Export 'signal': I'm guessing that I need a calc field (eg. export = 1) propagated from my Master record through child tables to signal to the script that they should be exported?
- For any particular Master 1a, I'd end up with 4 files to import. I assume I'd need a set filepath list (eg. filepath$/T1, filepath$/T2, etc)? ie. I would not be able to have random patient names if the process were to be automated (ie. I don't want the user to have to find the file).
I see now that I need to export 1 file per table regardless of level and of course ensuring the UUID pk/fk fields are included in each table.
After export on table1, you can use "Go to Related Records" for getting found set on table2 to be exported.
You didn't wrote the purpose for exporting/importing, for synching there need flags for "already synched", other than do it on all records.
Making paths, you can use Get(TemporaryPath) & Get(UUID) & ".anyExtension" for random names never be duplicated.
1. GTRR: I'd been think of using TOs related by an 'export_record=1' field to table from which I'm choosing this list of records to export. You appear to be suggesting using the 'natural' relationships. How would your method alter the syntax below where Patient = Parent, Calc = child:
Go to Related Record [ From table: “Patient”; Using layout: “Patient_L” (Patient) ] [ Show only related records; New window ]
Export Records [ File Name: “$ex_patient” ]
Close Window [ Name: "ex_patient"; Current file ]
Go to Record/Request/Page [ Next; Exit after last ]
LoopGo to Related Record [ From table: “Calc 2”; Using layout: “Calc” (Calc) ] [ Show only related records; New window ]
Export Records [ File Name: “$ex_calc” ] Close Window [ Name: "ex_calc"; Current file ]
End Loop2. Purpose: So that users can export/import records between each other.I wasn't sure what you meant by: "for synching there need flags for "already synched", other than do it on all records." I thought the UUIDs would ensure this?
3. Random names: Thank you. Actually that answers a different Q. What I'd meant was that on importing, I couldn't figure out how to do it automatically, ie. without involving the user browse to find a file(s), except by specifying the exact filepath and that meant I could only use a generic filename known in advance. I didn't know how to grab any/ all filenames waiting in the Import folder and cycle through them.
Sorry I couldn't get how do you choose the records to export, but there is "Match all records in current found set" option for GTRR, then usually you don't need to loop through found set.
//there is found set of "Patient" to export
Go to Related Records["calc"]
//completed export from 2 related tables
1. Choosing export records:
I do this from a 'Front' table/layout using a checkbox field, Front::export_record (ER). Then I've got relationship: Front::ER=Patient::ER.
I have calc fields where Parent::ER=Child::ER down the chain. I was then going to have TOs each relating Front::ER=TO::ER. I'm not sure this is most efficient, but it does work.
2. GTRR: Match all records in current found set: You are of course right. Thanks, that simplifies the script.
3. Error dialogue: This error occurs just after specifying which Patient records to export:
"This operation cannot be performed because one or more relationships between these tables are invalid."
However, if I continue the script, both Patient and Calc files are exported containing the correct records.
Retrieving data ...