Editable lookup - single select

[Dean_Lutrin]

Hi friends
we are using airtable for a database of procedures performed by surgeons. We have one table which has patient demographics which includes the name of the treating surgeon. We have another table with the operation details. That table looks up the name of the treating surgeon and has that in a column of its own. Occasionally a patient undergoes an operation by another surgeon (a secondary surgeon). I would like to create a column that looks up the name of the treating surgeon from the demographics table and puts it into the operation table, but it must allow it to be edited/changed to allow another doctors name to be entered. Essentially a lookup that can be edited. Any ideas?

[David_Skinner]

I’m not sure I fully understand your problem here, but it sounds like you probably have the structure wrong. You should have three tables Patients, Surgeons and Operations. The Operations table should link to the Patients and Surgeons table. Each operation is an individual record that links to the patient it is being performed on, and the surgeon that is performing it.

[Dean_Lutrin]

Thanks for your reply.

I do not have a “surgeon” table because there are only 6 surgeons on our staff and they are here long term. I have made a single select dropdown for the surgeon rather than a lookup table. Even if i use a lookup, is it possible to have a different surgeon assigned as the admitting doctor and a different (of even multiple) assigned as the operating doctor?

[David_Skinner]

I would still think it is better to have a Surgeons table from a structural point of view, it will make any future functionality easier to build in.

Regardless, is there a reason that the surgeon is assigned to the Patient record, rather than the Operation record? It doesn’t seem right that the Operation record would look the surgeon up from the Patient record if a patient can have different operations with different surgeons.

If the Patient record definitely needs to have the primary surgeon assigned to it, then I still think the Operation record needs to have the surgeon assigned separately. Further, if this is the case, then I think a Surgeons table is even more important to maintain the consistency of your data.

[Dean_Lutrin]

David.

Some patients are admitted by a surgeon but are not operated upon by that surgeon. And each patient belongs to a particular surgeon. The problem is when they move around… I take your point and will augment the database structure. But it would still be nice to have Airtable fill out a default value that I can change if there is an exception.

[Justin_Barrett]

Dean_Lutrin:

it would still be nice to have Airtable fill out a default value that I can change if there is an exception.

As far as I know, the closest you can get to this behavior is to use the Single Line Text field type, which allows for a default value to be set. Only a handful of field types have editable defaults.

However, I’m not sure that this matches what you want based on your original description. It sounds like you want the surgeon field on the Operation Details table to initially pull from the surgeon’s name on the Patient Demographics table, but still be editable if you want/need to change it. In other words, you want that field to initially be dynamic—driven by existing data elsewhere in your base—but also manually editable. Unfortunately Airtable fields don’t work this way. Their content is either dynamically-driven, or manually-entered. It can’t be both.

Dean_Lutrin:

is it possible to have a different surgeon assigned as the admitting doctor and a different (of even multiple) assigned as the operating doctor?

Yes, by using multiple doctor fields: one for the admitting doctor, the other for the operating doctor(s). For the admitting doctor, that could be a Lookup field pulling from the patient’s demographics record. For the operating doctor(s), this could be a Multiple Select field with the same choices that exist on the other table. Here’s that setup in a quick mockup I threw together. Is this what you want?

[Dean_Lutrin]

Thanks. Perfect advice.
Its a pity that it cannot be both as you have mentioned but your workaround makes sense.