10 Replies Latest reply on Oct 13, 2014 4:34 AM by wimdecorte

    Searching across two databases in FileMaker 13

    c0nsilience

      Hello all,

       

      To do this, would one just need to connect them together as external data sources? Both DB's are FM 13 and .fmp12 format. They will be on the same machine.

       

      Any information would be highly appreciated!

       

      Thanks!

        • 1. Re: Searching across two databases in FileMaker 13
          wimdecorte

          Searching - as done by the user - is always done from a context: a layout and by extension a Table Occurance that the layout is based on.  The resulting found set is always going to be a found set in the table that the layout is based on.

           

          From your description I'm not sure that is what you are after.  You can not build a found set in two different tables unless you take control over that through scripting.

           

          So perhaps start by explaining a little more what you are after, using your real world scenario instead of trying to abstract it.

          1 of 1 people found this helpful
          • 2. Re: Searching across two databases in FileMaker 13
            c0nsilience

            If their are two databases of movies, based on genre, Horror and Drama, I'd like to be able to input data into Horror and Drama separately, but be able to search both of them at the same time.  Would I need to build a third DB (layout, with tables), called 'Movies' that has the tables from Horror and Drama as external data sources, to be able to do this?

             

            Bear in mind, I know I can have Horror and Drama as separate tables in the same database to accomplish this, but that's not what I'm after.

             

            Does FileMaker allow one to use a third database as a front end (like a pivot table) to gather data from two different DB's?

             

            Thanks!

            • 3. Re: Searching across two databases in FileMaker 13
              BruceRobertson

              Why not just ONE table, with genre as a field in that table?

              1 of 1 people found this helpful
              • 4. Re: Searching across two databases in FileMaker 13
                c0nsilience

                And have Horror and Drama as a value list for that field?  Yep, that'll work.  But, let's pretend for a minute that I have to have Horror and Drama as two separate tables in two separate databases, would FM be able to search both?

                 

                I have a client that wants two separate db's, but the ability to search both.  So, I'm trying to figure out how to do that

                • 5. Re: Searching across two databases in FileMaker 13
                  mbraendle

                  Looks like the data model is wrong. Horror and Drama are values of the same attribute (= field) genre.

                   

                  Can you merge the two databases by reconciling equal or similar attributes?

                   

                  As a side remark:

                   

                  Wim's notion that one "can not build a found set in two different tables unless you take control over that through scripting" is also true for Web-enabled applications of FileMaker.

                   

                  One example that does this http://www.clicaps.ethz.ch/index.php?lang=en

                   

                  Although you just see and use one search bar it does 6 searches in the background (you can try out with e.g. "nature")

                  1) one in the lookup table of suggest terms

                  2) one in the "do you mean" table

                  3) one to count the hits in the serials table

                  4) one to count the hits in the monographs table

                  5) one to obtain the results of the serials table

                  6) one to obtain the results of the monographs table

                   

                  3 and 4 trigger whether data must be retrieved in 5 and 6 and also trigger based on a threshold whether results are sorted or not.

                  • 6. Re: Searching across two databases in FileMaker 13
                    BruceRobertson

                    Tell the client two databases is a GREAT idea!

                    It needlessly complicates everything (believe me his is only the start of your many predictable problems) and you can charge more for it and we can even send you invoices for our time.

                    • 7. Re: Searching across two databases in FileMaker 13
                      keywords

                      Re: "Does FileMaker allow one to use a third database as a front end (like a pivot table) to gather data from two different DB's?"

                       

                      The answer to this question is Yes, but I concur with Bruce and Martin—it sounds like you should need only a single movies table with genre as an attribute. Your client might be expressing that wish out of ignorance as to how these things work, and so you may need to take the lead in educating them on sound data modelling. Even keeping horror and drama movies in separate tables, let alone files, means unnecessary extra development and maintenance load. And what if they want to add other genres. Comedy? Romance? History? Adventure? Farce? Are these more tables/files and more complexity? Or just simply other labels in a single Genre field? I know which way I would go.

                      • 8. Re: Searching across two databases in FileMaker 13
                        c0nsilience

                        Thanks everyone!  Yeah, they are moving from dozens of Concordance DB's into FM.  I know how I'm going to build it, I just wanted to see if it was possible to do the above-mentioned.  Superficially, the idea of the separate db's could pose as a data separation model, even though it really isn't.

                         

                        Thanks for all of the feedback!

                        • 9. Re: Searching across two databases in FileMaker 13
                          CarlSchwarz

                          You could have two separate portals of results pointing to the found results each remote database, which is fine if there are only two remote databases, but as the number of genre's increase the UI is going to get unworkable.

                          Or a "hack" would be to do a find in both databases, and since the tables are identicle to eachother then import the found records to a new table to display on the layout... Then if you want to edit thos found records... start some serious scripting!

                          • 10. Re: Searching across two databases in FileMaker 13
                            wimdecorte

                            c0nsilience wrote:

                             

                            Superficially, the idea of the separate db's could pose as a data separation model

                             

                            No, it really does not.  Data Separation is about seprating the data from the UI and the scripting, not splitting data into separate chunks.