10 Replies Latest reply on Mar 14, 2013 7:57 AM by JosephSchueller_1

    Searching for any or combination of strings from calculated value

    JosephSchueller_1

      Title

      Searching for any or combination of strings from calculated value

      Post

           Ok, this one might be a doozy. Some needed background: 

           In a new solution, I've set up a multi-table search based on this article

           http://www.techrepublic.com/article/filemaker-multi-table-search/6310554 

           You may very well be familiar with the method, and it works, for the most part. I acknowledge that incorporating such a search can be an indicator of poor design, but rest assured I am utilizing the feature as a temporary stop-gap for a group of naive users. I will be educating them on proper find techniques over the coming months, but right now I need the solution to be as intuitive as possible. 

           Now, the problem:

           As stated above, the method works "for the most part". For example, I have a table "Individuals", which contains fields  Prefix, FirstName, LastName, MiddleName, MiddleInitial, etc. The multi-table search script returns records based on a calculated concatenation of the the chosen "name" fields. "Mr. John Q Doe" is a record in the Individuals table, searching for "Mr. John",  "Mr. John Q",  "Q Doe", etc. will yield succesful results. However, searching for "John Doe" does not. The individual strings must be found using their actual syntax from the concatenation if using more than one in the search. I want the search to return any records that match any combination of the individual strings in the concatenation, and have not had any success through modifying the calculation though I'm thinking that is the way to go. Any ideas? Thanks in advance!

           If it's any help, I've attached some screen shots of the reffered calculation, the "main menu" of the solution with navigation buttons and the search bar, and the returned records from a search for the above example. 

      CalcScreenShot.JPG

        • 1. Re: Searching for any or combination of strings from calculated value
          JosephSchueller_1
          /files/603083c2f9/MenuScreenShot.JPG 624x532
          • 2. Re: Searching for any or combination of strings from calculated value
            JosephSchueller_1
            /files/7a084ee6b8/SearchResultsScreenShot.JPG 573x948
            • 3. Re: Searching for any or combination of strings from calculated value
              philmodjunk
                   

                        I acknowledge that incorporating such a search can be an indicator of poor design, but rest assured I am utilizing the feature as a temporary stop-gap for a group of naive users. I will be educating them on proper find techniques over the coming months, but right now I need the solution to be as intuitive as possible.

                   With proper design, you shouldn't have to educate the users in "proper find techniques". Performing a variety of different finds should simply work for the user without any special training. Sometimes special find layouts and scripts are needed to make that happen.

                   What I see here is not a multi-table search. It is a multi-field search and the method in your referenced article for searching out data in multiple tables is not needed.

                   Try this  manual search as a test:

                   Enter find mode. Type:

                   John Doe

                   into the cCompIndividuals field and perform the find.

                   You should successfully find Mr. John Q. Doe.

                   This type of find can be scripted. See this thread for scripted find examples: Scripted Find Examples (to get a "live" link like this, click the icon in the far left end of the Post A Answer tool bar and paste your link text into the dialog that pops up.)

                   Just specify your calculation field as the field into which the set field step enters the find criteria.

                   Ps, you have some extra quotes and ampersands in your calculation. They do not change the results returned by the calculation and you can simplify the expression to be:

                   Prefix & " " & FirstName & " " & MiddleInit & " " & LastName & " " & AsKnownAs

              • 4. Re: Searching for any or combination of strings from calculated value
                JosephSchueller_1

                     Thanks for your reply Phil, but you're missing the mark. Please review the linked article to really "get" what I'm doing. I don't mention the other tables being searched for simplicity's sake. It's just a matter of including them in the script and relating them to the Globals table. From the article: 

                      

                       
                1.           The user enters a search string into a search field.
                2.      
                3.           If this search string is found in a specified field in one of the tables to be searched, this match returns a boolean positive result of '1' in another (calculated) belonging to the record in which it is found.
                4.      
                5.           Through scripting, the records containing the '1's are then retrieved from their respective tables and imported into the SearchReturn table, whose purpose is to store them temporarily until the user decides which record they wish to work on, at which point;
                6.      
                7.           Clicking a button on the desired record returns the user to that record in the table from whence it came.

                     I would not underestimate the difficulty a new FileMaker user experiences in learning how to utilize filtering, operators, constrain/extend, etc. when performing finds, though the method I employ doesn't accomplish nearly the results of using those features. I simply want users to know that a record exists, and where it is. The intent is to stem common problems such as duplicate records and consequences from user's lack of comprehension of the entities. I am building a fairly complicated solution, and it needs to make intuitive sense from the get go or it will not be succesful. 

                     I am not a fan of making (for each table) "magic buttons", scripts, or special layouts anymore than is absolutely necessary. Users will eventually learn (through experience and training) how to use the built in features, becoming nimble users who have a logical understanding of what they are doing. But in the meantime, why not give them a familiar option that works? My idea is to make it feel like a standard web search and gradually train them on more advanced techniques. 

                     Yes, the simple find for "John Doe' works in the cCompIndividuals field in the Individuals table. But I want it to find "John Doe" everywhere, that's where I'm stuck. You might be asking "Why would he be in any other table?" Because no matter how well designed a solution may be, users will unavoidably enter data in the wrong place, make a new record in the wrong table, confuse one table/entity with another, etc. It will help me to do housekeeping.  Also, my solution utilizes several join tables where, for example, an individual or group can be "enrolled" variously. The SearchReturn layout allows a nice, simple snapshot of a record occurrence's history and relationships, as well as a convenient way to navigate to the related records. This has has always been a desired feature from the populations of users that I've done development for. I intend on making good use of tabbed portals, but that is just something my users aren't used to seeing yet. 

                     Finally, the extra ampersands and quotes were the way I got the data in AsKownAs to show up as displayed on the final screenshot ('Goombah'). The point is to indicate what an individual is commonly called- nicknames, if you will. 

                      

                • 5. Re: Searching for any or combination of strings from calculated value
                  philmodjunk

                       Then please describe the full parameters of your search needs and not a simplified version that does not match what you are actually trying to do. I did skim that article enough to know that it was way more than you needed for what you described in this thread.

                  • 6. Re: Searching for any or combination of strings from calculated value
                    JosephSchueller_1

                         Phil - The full parameters of the search were contained in the article, that's why I provided the link. It seems you chose to answer my question without familiarizing yourself with the complete context.  If you can help me, great! Thanks! But if you can't, please don't scold me out of frustration. 

                         I understand this is complicated, but please carefully read the article and my question before responding. Thanks!

                    • 7. Re: Searching for any or combination of strings from calculated value
                      JosephSchueller_1

                           Bumping for help. Still haven't been able to figure out how to get the search to return results when searching a combination of non-sequential strings.

                      • 8. Re: Searching for any or combination of strings from calculated value
                        philmodjunk

                             There is frequently a case where posters get a bit confused about whether or not a solution of that type applies to their situation. Since your example did not merit using that approach--as was easily determined by reading the very outline that you posted in your earlier response, I didn't see any need to speadn time reading that article.

                             I've asked for an example of what you want to do that shows that you are actually searching more than one table and you have yet to do so.

                        • 9. Re: Searching for any or combination of strings from calculated value
                          JosephSchueller_1

                               Phil, can you just leave/ignore this thread? I'm not trying to get into a semantic, "I'm smarter than you" back and forth. Anybody who takes the time to read the article and my posts will see quite clearly what I'm getting at. Your questions are already answered if you read carefully. If you don't have time to do that, just say so. 

                               My goal is getting the search to return correct results regardless of search string component sequence. I'm having trouble with that. The problem is built into the method described in the article. The cross-table search action is itself immaterial to my problem, but understanding the steps in setting it up is at the core.  I'm not going to commit a disservice by trying to describe the method in my own words when it's already written. Understanding the steps of setting up the method is key to solving my problem. My narrative is intended to justify "why" I chose this method, so I wouldn't have to hear anybody try to talk me out of it. Ignore that part if you want. 

                                

                          • 10. Re: Searching for any or combination of strings from calculated value
                            JosephSchueller_1

                                 Update:

                                 I've figured out a working (if inelegant) solution. Forgoing the "cCompContact" field as set up in the article, I've set up the "cIfMatchComposite" calculation to return "1"  if the pattern count matches any combination of strings queried as:

                                 If(PatternCount(FirstName;Globals::gSearch)= 1; 1 ;"")

                                 or

                                 If(PatternCount(LastName;Globals::gSearch)= 1; 1 ;"")

                                 or

                                 If(PatternCount(AsKnownAs;Globals::gSearch)= 1; 1 ;"")

                                 or

                                 If(PatternCount(FirstName&" "&LastName;Globals::gSearch)= 1; 1 ;"")

                                 or

                                 If(PatternCount(FirstName&" "&MiddleName&" "&LastName;Globals::gSearch)= 1; 1 ;"")

                                 ..... and so on. There are approximately 40 different concatenated strings based on the possible combinations of fields to be searched in the particular table this calculation is set for. Now I need to replicate the steps in the remaining tables based on the possible field combos users may search for, which should be easier as the above example is from the "Individuals" table in my solution and is thus subject to myriad permutations of search criteria.