10 Replies Latest reply on Jan 2, 2015 8:32 AM by MarkCampbell

    Found set from multiple tables



      Found set from multiple tables


      I am trying to do a search on a keyword in multiple tables and then display the number of records found.

      As an example, we have tables for each of Names, Cities and Occupations. The user enters "Baker" into a text field and presses a search button. How could I make a script to search each table for the string "Baker" in any field and then display the number of each found set in associated layout fields - e.g., "7 Names", "2 Cities", "5 Occupations".

      Any suggestions? Can it be done?

        • 1. Re: Found set from multiple tables


          Well, you could write a script that does something like running three different finds (one for each table and then capturing the number of record returned.  Each field to be searched and reported on would be a new section in the search.

          Set Variable[$_Name,""]

          Set Variable[$_City,""]

          Set Variable[$_Occupations,""]

          Go To Layout [Person_Form]

          Set Error Capture [ON]

          Enter Find Mode

          Set Field [Last_Name, "Baker"]

          Perform Find

          Set Variable[$_Name,Get ( FoundCount )]

          Go To Layout [Address_Form]

          Enter Find Mode

          Set Field [Street, "Baker"]

          Perform Find

          Set Variable[$_City, Get FoundCount )]

          Go To Layout [Job_Form]

          Enter Find Mode

          Set Field [Occupation, "Baker"]

          Perform Find

          Set Variable[$_Occupation,Get ( FoundCount )]

          Set Error Capture [OFF]

          Show Custom Dialog ["Search Results", $_Names & " Names" & ¶ & $_City & " Cities" & ¶ & $_Occupations & " Occupations"]



          • 2. Re: Found set from multiple tables

            I dont understand your answer, doesnt mean it's wrong.  I just dont think it would work. What happens next?  You created 3 finds on 3 different layouts (Tables, TO's).   What does the original poster want to do next?  How would one go to a found set of these records for further analysis

            • 3. Re: Found set from multiple tables


              Hi Steve,

              The OP's request was to search 3 different fields in 3 different tables and then display the number of records found from each of the tables.  The request wasn't to return 3 different record sets from the 3 tables, simply to capture the number of records in each table where a specific field contained the "keyword".

              The OP didn't tell us what the purpose of the returned information was.




              • 4. Re: Found set from multiple tables

                Why not use three relationships where the same "search" field is used in the layout's table to match to the City, Name and Occupation tables. Then simple aggregate functions can return the count of related records (Count ( RelatedTable::NeverEmptyField ) ) without any scripting needed at all. There are ways to set up a "keywords" calculation field that can be used to search against data where you have multiple words in the same field and want to match against all records where that keyword is present in the field.

                The key difference (pun intentional) is that such matching via relationship will be by exact value where a scripted method can use partial text matching. Searching for "Baker" can return "Bakers", for example--when using  script, but now via relationship.

                • 5. Re: Found set from multiple tables

                  My apologies for my vague request and my over-simplified example. In reality it is a bit more complex. We currently have a research dashboard that our field crew goes to upon login which displays seven types of artifacts they can drill into (screenshot attached for clarity). Initially it displays the number of all records in each table (via a relationship like Phil mentions above). The user can click on the graphic sector they want and get a list of all the records (e.g., Charts) and pick one for all the gory details. This all works fine.

                  The functionality I am trying to implement is:

                  1) allow a field engineer to enter a keyword, say "Containerization", and click Search

                  2) Do a search of all fields in all seven tables for any record with the keyword in it and create a found set for each table.

                  3) Display the record count found for each table on the dashboard. For example, the engineer would see on the dashboard that there are 2 Charts, 1 Company, 1 Use Case, 7 Solutions and 0 for the rest.

                  4) The engineer would click on the table they want, e.g., Charts, and see a list of only the two Charts matching the keyword

                  5) Upon returning to the dashboard they would see the same selection numbers for the search (e.g., 2, 1, 1, 7, 0, 0, 0).

                  6) They click the reset button to un-select all found sets and display the total records as before the search (e.g., 3, 732, 77, ...).

                  One idea: I do have a table called Dashboard I can use to record results. So maybe a "Search" script that stored the keyword(s) and then did a table by table searches and record the results in Dashoard fields. I could make a record for each login user upon search so different users don't see/confuse others users' searches. When selecting the table (say Charts) I could pass in the keywords and do a single table search on just that table and display the results. Or is there a better way.

                  Sorry this is so complicated.

                  (Note: at this point I have a kludgy solution to manipulate the graphics so no need to worry about that)

                  • 6. Re: Found set from multiple tables

                    The method by Mark_M would seem capable of what you want.

                    I just find it odd that you need to "search all fields" of any table as each field in a record should store very different information. I'd expect such a keyword to be present in exactly one such field of any given table. And having to search multiple tables also suggests that your data model may not be optimum as there is a good chance that you could structure your data so that this "keyword" is present in only one field of one table--a centralized table to which you link different "detail tables" for your different types of artifacts. The central table defines fields common to all artifacts and the related tables hold fields unique to one type.

                    • 7. Re: Found set from multiple tables

                      Thanks Phil - I will relook at Mark_M's solution - it probably will work.

                      Good feedback on the data model also. The big issue we have is that we serve two constituentcies - researchers and field engineers/sales people. The field folks need to search on almost anything and often do - what companies have something to do with China, what companies were founded in 2008, there is a new market sector called APM, do any solutions deal with this. These things change almost weekly and trying to get discrete cells that model this liquid nature has proven elusive. Now for our researchers, we do use very well-defined fields and flows and have a central table (Solutions) that is the home of all integral data (in ~100 well-defined fields). That side works well - its the requirement to handle requests from the "general masses" that is complicating things. 

                      BTW - the general search field in the top rightmost corner of the standard FileMaker toolbar does almost exactly what I want (as opposed to the Find/Perform Find button). What function does it call?

                      As always your insights are greatly appreciated.

                      • 8. Re: Found set from multiple tables

                        That box is called "Quick Find" and there's an equivalent script step that allows you to script it. It's layout specific and you can specify which fields on a given layout are searched by quick find so keep that detail in mind when attempting to implement it.

                        • 9. Re: Found set from multiple tables


                          OK, I just played around with Quick Find in one of my solutions - that is wicked cool.




                          • 10. Re: Found set from multiple tables

                            Excellent - I will see if I can get that working. Great insight guys - much appreciated.