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.
