1 2 3 Previous Next 34 Replies Latest reply on Jun 29, 2010 6:37 PM by comment_1

    Import->XML Multiple Related Tables

    spesic

      Title

      Import->XML Multiple Related Tables

      Post

      FileMaker Ver.: 9

      OS: Win 7 x32

       

      Hi all,

       

      I'm a web developer mostly dealing with PHP/SQL and I'm fairly new to FileMaker database.

      I was hired by a contractor who wanted to develop a web application (registration form for their clients).

      It's pretty much a standard reg. page with bio/demographics/etc.

       

      Previously they had a fillable PDF which their clients would fill and send back to the contractor, and then they would have someone manually enter this data into FileMaker Pro.

      Now this is all done online and stored within SQL database.

       

      My contractor would like, if possible, to be able to export data from SQL into FileMaker database on a monthly basis. This would be convenient as they can produce montly/yearly reports and other neat things that FileMaker does rather than having to pay me more money to develop this in PHP.

       

      I did this for one of my previous clients so I thought this shouldn't be a problem. The earlier project I worked on had only one table and my XML export worked like a charm.

      However this particular database has multiple relational tables that looks something like this:

       

       

             MAIN  ----- Other Stuff
           /     |
          /      |
         Bio.   Demographics

       

       

      It's much more complex than that, but I'm going to leave it with 3 relational tables for the simplicity.

      So I first did the export records to XML to se the format and I noticed that the metadata from relational tables looks something like this: tablename::fieldname (e.g. education::university_name) so I figured that's all I had to do when I generate XML from SQL database.

       

      When I try to import this XML into FileMaker Pro database the fields from the MAIN table are automatically recognized and paired up with the ones from XML file. However all other fields from relational tables are not recognized and cannot be paired up with ther fields in FileMaker Pro. It is as if I can import into only one table at the time.

       

      Am I missing something here or is there no way to make this happen?

        • 1. Re: Import->XML Multiple Related Tables
          comment_1

           


          spesic wrote:

          It is as if I can import into only one table at the time.


           

          You can import into only one table at a time. This shouldn't cause any great difficulty, since the imports can be scripted.

           

          The best way to handle this would probably be to export a separate document for each table (with the common primary key included in all of them). If you set up some consistent naming scheme for the files, they could all be imported by pressing a button.

           

          Alternatively, use one XML document and a separate XSLT stylesheet for each table.


          • 2. Re: Import->XML Multiple Related Tables
            spesic

             


            comment wrote:

             


            spesic wrote:

            It is as if I can import into only one table at the time.


             

            You can import into only one table at a time. This shouldn't cause any great difficulty, since the imports can be scripted.

             

            The best way to handle this would probably be to export a separate document for each table (with the common primary key included in all of them). If you set up some consistent naming scheme for the files, they could all be imported by pressing a button.

             

            Alternatively, use one XML document and a separate XSLT stylesheet for each table.


            So according to THIS article RECORDID (which I assume it's the primary key or serial_ID in FileMaker?) is ignored by FileMaker on import. So if I import records from multiple tables separately, how will FileMaker merge this data together?

             



            • 3. Re: Import->XML Multiple Related Tables
              comment_1

              In Filemaker, a primary key is whatever you choose (usually, a field defined to auto-enter a serial number). Using the internal RecordID is not good practice, since it cannot be imported.

               

              I presume your data already has primary and foreign keys, so why don't you simply import them and use them to link the tables - either permanently (assuming no records are created within Filemaker) or temporarily.

              • 4. Re: Import->XML Multiple Related Tables
                spesic

                Thanks for your responses comment, I really appreciate it.

                 

                So I'm going to use existing primary key within the FileMaker database to link the tables. However I'm having a different problem.

                When I select second XML that contains data from a related table biographics in the target option I can only select Current Table ("main").

                Related Tables are grayed out and cannot be selected. What am I doing wrong?

                • 5. Re: Import->XML Multiple Related Tables
                  comment_1

                  If you're importing manually (i.e. not by script), you can import only into a table of the current layout. Go to a layout of the target table before importing.

                  • 6. Re: Import->XML Multiple Related Tables
                    spesic

                    The problem is that neither of the related tables have their own layout. There is only one layout and that one uses 'main' as the main table.

                    I guess I can tell them to go to layout setup and change the current table for the layout for every related table. It still beats typing all that data into the database manually.

                    • 7. Re: Import->XML Multiple Related Tables
                      comment_1

                       


                      spesic wrote:

                      The problem is that neither of the related tables have their own layout.


                       

                      That IS a problem, and not only for the current issue - there's a lot of things you cannot do if the table doesn't have a layout.

                       

                       


                      spesic wrote:

                      I guess I can tell them to go to layout setup and change the current table for the layout


                       

                      No, they should simply create additional layouts for the tables that don't have any.

                      BTW, there is something peculiar about this, because Filemaker automatically creates a layout for a new table.



                      • 8. Re: Import->XML Multiple Related Tables
                        spesic

                        Yeah, that's just how things are.

                        They had some FileMaker 'expert' laid out the database for them several years ago and that person is gone now.

                        My experience with FileMaker is very limited and my contractor and his employees have even less understanding of how it works or why things are the way they are.

                        • 9. Re: Import->XML Multiple Related Tables
                          comment_1

                          Do you have access to the Filemaker file/s?

                          • 10. Re: Import->XML Multiple Related Tables
                            philmodjunk

                            Before you go to the trouble of creating new layouts, keep in mind that layouts may be removed from the layout menu to hide them from the user. In that case the layouts are there, you just have to enter layout mode before pulling down the layout menu or select Manage | Layouts before you can see them.

                            • 11. Re: Import->XML Multiple Related Tables
                              spesic

                               


                              comment wrote:

                              Do you have access to the Filemaker file/s?


                               

                              Yeah, I do... why?


                              • 12. Re: Import->XML Multiple Related Tables
                                spesic

                                 


                                PhilModJunk wrote:

                                Before you go to the trouble of creating new layouts, keep in mind that layouts may be removed from the layout menu to hide them from the user. In that case the layouts are there, you just have to enter layout mode before pulling down the layout menu or select Manage | Layouts before you can see them.


                                I think I'm just going to create blank layouts for the import purposes, rather than going through any additional trouble.

                                 


                                • 13. Re: Import->XML Multiple Related Tables
                                  comment_1

                                   


                                  spesic wrote:

                                   


                                  comment wrote:

                                  Do you have access to the Filemaker file/s?


                                   

                                  Yeah, I do... why?


                                  Because I wanted to suggest that you check if the layouts are realy missing or just hidden...

                                   



                                  • 14. Re: Import->XML Multiple Related Tables
                                    spesic

                                    OK, so I'm done generating XML files and everything is going smooth except one thing.

                                    When importing fields with multiple rows (<DATA></DATA> tags), everything after the first row is omitted. Any idea why this happens?

                                    1 2 3 Previous Next