Building a value list based upon a related table
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.