What would you suggest as the best/fastest way to search across multiple related tables. I would want to display results in a portal and all the search tables are related to the main table. Thanks so much!
The closest thing to this is fmSearchResults, which was sold by seedcode, but now you'd have to contact the developer directly: msnmedia.com
FMSearch is off the market...Any other suggestions? Anyone?
What about a globals table with one or more global fields. The value(s) in the global fields (maybe from value lists if you search for the same things often) equal the values in the related tables. Put the portals on the global table layout.
Thank you, but this is searching "notes" type fields and others combined. I don't think this would work for this solution.
Any other ideas? Help!
What exactly are you searching and what is your workflow. Searching usually means records, so can you give an example? Searching related records usually means child tables (records) and grandchildren tables (records).
What do you want to see as the end product?
I would use a global field to get the search term, then put an On Exit script trigger to perform a find for that term in the specified fields with a new request for each one (the fields should be on the layout, even if they are invisible/off to the side so that the connection to the related tables works). Then set a utility field to flag all the records in the found set (ie set the field to 1). Use this to filter your portal, then script to reset the utility field before the next search.
If you didn't need the portal, you could bypass the utility field entirely and just use a list layout.
The attached is the global search I mentioned in my first post. There are 3 tables each with a text field, and each with a "search" field that substitutes a carriage return for each space between words, thus placing each word in the text field on a separate line. The global search field is = to each "search" field in the relationship graph. Selecting a word from the value list attached to the global search field will display related records having the same word in the text field of each table.
This is kind of rudimentary and may need more work for actual use, but you get the idea. Also, note that the attached only works if the global search field has just one word. But you really haven't given us enough detail to go further.
So we have Recipes as the main table Im looking to display in portal.
There would be Ingredients as a child table and Notes as a child table. Search them all at once and display in recipes.
So, obviously I can search a layout from the context of Recipes with child portals on the layout and display in a list or loop through found set and have a bolean (flag) as mentioned by jharding and then display the flagged in a portal. I could see this getting bogged down om performance; but I should probably test before I make that assumption.
Thanks so much; I did consider this; I'm just wondering what teh performenace would be . Because you have to loop through 2 found sets and set the flag field before and after.
I definitely know how to accomplish this; I w\as just looking for a method that would be as fast as possible from a performance standpoint.
Thanks so much! Very clean and appreciatted!
I am searching many more fields tha your demo, and I can't use a value list; so i'm wondering if portals will load up as you type in a shared environment with 1000's of records .
The value list is completely optional––it makes sense only if you search for the same things repeatedly. The file I uploaded makes sense only if your text field or fields have just a few words in them so that you can put each word on its own line. If your Notes fields can consist of many paragraphs my solution is not feasible. Like I said earlier, you haven't given us much detail. Can't tell if your Recipes to Ingredients example is your actual database or if you are just using that as an illustration.
Retrieving data ...