9 Replies Latest reply on Nov 13, 2015 1:57 AM by tays01s

    Export and import multi-table records: Scripts

    tays01s

      Can anyone share any examples of this, just to see some of the principles?

        • 1. Re: Export and import multi-table records: Scripts
          user19752

          There may be no special technique, need to import on each table.

           

          First import master table(s)

          Then import other tables having foreign keys

          • 2. Re: Export and import multi-table records: Scripts
            tays01s

            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?

            • 3. Re: Export and import multi-table records: Scripts
              user19752

              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.

              • 4. Re: Export and import multi-table records: Scripts
                tays01s

                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

                ii) 2

                iii) 3

                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?

                 

                4. Import:

                - 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).

                • 5. Re: Export and import multi-table records: Scripts
                  tays01s

                  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.

                  • 6. Re: Export and import multi-table records: Scripts
                    user19752

                    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.

                    • 7. Re: Export and import multi-table records: Scripts
                      tays01s

                      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:

                      Loop

                      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 ]

                      End Loop

                      Loop
                      Go 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 ]

                      Go to Record/Request/Page [ Next; Exit after last ]

                      End Loop
                      2. 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.

                      • 8. Re: Export and import multi-table records: Scripts
                        user19752

                        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

                        Export Records[]

                        Go to Related Records["calc"]

                        Export Records[]

                        //completed export from 2 related tables

                        • 9. Re: Export and import multi-table records: Scripts
                          tays01s

                          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.