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.
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.
I would suggest a self-clearing conditional value list. A great example is here:
On post 16 of the thread. :-)
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