polarpro
asked on
How do I search in not-related tables at the same time?
Hello.
The database I am currently working on consists of several tables, and some of them are not connected, e.g. TableA and TableB.
My aim is to have one single layout where a user can enter his search terms.
So, there would be a search field for NameA, for FurtherInfoA, for NameB, for FurtherInfoB and so on.
On top, there would be a search field where you can search all the fields at once. (To give you a bigger picture: This is a kind of a reference database with different categories. Each category has its own table as they require different fields. The reason why I would like to search all the tables at once is that often the user doesn't know which category to search.)
As long as I have tables that are related to each other I can place the according fields on the layout and everything works fine; I created fields that search in several fields at the same time and so on. But as mentioned: The tables are not related to each other.
I wonder if the right way to go is to introduce global fields here... (or if the solution is super simple and I just don't see the wood for the trees...)
Thank you,
Mike
The database I am currently working on consists of several tables, and some of them are not connected, e.g. TableA and TableB.
My aim is to have one single layout where a user can enter his search terms.
So, there would be a search field for NameA, for FurtherInfoA, for NameB, for FurtherInfoB and so on.
On top, there would be a search field where you can search all the fields at once. (To give you a bigger picture: This is a kind of a reference database with different categories. Each category has its own table as they require different fields. The reason why I would like to search all the tables at once is that often the user doesn't know which category to search.)
As long as I have tables that are related to each other I can place the according fields on the layout and everything works fine; I created fields that search in several fields at the same time and so on. But as mentioned: The tables are not related to each other.
I wonder if the right way to go is to introduce global fields here... (or if the solution is super simple and I just don't see the wood for the trees...)
Thank you,
Mike
Member_2_908359
there is no special trick. do the search in the first table, but before you execute it, store the search value entered by the user in a variable or global field, then use it to automate the search in the other tables.
ASKER
Thank you very much!
(I knew that globals would come into play...)
Well, if I am right, the first step of the answer is about typing search terms on a layout in browse mode and executing the search via a script.
Would it be possible to go a little further into detail regarding this? I work with globals and scripts, but must admit that the here mentioned way of searching is a thing I've read about often but couldn't find helpful instructions.
Or am I on the wrong track?
(I knew that globals would come into play...)
Well, if I am right, the first step of the answer is about typing search terms on a layout in browse mode and executing the search via a script.
Would it be possible to go a little further into detail regarding this? I work with globals and scripts, but must admit that the here mentioned way of searching is a thing I've read about often but couldn't find helpful instructions.
Or am I on the wrong track?
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Hello,
Thank you so much for your advice. Tried hard but didn't get it going though.
Thank you so much for your advice. Tried hard but didn't get it going though.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
a simple goto layout(2nd table) before searching in the 2nd table will work either
That's what I said, just with less words...
:P
:P
ASKER
"One important point to remember is that FileMaker cannot perform a search on an unrelated table."
Yes, this is the point... I guess. Telling a script to go from one layout to another is one thing; but then, in the end there will be problems with displaying the results, as a layout cannot display results from tables it is not connected to; as far as I know. I know that for this you can create a special Results table, but this requires way more advanced scripting skills. (So I've got some homework to do while working on the actual project... ;)
So, for the time being, I'll put all the information in one big table and add special tables for other things when needed. And I'll try to write a nice script that collects info and then goes from table to table...
Thank you very much for your help, guys!
Yes, this is the point... I guess. Telling a script to go from one layout to another is one thing; but then, in the end there will be problems with displaying the results, as a layout cannot display results from tables it is not connected to; as far as I know. I know that for this you can create a special Results table, but this requires way more advanced scripting skills. (So I've got some homework to do while working on the actual project... ;)
So, for the time being, I'll put all the information in one big table and add special tables for other things when needed. And I'll try to write a nice script that collects info and then goes from table to table...
Thank you very much for your help, guys!
the way to collect data in a common layout is to create a dummy relationship to the other table: do one with any field to any field and use the 'x' type to link them; this way the realtionship is always true, but fields will still be separate, so that might be a problem depending on what you want to show.
ASKER
@ lesouef:Thank you for your hint regarding the Cartesian Join. I also tried it before I posted my question, but again needed a few more hints to get it going in a proper way... I might come up with this as a "real" Experts Exchange question again (soon) as there a few things the Cartesian Join might be useful for...
Thank you very much, your help is highly appreciated.
Thank you very much, your help is highly appreciated.