Searching for any or combination of strings from calculated value
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
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.