Skip to main content

I come from MS Access, so I realize I’m in uncharted territory here. I have several instances where I want to create a table that serves as a list for dropdowns to use across multiple other tables, and I’m not sure how to do that without it being really messy. For example, I want to make a list of departments as a table, rather than having a “single select” field type, so that if I add departments or change the name of departments, it will update across all tables that need to reference a department, rather than needing to update a dropdown field in every table that uses Department, every time there is a change in department names or structure.
 

I have one table for order requests - every request needs a department. I have another table for personnel - every person is assigned to a department, etc. I’m finding that if I create a linked record, I’m getting a field added to department that may have thousands of linked records. Every department certainly makes thousands of order requests every year. I just want a list of departments that can be used elsewhere throughout my other tables, not a list of every record where that department has been used. That would be so incredibly bloated.

Ah, that’s kind of the way the linked records are intended to be used and I don’t think there’s a way around it I’m afraid

For what it’s worth, I haven’t seen people complaining about lag due to the linked record connections?

Hey ​@Slimshaw,

If I’m following, wouldn’t that get solved by hiding the Orders field on the Departments view?

Feel free to grab a slot using this link if you’d like to go through this together. I’d be happy to show you around.

Mike, Consultant @ Automatic Nation 
YouTube Channel 

I (or someone) would then have to go in and hide fields anytime someone made a table where they wanted to use the list of department names as a drop-down, which isn’t ideal, since most people should not have write access to that list. It’s also less than ideal that all of the other fields in the Department table are visible to anyone choosing a department from the dropdown. Is there really no way to solely look up a value from a table without adding info to the source table or providing a view into more than the one field I want to allow users to see?

Another situation in this same scenario would be a vendors table - it would have the vendor name, rep’s name, contact info, notes about the vendor, but on the orders table, all a user should see and be able to choose is the vendor’s name. It’s duplication of effort to create a dropdown field type and recreate the name of every vendor as an option, and edit it every time a new vendor is added or a vendor has a name change. I do not want to record every order that vendor is linked to (and every receipt they are linked to, which is another table that references vendor name) within the vendor table. I want it to be a clean list, unchanging except by a handful of people who may need to update it from time to time.

You can do everything that you’d like to do from the interfaces layer instead of the data layer, which gives you control over what extra fields you see from the other table.

Back at the data layer, you can hide fields that you don’t want to see, and create extra views as well.

Also, in the data layer, the fields that will be shown in the record picker for the linked record field will be based on your topmost view in the linked table. But most of your team’s data entry should be done at the interface level where you can restrict things — and you can even prevent them from having base access altogether (i.e. interface access only).

You can also set table permissions so people can’t add new vendors to the vendors table. Or, again, if you do it from the interface layer, you can make that list read-only.

The data layer is primarily for you to build the structure of your base, but your team should do their day-to-day work in the interface layer, where you have complete control.

Hope this helps!

If you have a budget and you’d like to hire the best Airtable consultant to help you with this or anything else that is Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld

Terms & Conditions