1 of 1 people found this helpful
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.
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?
1 of 1 people found this helpful
Why not just ONE table, with genre as a field in that table?
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
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.
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.
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.
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!
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!
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.