2 Replies Latest reply on Jan 3, 2014 8:39 PM by dsimonson

    Building a value list based upon a related table

    dsimonson

      Title

      Building a value list based upon a related table

      Post

           I am building a solution that tracks patients and ophthalmic surgeries.

           As we are operating on patients, different types of procedures may have a different list of associated  special items that will be used.  I have created a portal from my patient/procedure table (called appointments) to a related table called special_items.  When the nurse clicks on the portal, a new record is created in special_items, that they then fill in with the item description and the appointment_id key and so on.  

           Here is my question.  I want to fill in the special item description via a drop-down value list that only contains those special items that are appropriate to that type of procedure, which I can identify because the appointment record has a field called appt_type that tells me the procedure.

           I have created another table called special_items_list, which I plan to fill with all of the possible special items, with fields for descriptions of the item and the appt_types that they are used for.  So I figure I will relate the appointment table to the special_items_list via the appt_type field, and use that relationship to create the value list.  Thus, when the patient record shows “cataract” in the appt_type field, the relationship to special_items_list only shows special items that are used in cataract surgery.

           This works well for items that are exclusive only to cataracts, but what if an item is useful for both cataracts and retina?  I thought about creating a bunch of fields in the special_items_list, such as is_cataract and is_retina and is_cornea, and making a relationship for each one, but that seems too complicated.  

           I tried putting in delimited data via a checkbox set entry field (when you create a new entry in special_items_list, you can check several types of surgery and it forms a delimited list in the field), but this did not work.

           Thanks for reading all of this, I hope you guys have a simple solution for me.

            

        • 1. Re: Building a value list based upon a related table
          philmodjunk
               

                    I tried putting in delimited data via a checkbox set entry field (when you create a new entry in special_items_list, you can check several types of surgery and it forms a delimited list in the field), but this did not work.

               That approach should work. I suggest taking a new look at it to see why that it didn't.

               This Demo File just so happens to illustrate the check box method: https://www.dropbox.com/s/j6qf0z9fnem3uxd/ConditionalValueListDemo.fp7

               It actually demonstrates how check boxes can be used with the match field on either side of such a relationship.

          • 2. Re: Building a value list based upon a related table
            dsimonson

                 You are right, Phil - it did work!  When you said it should, I went back and looked at what I was doing.  I will tell you why I was thinking it didn't work (to maybe help another newby).  To check to see if the relationship worked, I had added the related field to a table view from my appointments, thinking it would show me the results - which it did, but it only showed the first record to match.  So I assumed it wouldn't work for the multiple match.  

                 But after reading your post, I went back and constructed the value list based upon that relationship (where I had checked multiple boxes for several items), and indeed it did work.  So I am good to go.  

            In my special_items_list record, I now have a checkbox set for the special_items.appt_type field that has cataract, retina, plastic, etc., and if you check more than one, the field ends up with a delimited set of all the items checked - and the value list matches only the records that have the matching appt_type.  So if the appointment.appt_type is cataract, it shows only cataract items, but if it is retina, it shows only the retina items - so an item that has both retina and cataract checked (and thus both values in the delimited set) shows up in both lists.

                 Thanks again, you are a font of FileMaker knowledge!!

                 --

                 Dan