Can you describe the design of your database and your purpose for doing this? There are a number of approaches which can do what you describe. Which is the best option to use depends on knowing more about your database.
Sure. I work for a medically based non-profit and we use volunteer ophthalmologists to perform eye surgery in developing countries throughout the world. When we do patient screening, we're doing it the old-fashioned way: on paper which is then entered into Excel manually. We typically only ever have about 4 doctors in country at a time, each with their own subspecialty. We've purchaed a couple of iPads, and the idea is to use FMP to capture all forms of patient data for demographic reports electronically from the beginning without the paper (things such as age, sex, region... the list is long). When it comes to the subspecialty, only one doctor will ever perform that procedure, so it would save a step when "Cataract" is selected, it automatically populates the assigned doctor field. I figured out how to create the drop-down list - it's the automatically selecting the doc that has me stumped. Unsure if this answers your question or not...
You can set up a table of doctors with a checkbox field for listing the conditions/diseases for which they will be responsible. (A check box field is a quick way to set this up so that you ca list more than one such item for a given doctor.
Then a relationship like this can be used to link a doctor record to a patient by their diagnoised disease or condition:
Patient::Condition = Doctor::ConditionlList
You may need to fine tune that to make it easy for you to select which doctors out of the list are currently available, but that can also be done--so let me know if you want that capability.
Then you can add the doctor name field from the doctor table right next to this drop down list and it will display the first such matching doctor.
Thank you for the help - I will give it a try. Any chance you can point me in the right direction of a knowledge base link on how to do this? Or maybe some good keywords to search on?
That depends on what you know.
In manage | database....
Do you know how to create tables?
Do you know how to define relationships between tables?
Using the Inspector while in layout mode...
Do you know how to set up a checkbox field?
Let me know exactly where you get stuck trying to set this up and I can start from there...
On further thought, you should use a relationship like I described earlier with a looked up value auto-enter setting to copy the doctor's ID into a field so that the displayed doctor name doesn't change even when he is no longer listed as the available doctor for this condition or disease.
I *tried* creating tables and defining relationships but failed miserably. On the bright side, yes I feel comfortable with the inspector and all its tricks.
First basic table design:
Open Manage | Database | Tables.
Enter "Doctors" as a table name and click create.
You can use the same steps to create a "Patients" table. You can also select an existing table, enter a new name for it and click "change" here to rename it.
Now click the Fields tab. You can select a table in the table drop down on this tab, then created and change field definitions here in similar manner.
You should create at least the following fields for the Doctors table:
__pk_DoctorID, DoctorName, ConditionsTreated, Available
Enter __pk_DoctorID as the field name, select number from the drop down and click create. Now click Options to bring up the field options dialog. Click the auto enter tab and select the serial number option. This way each new doctors record will get a unique serial number. The pk part of the field name is a trick to identify this field as your "primary key". Primary keys uniquely identify records in a table and are the main fields used to link one table to another in a relationship. The two underscores at the beginning make sure that in list of fields sorted by name, this field will be listed at the top, which can make it much easier to work with.
Make DoctorName and ConditionsTreated, text fields.
Make Available a number field.
Now select the Patients table in the drop down and give it these fields:
__pk_PatientID (Auto-entered serial number just like the DoctorID field.)
_fk_DoctorID (make this a field of type number.)
constAvailable : Select Calculation for this one and typ in a single number, 1 in the calculation box and click OK.
These are just the minimum fields needed to answer your question. You'll obviously want to add more fields in both of these tables.
Now click the relationships tab and drag from _fk_DoctorID to __pk_DoctorID. You've now linked these two tables in a relationship by DoctorID that means the same as this:
Doctors::__pk_DoctorID = Patients::_fk_DoctorID
Now click Doctors to select it and click the button with two green plus signs to duplicate this table occurrence. This is not a new table, it's a new "label" for the Doctors table so that we can make a second relationship to the same table based on the value in the patient's Condition field. Double click this new box and rename it from Doctors 2 to DoctorsByCondition. Drag from patients::Condition to DoctorsByCondition::ConditionsTreated and from constAvailable to Available. This produces this relationship:
Patients::Condition = DoctorsByCondition::ConditionsTreated AND
Patients::constAvailable = DoctorsByCondition::Available
Now return to the Fields tab. Select the _fk_DoctorID field in Patients and click the options button. On the auto-enter tab, select the looked up value option and specify that it copy __pk_DoctorID from DoctorsByCondition (not doctors!).
This sets up the basic tables and relationships so that you can select a condition for a patient and see the doctor's name appear. See if you can get this far and then we'll take crack at setting up layouts for doctors and patients to make this work.
Please note that this is a very simplified approach. In a full up system, you'll likely have many more tables--including one that allows you to manage multiple conditions and treatments for the same patient which would be difficult to manage with just the two tables we are starting out with here.
Thank you very much for taking the time to write this up. I will give it a try tomorrow and let you know how it goes.
Hello! Followed your steps line by line - I think I'm ready to move on to the next step!
You now already have a layout to "Doctors" and another to "Patients". On the Doctors layout, you can enter layout mode and set up Available with a check box format using a single value: 1. Click this field for a given doctor and you mark them as avialable to perform procedures. This way you can keep things updated as to which doctors are currently "in country" and available to perform procedures on the patients. Format the ConditionsTreeated field with a check box set whith the same value list that you have already set up to list diseases and conditions that require treatment by one of your doctors.
On your Patients layout, format the Condition field with your drop down list of possible diseases and conditions.
Now, when you select from the value list on the Patients layout, the ID number of the matching doctor will be copied into the Patients::_fk_DoctorID field.
Enter layout mode and use the field tools to add the doctor's name field from Doctors to the patient layout. When you select a value in the conditions field, this field will show the doctor's name. Do not add this field from DoctorsByCondition. While this will appear to work, if you later clear the available check box for that doctor, the doctor's name (when selected from DoctorsbyCondition) will disappear from the layout where the ID number based relationship is still good and you'll still be able to see the doctor info when you use fields from the Doctors table.