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:
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
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:
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.
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!
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.
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.
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.
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 ;"")
If(PatternCount(LastName;Globals::gSearch)= 1; 1 ;"")
If(PatternCount(AsKnownAs;Globals::gSearch)= 1; 1 ;"")
If(PatternCount(FirstName&" "&LastName;Globals::gSearch)= 1; 1 ;"")
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.