4 Replies Latest reply on Sep 24, 2012 8:46 PM by dsimonson

    Creating Value Lists


      Here's my problem.

      I am creating a solution that tracks surgeries. For each surgery, I want to be able to record not only the type of surgery and the patient, but also the surgeon, the circulating nurse, the scrub nurse, the anesthetist, etc.

      In the solution I have a table called "personnel" that has all of these folks in it, with a field, TypeOfPersonnel, that has either surgeon, nurse, scrub, or anesthetist.

      What I want to be able to do is create a value list of just surgeons, just nurses, or just anesthetists, without having to have separate tables for each type of personnel- because sometimes (e.g. when a circulating nurse on one case can be a scrub nurse on another) they can be in several categories.

      Once I do this, I figure I can make drop-down lists for each field in my patient registration table that will be populated from one of these value lists. But I am stumped as to how to do it. I have played around with self-joins, but that hasn't worked. I am sure this has an easy fix- can someone help me out? Thanks.


      Dan Simonson


        • 1. Re: Creating Value Lists

          Why don't you have one table for personnel. Then have a field that designates what type of personnel they are. Then you make a table occurrence of that table with the type included in the link to whatever patient table you have?


          We do this with PaperCutPro. All the staff, physicians, and nurses are in one staff table. But they are included in different lists in different places throughout the solution depending on what they do and also they are included only if they are active. That way you don't have continually growing choice lists of people who have long since left the company.



          • 2. Re: Creating Value Lists

            You want dynamic value lists (based on 'TypeOfPersonnel') and in the "old days" I did this with a "CONSTANT" (a stored calculation) for each "value" I wanted to match. In your case: surgeon, etc. These would need to be exact, so they could be used in a relationship. Probably in a portal, you'd select the 'TypeOfPersonnel' and then the next selection would be based on the relationship starting from the 'TypeOfPersonnel'. Not fully explained, I know, but there are better ways then the "old days"!


            Dan, I don't know if any of this will help, but I suspect they may:



            Lot's of ExecuteSQL() function, so fmp12 is a requirement.



            • 3. Re: Creating Value Lists

              Hi Dan,


              I would suggest a self-clearing conditional value list.  A great example is here:




              On post 16 of the thread.  :-)

              • 4. Re: Creating Value Lists

                Great ideas, folks!  I am almost there.  I created a global field in my patients/surgeries table:


                z_g_Anesthetists and then loaded it with the entry "Anesthetist"

                z_g_Circulator with the entry "Circulator"

                z_g_Scrub with the entry "Scrub"

                z_g_Surgeon with the entry "Surgeon"


                In my personnel table, I identify folks in the TypeOfPersonnel field with one of those exact names.


                I also created a field "Active" in the peronnel table, and have a global field


                z_g_Active with the entry "Active"


                So, I created an occurence of the personnel table for each of those entries, and then the relationship was, for example for surgeons,


                SurgeriesTable::z_g_Surgeon = PersonnelTable::PersonnelType


                SurgeriesTable::z_g_Active = PersonnelTable::Active


                This works great, but I still have a problem.  Some of my folks are both Circulators and Scrubs- so on one case, they might be the circulator, and another, they might be the scrub.  How do I do that?


                Thanks again for all the tips!


                Dan Simonson, CRNA, MHPA

                2607 S. Manito Blvd.

                Spokane, WA 99203



                H. (509) 747-0819

                W. (509) 623-9766

                C. (509) 981-6274