6 Replies Latest reply on Oct 30, 2012 4:31 AM by RickyFarr

    Multiple Table Import

    RickyFarr

      Title

      Multiple Table Import

      Post

           Dear All,

           I thought I was finally getting somewhere and managed to get the database structure we needed and working on the ipad. 

           We tried to import our database after exporting it from FMGo - to find that only 1 table was being imported. Is it true that you can only import 1 table with FM Pro and need FM Pro Advanced if you want to import multiple?

           If so FM Pro Advanced is not a possability at the moment.... therefore we will have to get all our data in 1 table - just one glitch...

           We have almost 90 procedures in a list - in our previous version we used a portal to add a line for each procedure selected (as multiple procedures are done). Now if we cannot import multiple tables, portals wont work - therefore is there a way around this issue?

           Is it possible to select a procedure from a list and have a button copy the selection and paste it into a new field... then if a different procedure was selected by pressing the button again it updates the field with the two selected procedures added?

           I feel I am going round and round in circles..... I get something working, just to find another hurdle lol

           Thanks in advance 

           rick.

        • 1. Re: Multiple Table Import
          Sorbsbuster

               You can script the importing of a complete multi-table database (copy) into the same complete (central) copy of the multi-table file.  The script has to drive to the correct layout (for the destination table) and then in the import dialogue choose the correct source table.

               Before you script this (if this is for the same veterinary application as your other posts) then note that you can write this to happen at the romote point of connection.  You don't have to e-mail it back to base and have the problems of then finding the right source file, never mind source table.

                

               Eit: and note that if you are using portals then you are presumbaly creating child records.  That was one of the scenarios where I suggested that it gets away from the dead easy 'we are only sending data one way', and moves into having to re-allocate correctly the newly-allocated system ID for the parent record to each child record (potentially: depending on how you allocate the Parent IDs in the first place.)

          • 2. Re: Multiple Table Import
            philmodjunk

                 Actually, only manual import records operations require going to a layout based on the target table before importing. From a script you do not need to go to each layout unless there is some "post import" processing of the imported records, such as determining a max serial number value for updating that setting.

            • 3. Re: Multiple Table Import
              RickyFarr

                    

                   Dear Sorsbuster,

                   Thank you for your quick and informative replies...

                   I would like to attain several things you say are possible:

                   1 - You can script the importing of a complete multi-table database (copy) into the same complete (central) copy of the multi-table file.  The script has to drive to the correct layout (for the destination table) and then in the import dialogue choose the correct source table.

                   Before you script this (if this is for the same veterinary application as your other posts) then note that you can write this to happen at the romote point of connection.  You don't have to e-mail it back to base and have the problems of then finding the right source file, never mind source table.

                   and......
                    
                   2 - I just noticed that you said they were e-mailing their copy back to base.  They don't have to do that; the update can be scripted to upload directly to the central file from wherever they have the internet connection they are using to send the e-mail.
                    

                    

                   These two points would make our current problem a realty + convince my boss that the concept of remote management / billing is a physical and realistic possability.

                   Pointers / advice / places to start and learn how to do the two steps above would be greatly appreciated. 

                   If this works I owe you big time - free vet advice can be thrown in for good measure!

                   Thanks again,

                   Rick.

                   p.s. we are not running FM Server, Just FM Pro from a single machine in the office.

                   p.p.s This is the third time I tried to post this.... for some reason each post comes up blank when submitted!

              • 4. Re: Multiple Table Import
                RickyFarr

                      

                     Dear Sorsbuster,

                     After spending a further day on google, FM forums and watching far too many youtube videos on FM scripting I am still at a dead end on how to do what you quoted the other day:

                     

                          You can script the importing of a complete multi-table database (copy) into the same complete (central) copy of the multi-table file.  The script has to drive to the correct layout (for the destination table) and then in the import dialogue choose the correct source table.

                     

                          Before you script this (if this is for the same veterinary application as your other posts) then note that you can write this to happen at the romote point of connection.  You don't have to e-mail it back to base and have the problems of then finding the right source file, never mind source table.

                     I still have 4 days to prove to my boss this can work - any help would be greatly appreciated.
                      
                     Rick.
                • 5. Re: Multiple Table Import
                  Sorbsbuster

                       With a deadline like that you can still go ahead with the e-mailing option.  It just isn't as elegant and has the potential for more (human) errors, but it should not jeopardise the overall proof-of-concept.

                       I can't know how familiar you are with each of the concepts of Filemaker.  This technique covers a few of them, not least of which is setting up your central file to be accessible from anywhere outside your network.  We've also been working on this project for some time; it is a big challenge (although we are setting the bar at genuine synchronisation, if we can achieve it).  It is not easy, as Phil said.

                       The principles are:

                       - set up your Central File to be accessible over the web.  (You do not have to use IWP - in fact, don't use IWP.)  For this example I'll call it Vet Database. To do that you need to set up port-forwarding on your router.  You need to have a fixed public IP, or another work-around (such as No-IP)
                       - Create a copy of the file, call it 'Vet Database Central', and put it in the same folder as the Vet Database.
                       - In the Vet Database go to the relationship diagram and add as External Data Sources each of the tables from Vet Database Central.
                       - You now have direct access from the Vet Database (which will be the local version the vets bring with them) and the main database tables. They look exactly the same, are even called the same thing - they are indeed copies of exactly the same file (structure) but they are distinct different files, in different locations.
                       - Set up scripts to import from each table in your Vet Database (the local copy on the iPad) into the equivalent table you added from Vet Database Central.
                       - Cycle through the tables and import the records.
                       - Some tricky bits now (as I suggested before):

                       - having sent the records back to the Central File, are you going to delete them locally, or tag them as 'Exported', so as not to export them again?  You need to check for a connection back to the Central File first, to make sure that you don't delete them when in fact they were never exported.  It is the same problem as you must check for when importing after e-mailing.

                       - you said that you may be going to have Parent and Child records raised locally.  You need to cover if you are going to have all records issued with a unique universal ID, and that is used everywhere, or whether the Central File will allocate each record the unique 'System' ID upon import.  If so, you have to put in a prodecure to change the Parent ID in the imported Parent Records, and their associated Child Records that were raised locally.

                       One of the things you want to avoid is to literally have two versions of the file - a structure you use Centrally, and a slightly modified structure you use 'remotely'.  If they are even slightly different you will end up doing all development work twice - once in each file.  So you want to have one universal file that can act as either central or remote.  For example, when you created the Vet Database Central copy and loaded that as an external source, it works at the central PC.  But you don't want the remote iPads to carry that 2nd copy with them.  When they go looking for 'Vet Database Central' you want them to search for the one that is on the web, back in your HQ.  So you use File - Manage - External Data Sources and make a list of sources.  You start with the Vet Database Central (so that the central file will see it and be happy) but you add a second one below it (like fmnet:/123.456.7.8 , being the public IP of your HQ's router).  When the iPad can't find any copy of the 'Central' database on itself it shifts on down the list and will find the real HQ-Central file, as long as you have a web connection.

                       I don't know how many of the tricks and techniques are familiar to you.  Like everything, they are 'easy when you know how' (and sometimes not so easy when 'we only thought we knew how'), but there are a lot of techniques piled in to your project.  If it works when you e-mail the file - then do that.  Get the rest of it working (interface etc) and you will always be able to add the direct import later.  Probably around the same time as your colleagues get enthusiastic and want to have all notes available at all times to every iPad...

                        

                  • 6. Re: Multiple Table Import
                    RickyFarr

                          

                         Thank you for all your advice - it feels like my first few years back at uni.... a required steep learning curve with a short deadline!

                         The concepts you have discussed are all relatievly new to me, however I pride myself in being stubborn and persistent - therefore learning is a natural progression.

                         We will be sticking with the email option for the next few weeks (until I have time to work on the remote version).

                         Your quote of:

                         

                              - Set up scripts to import from each table in your Vet Database (the local copy on the iPad) into the equivalent table you added from Vet Database Central.
                              - Cycle through the tables and import the records.

                         is still proving a bit tricky, but its a slow work in progress.

                         I just wanted to say thank you again and I am sure you will probably see posts in the future from me trying to perfect this veterinary app into a viable business solution - all whilst working an 70-100hr week in my day job lol.

                         Kindest Regards,

                         Rick.