value list options

[sharabi]

I have two related tables; Patients and Physicians. I need to see the list of physicians names from the patient's table to choose one (value list), and to be able to add a new physician name in the Patient's table, if that specific physician is not listed, without going to the physician table to add a new physician's name over there. Thanks

Edited November 6, 2010 by Guest

[Matthew F]

The short answer to your question is to check the box "allow creation of records in this table" in the graph between Patients and Physicians.

However, it may be ill advised to allow users to do this. You may end up with duplicate entries for physicians, e.g. with small variations their names and you won't be able to capture other vital information like their phone numbers, addresses, etc. It is probably better to force the user to create a new physician address entry if they don't already appear on the list.

[sharabi]

Thanks for your reply. Please see the attached file.

Adding a new physician with this relation works fine; however, for every new patient a new record will be created in the physician table even if the physician is already present.

I do not want to have duplicate records in the physician table carrying the same name.

Please help

file.zip

[Matthew F]

There are a number of issues with the design of your file.

1. You use the term 'ID' as the matchfield between Patients and Physicians. Is this the patient's ID or the physician's ID?

2. Is it possible for a patient to have more than one physician? If so you will need to set up a different structure to handle a many-to-many relationship. For this you can use either a join table or a multi-key.

3. If it is only possible for a patient to have one physician, then your simple one-to-many relationship is OK. However, the match-field should be ID_Physician, or Name_Physician. This field needs to be on both tables.

4. Using IDs instead of names (as you are doing) is strongly preferred because it is easier to ensure that they are unique. However, the use of IDs will make it harder to do what you want to do, i.e. create related records by simply filling in the Physician's name on the Patients layout. When you use a relationship like this to create a related record the user needs to enter a new value for what ever was the match field. In your case, this would be ID_Physician. However, this will mess up your auto-serial numbering system for physicians. The user would then need to enter the physician name after the related the record is created. At this point they should probably the other physician data like phone number, address, etc. Overall, this is not a good design.

Here's an example of what might be a better way of handling it by adding a little '+' button for adding new docs. I also changed your relationship and value list definitions. If you don't like seeing the physician IDs you can hide it by putting it under the physician name.

file.fp7.zip

[sharabi]

Thanks very much. I learned a lot from your solution which does what I need.

To me it seems that you are not really using the capability of adding new records through the relationship between the two tables.

Secondly, is there a way of getting rid of the ID numbers?

Your design seems to be secure enough that the user will not be able to enter a wrong name in the physician field; but he has no other option than to choose from the list . Am I right?

Thanks again for your help.

[Matthew F]

is there a way of getting rid of the ID numbers?

Yes, two ways. Change the physician_ID field to be a 'pop-up menu' instead of a 'drop-down list'. Pop-ups have a different behavior for fields which display a value list that is set to 'display values from second field only'. If you prefer the look of drop-down lists, you can simply move the physician_name field on top of the physician_ID field.

he has no other option than to choose from the list

Yes, that's intentional. The idea is to give the user the opportunity to enter more info on the physician.

To me it seems that you are not really using the capability of adding new records through the relationship between the two tables.

Yes, that's correct. For your setup, I do not think it is a good idea to do so. On the other hand, if you don't feel its important for the physicians to have ID numbers then you could make the relationship based on physician_name and add records directly through the relationship.... See the attached example.

not_recommended.zip