Autocomplete/dropdown Hybrid

[PatrickCuezze]

Here is my dilemma - I want to use the autocomplete functionality (where you type and the results come from the lookup table) BUT I want them to be required to select only options from the lookup table (like a dropdown).  Right now, if you use autocomplete, the user can select from the lookup table but can also free-form type if they desire.  I want them to be required to select from the lookup table.  Hope that makes sense.

 

Thanks ,

Patrick

[MayMusic]

Then why not using dropdown? Still when user is on dropdown he can type in the first letters to get to the part which starts with those letters

[PatrickCuezze]

Thanks for the reply.  The difference between autocomplete and drop down is that autocomplete lets me type ANY word in the field and it pulls results.  This is important because I'm dealing with, for instance, CPT codes.  A CPT code may be "90221" but have  a descriptor of "diabetes consult, Level I, not complicated" (I totally made that up as an example).  Many times when entering the data, we will just be typing the code (90221).  But other times, we need to, essentially, lookup the code.  It's really easy to do this with the Autocomplete feature except it doesn't restrict fields to the lookup table.  So we end up with lots of non-structured data. There are thousands of codes in the table.

 

So right now, the lookup table descriptor would be "90221, diabetes consult, Level I, not complicated".  In the dropdown environment, there is no way for the person entering data to search for "diabetes".  

 

I hope that makes sense.  I was thinking of double entering the lookup table so every code would have two corresponding lookup fields - one for the number and one that begins with my guess at the best key word in each field.  This is an inelegant and tie-consuming solution but would work 90% of the time.  Just seems it should be a no-brainer to restrict autocomplete to the lookup fields.....

[PatrickCuezze]

I figured this out.  I just needed to create a relationship manually between the lookup table and the child table and enforce referential integrity.  Seems like referential integrity should be enforced by default when you use the autocomplete feature but it isn't.  The only hiccup I had was that I had sample data in the child table which would not, at first, allow me to to enforce referential integrity.  I deleted that data in the table and voila.  Now, if you type something that isn't in the lookup table, it gives you an error!  

 

For a lawyer-turned-pizza operator working on a medical billing database, it made me happy to find this solution so I hope it helps someone else