1 Reply Latest reply on Apr 1, 2015 7:06 AM by philmodjunk

    Intelligent searching of value lists when trying to enter data



      Intelligent searching of value lists when trying to enter data



      We are making a database for anaesthetic procedures in a veterinary hospital. The problem is that there are a huge amount of options for things like surgical procedures (>500) and breeds of dog (>200). We also need an open value list that people can add to, as we are always doing new procedures.

      Basically my concern is that the same data may be entered in many different ways (e.g. we use a lot of acronyms for procedures THR for "Total Hip Replacement" so people may try to enter it in shorthand as well as in full, and "repair fractured femur" or "femoral fracture repair" would be two potential ways to enter data for exactly the same procedure). I really don't want people to enter a new procedure for example because they couldn't find the one they were looking for quickly.

      We have developed conditional value lists say for orthopaedic surgery and abdominal surgery which helps to a degree but there are still a lot of options per conditional value list. Subsequently searching for the correct procedure is a little too time consuming. We could add extra layers of conditional tables but this makes it a little cumbersome and, as we are gathering a huge amount of data, rapid entry will be key to ensure complete compliance. 

      What I would like is to have "Total Hip Replacement (THR)" in the value list and be able to put "THR" or "total" or "hip" or "replacement" into the data entry field and still get "Total Hip Replacement (THR)" as well as any other procedures that contain these words e.g. "Valve replacement" for "replacement" or "Repair fractured hip" for "hip". 

      At the moment I can only seem to get "Total Hip Replacement (THR)" by entering "Tot...." i.e. entering exactly the first few letters of the option.

      It appears that when searching the database that this sort of intelligent searching can be done but I am not sure whether it can be done at the data entry point.

      Can anyone help me out or advise me on whether this is possible?



        • 1. Re: Intelligent searching of value lists when trying to enter data

          You might consider more sophisticated conditional value lists than the basic design and there are also alternative methods for selecting from a dynamic list of values that you might consider. See the links that I've added at the end of this comment if you are interested in pursuing those options.

          But another option that I've used with the Tutorial section of the Known Bugs List database also comes to mind. Each tutorial record is a "tip" for using FileMaker more effectively that I might use in creating a post here in the forum. I need to be able to find these very quickly. So I added a text field to the table where I enter a return separated list of key words. To apply it to your project, each of the different methods for naming Total Hip Replacement would be a different row of text in this key word field on your THR record in a table of such procedures.

          I then set up a global text field with an auto-complete enabled drop down list of the key words from this text field. Each row of text in that field will be a different value in the value list. By typing just a few letters, the value list filters down to just a few possible options. When I select an actual value from this now narrowed list, a script uses the text thus entered into the global text field to perform a find to bring up that record. In your case, that script could return the ID of the procedure record thus found to enter into a corresponding ID field of the record you have just created to record a patient's treatment.

          You might check out the Adventures in FileMaking series. Adventures 1 and 2. They are free to download.

          Adventures in FileMaking #1 - Conditional Value Lists (includes details on how to set up a basic field based value list)
          Adventures in FileMaking #2 - Enhanced Value Selection (what to do when a simple value list won't cut it.)

          Caulkins Consulting, Home of Adventures In FileMaking