1 2 Previous Next 29 Replies Latest reply on Feb 28, 2013 9:13 AM by philmodjunk

    Issue with Export Script

    NaturSalus

      Title

      Issue with Export Script

      Post

            

           Hello,

           I have the follwoing Export script that is triggered by the user from a button,


            

      # Error Handling

           Allow User Abort [ Off ]

           Set Error Capture [ On ]

      # Setting File Path

           Set Variable [ $FilePath; Value:"file:" & Get ( DesktopPath ) & "bfsv3sp.mer" ]

      # Export Appointment records

           Go to Layout [ “Appointment_Detail” (Appointment) ]

           If [ Get ( FoundCount ) ≠ 0 ]

           Show All Records

           Export Records [ File Name: “$FilePath”; Character Set: “Windows (ANSI)”; Field Order: Appointment::__kp_Appointment

           Appointment::_kf_Patient

           Appointment::AppointmentDate

           ...

           [ No dialog ]

           End If

      # Export Patient records

           Go to Layout [ “Patient_Detail” (Patient) ]

           If [ Get ( FoundCount ) ≠ 0 ]

           Show All Records

           Export Records [ File Name: “$FilePath”; Character Set: “Windows (ANSI)”; Field Order: Patient::__kp_Patient

           Patient::PatientNameFirst

           Patient::PatientNameFirstLast

           Patient::PatientNameLast

           …

           [ No dialog ]

           End If


            

           following the same strategy the next tables are exported


            

      # Export ManufacturerMedicine records

           Go to Layout [ “dev_ManufacturerMedicine” (ManufacturerMedicine) ]

           If [ Get ( FoundCount ) ≠ 0 ]

           Show All Records

           Export Records [ File Name: “$FilePath”; Character Set: “Windows (ANSI)”; Field Order: ManufacturerMedicine::

           __Kp_ManufacturerMedicine

           ManufacturerMedicine::_Kf_Manufacturer

           ManufacturerMedicine::_Kf_Medicine ]

           [ No dialog ]

           End If

           Go to Layout [ original layout ]


            

           I have run the Export script through the debbuger and is seems to work fine and no errors showed up. However, when I try to import manually the exported file from an empty application the only fields listed in the Import File Mapping Window are those ManufacturerMedicine table that is the last table that was exported.

            

           To be able to see what was exported, I exported to a .cvs file instead of to a .merg file and I could check that export file contained only the records of the ManufacturerMedicine table. This is telling me that there is a mistake in my Export script.


           Where is my mistake?

            

           Thanks,

            

           natursalus

      import_Screen.jpg

        • 1. Re: Issue with Export Script
          philmodjunk

               Each export is exporting data to the exact same filename at the exact same location. Each Export Records step overwrites the file created by the previous export. You need to modify your script so that each table is exported to a different file name.

          • 2. Re: Issue with Export Script
            NaturSalus

                 Hello Phil,

                 Thanks for looking into my question

            Each export is exporting data to the exact same filename at the exact same location. Each Export Records step overwrites the file created by the previous export. You need to modify your script so that each table is exported to a different file name.

                 I see, that explains "my problem"

                 But if each table is exported to a different file name,this means that if I want to export the15 tables of project A, I will end up with 15 different files on my desktop?

                 Can't I just export the 15 tables to just one file?

                  

            • 3. Re: Issue with Export Script
              philmodjunk

                   Yep, 15 tables means 15 files.

                   You cannot.export all the data to a single file.

                   Think about it. The number and names of your columns are likely to be different with each file. FileMaker would be unable to import data from such a text file due to those differences and how would it know which data goes with a given table?

              • 4. Re: Issue with Export Script
                NaturSalus

                      

                Yep, 15 tables means 15 files.

                You cannot.export all the data to a single file.

                      

                     You must be kidding. These are devastating bad news!

                      

                Think about it. The number and names of your columns are likely to be different with each file. FileMaker would be unable to import data from such a text file due to those differences and how would it know which data goes with a given table?

                      If this is so, what is the purpose of the export script?

                     If they haven't figure out the way to do what I was expecting, why not just remove the export script from FM and just say to FM users: " if you want to export all the tables of a project just make a copy of the project"

                     Are we in 2013 or 1713?

                      

                     Thanks for opening my eyes to the shortcomings of FM

                      

                     natursalus

                      

                • 5. Re: Issue with Export Script
                  philmodjunk

                       I really don't see this as a shortcoming. Why is this "devastating news"?

                       If you want to save the data all in one file, don't export it as merge text, csv or tab.

                       Export it as a FileMaker file with one table for each table of data that you want to export.

                  • 6. Re: Issue with Export Script
                    NaturSalus

                         When you say:

                          

                    If you want to save the data all in one file, don't export it as merge text, csv or tab.

                    Export it as a FileMaker file with one table for each table of data that you want to export.

                          

                         Should I undersand that based on what I want to achieve (exporting all the table of project A into just one file), I should modify my Export script from what I showed before to the following?

                          

                          

                    # Error Handling

                         Allow User Abort [ Off ]

                         Set Error Capture [ On ]

                    # Setting File Path

                         Set Variable [ $FilePath; Value:"file:" & Get ( DesktopPath ) & "bfsv3sp.fp7" ]

                    # Export Appointment records

                         Go to Layout [ “Appointment_Detail” (Appointment) ]

                         If [ Get ( FoundCount ) ≠ 0 ]

                         Show All Records

                         Export Records [ File Name: “$FilePath”; Character Set: “Unicode (UTF-16)”; Field Order: Appointment::__kp_Appointment

                         Appointment::_kf_Patient

                         Appointment::AppointmentDate

                    File Type: FileMaker Pro Files (*.fp7)

                         ...

                         [ No dialog ]

                         End If

                    # Export Patient records

                         Go to Layout [ “Patient_Detail” (Patient) ]

                         If [ Get ( FoundCount ) ≠ 0 ]

                         Show All Records

                         Export Records [ File Name: “$FilePath”; Character Set: “Unicode (UTF-16)”; Field Order: Patient::__kp_Patient

                         Patient::PatientNameFirst

                         Patient::PatientNameFirstLast

                         Patient::PatientNameLast

                    File Type: FileMaker Pro Files (*.fp7)

                         …

                         [ No dialog ]

                         End If


                          

                         following the same strategy the next tables are exported


                          

                    # Export ManufacturerMedicine records

                         Go to Layout [ “dev_ManufacturerMedicine” (ManufacturerMedicine) ]

                         If [ Get ( FoundCount ) ≠ 0 ]

                         Show All Records

                         Export Records [ File Name: “$FilePath”; Character Set: “Unicode (UTF-16)”; Field Order: ManufacturerMedicine::

                         __Kp_ManufacturerMedicine

                         ManufacturerMedicine::_Kf_Manufacturer

                         ManufacturerMedicine::_Kf_Medicine ]

                         [ No dialog ]

                    File Type: FileMaker Pro Files (*.fp7)

                         End If

                         Go to Layout [ original layout ]

                          

                          

                    • 7. Re: Issue with Export Script
                      philmodjunk

                           That still produces separate files.

                           You'd have to use this method:

                           Use New Database to create a new FileMaker file. If you have advanced, copy and paste your tables from your original file into this new file. No need for any added layouts etc. in this file but a script that saves a cloned copy of this file could be useful.

                           You'd then use one of two methods:

                           1) Create an import records script that imports the desired data from each of your tables in your original flie into this copy.

                           2) Create table occurrences with external data source references to each of the tables in this new file. Create the Import Records script in your new file, using references to these added table occurrences to import your data.

                           But before we go any further down that path, what is the purpose for this data export process in the first place? (Knowing the reason why can help me suggest better alternatives if any exist.)

                      • 8. Re: Issue with Export Script
                        NaturSalus

                             My apologies but both suggestions look very convoluted to me.

                             Let me explain what I am trying to do.

                             I have an application based on FM 11 Pro Advanced. Since I don't like the present incarnation of FM 12 (just have a look at your buggs database for the reasons) I don't want to offer my application based on FM 12. But I must have a secure way to import data based on FM 11 into an application based on FM 13 or FM 14.

                             Under this scenario I thought that the best option was to export all the data of the application based on FM11 in a format that could be imported from a future application based on FM 13 or Fm 14.

                             Any ideas about how to achieve this?

                              

                              

                              

                        • 9. Re: Issue with Export Script
                          philmodjunk

                               My suggestion is to export the data as separate merge files. Since importing the data into the new file has to be imported table by table, I don't see any major issues with generating one data file for each table.

                          • 10. Re: Issue with Export Script
                            NaturSalus

                            My suggestion is to export the data as separate merge files. Since importing the data into the new file has to be imported table by table, I don't see any major issues with generating one data file for each table.

                                 Phil, the issue comes from the client point of view. How do you explain to him / her that his/her desktop is going to be full of files and that for upgrading they will have to collect each one of them.

                                 It is hard to understand why the skillful coders of FMi can't come up with a convenient way of transferring all data from one application to another when both have different extensions.

                                 thanks

                                  

                            • 11. Re: Issue with Export Script
                              philmodjunk

                                   Why would they ever even see these files let alone "collect" them?

                                   The process of exporting and importing the data can be fully automatic. The user clicks a button in the original to export the data, then opens the upgrade copy and clicks a button to pull it all into the new version.

                              • 12. Re: Issue with Export Script
                                NaturSalus

                                      

                                Why would they ever even see these files let alone "collect" them?

                                The process of exporting and importing the data can be fully automatic. The user clicks a button in the original to export the data, then opens the upgrade copy and clicks a button to pull it all into the new version.

                                      

                                     Sounds good to me.

                                      

                                     First point. Hiding the exported files from the user view.

                                     I can think of two options:

                                      

                                     First option, all exported files are kept on the Desktop under the Export folder. 

                                       
                                •           Pros: the same export pathfile can be used for windows and mac systems. The user actually sees the result of the export action
                                •      
                                •           Cons: I don't think you can script the creation of a folder from FM. And if there is a way I woul like to know it.

                                      

                                     Second option. All exported files are kept in a system folder.

                                       
                                •           Pros: asthetically it is the most professional option.
                                •      
                                •           Cons: dynamic file paths ought to be generated by the Export script and both for Windows and Mac OS. I know some of the pieces of this puzzle [Get(SystemPlatform; Get(SystemDrive); Set Variable ($FilePath; Value: Select File); etc] but I am not quite sure how to make it work right.

                                      

                                     Second point. Modifying the Export script.

                                     No issue as long as I know where to put the exported files ( on a dedicated folder either on the desktop or under FM folder) and how to set up the file path.

                                     I guess this is a good oportunity for another "tutoring" FM session on your part, if you have the stamina...

                                • 13. Re: Issue with Export Script
                                  philmodjunk

                                       I think this could easily be done by exporting to temporary items. I've never needed to test this, but assume that temporary items persist until the computer is restarted. If the persist only until the creating application quits, you'd need to use a different location. This is also a standard location so it will work for mac and windows systems.

                                       My second choice would be a folder inside the documents folder as this is both a standard location and the one directory where we can currently use just FileMaker to get a list of the file contents--which might be a nice option here.

                                       I routinely create folders and delete files and folders from FileMaker, but I kick off system specific batch files to do so.

                                        

                                  • 14. Re: Issue with Export Script
                                    NaturSalus

                                    My second choice would be a folder inside the documents folder as this is both a standard location and the one directory where we can currently use just FileMaker to get a list of the file contents--which might be a nice option here.

                                    I routinely create folders and delete files and folders from FileMaker, but I kick off system specific batch files to do so.

                                          

                                         I see...

                                         FM  "send event" script step

                                         and

                                           
                                    •           Applescript for the Mac OS X
                                    •      
                                    •           Cmd scripting or Visual Basic scripting for Windows OS

                                          

                                          

                                    I think this could easily be done by exporting to temporary items. I've never needed to test this, but assume that temporary items persist until the computer is restarted. If the persist only until the creating application quits, you'd need to use a different location. This is also a standard location so it will work for mac and windows systems.

                                         Not quite sure what you mean by "temporary items". But another way to look at it is the following:

                                           
                                    •           first create a new folder,
                                    •      
                                    •           then export the zillion export files to it,
                                    •      
                                    •           then import the zillion export files into the upgrade project, and
                                    •      
                                    •           finally delete the newly created folder.

                                         So it ends up being a refinement of your second approach.

                                          

                                         Thanks,

                                          

                                         natursalus

                                    1 2 Previous Next