6 Replies Latest reply on Nov 19, 2009 4:44 PM by nolak37

    Search a field for any match



      Search a field for any match


      Hello Again


      I have a field  set as a dropdown list Displaying the values from another table and Auto-complete using value list is selected.


      This is great because i can start typing and it instantly offers a variety of options to choose from, however the options listed are only those which match the first word.


      e.g.  When I type (ob) options are: observer variation,  obesity, obstruction, etc.

      but i will not get options for: male organs, migraine ocular, migraine ophthalmic, etc. where there is a preceding word.


      My question here is if there is a way to create a field to search from a list of values in this manner, and if so how might one create this.



        • 1. Re: Search a field for any match

          I can think of an approach that may serve, but it won't do exactly what you describe. You'll have to be the judge of whether this approach will serve and maybe another forum participant will suggest a better idea.


          You could set up a conditional value list where the choice you make in one field filters the list of possible values that appear in the value list of the second field.


          In field 1, You'd have a value list of one word entries built from all words entered in your main field. (I'll call it field 2 for this example.)


          Entering "o" in field 1's value list would then give you the choices of obesity, obstruction, organs, ocular, ophthalmic etc. (all single word keywords starting with same letters as your input.)

          Then, when you select a value in field 1 such as "organ", you'd see a list of choices in field 2 such as Male Organ, Organ Failure, Organ Necrosis, ... etc.


          Would that work for you?

          • 2. Re: Search a field for any match

            This approach might work, its a lot more work than i was hoping but its an option, ill keep looking for alternatives and post them if i find them



            • 3. Re: Search a field for any match
                 It's fairly easy to turn a line of text like this: Apple Orange Tangerine, into a value list of individual words. No scripting will be needed. If you want to know how, let me know.
              • 4. Re: Search a field for any match

                It sems you know something i dont, LOL


                Sure if you could let me know im sure it will help


                Thanks Phil

                • 5. Re: Search a field for any match

                  This calculation gives me the "field 1 value list" I described and can also be used as the foreign key in a relationship that filters your list of values visible in Field 2:


                  Call the calculation field cKeyWords:

                  Substitute(YourTextField ; " " ; "¶")


                  Turns a line of words into a list of values separated by a return. If you specify this cKeyWords as your source of values for a value list you'll see a combined list of all such words from this field from all the records in your table.


                  With two records in your table:

                  Apple Orange Peach (record 1)

                  Apple Kiwi               (record 2)


                  You'll see






                  in your value list.

                  • 6. Re: Search a field for any match

                    I did what you said and it worked great


                    This wasnt exactly what i was looking for as far as the end result goes but it does the trick.  Id say its the best alternative.


                    Thanks a bunch