Just to address this quickly:
Airtable allows you to LOCK a view (and password protect it) so that nobody can change the filter, group, sort, and color settings without unlocking it first.
But moving on to the rest – Airtable behaves like a database, and not like a spreadsheet. It’s database behavior is pretty standard across databases, so using it like a database rather than a spreadsheet is going to yield the most benefits, in my opinion. And that means modeling relationships between different kinds of “things”, where a table represents a collection of “things” that are alike.
What I’m going to suggest may sound onerous if you are not accustomed to working with databases, but once you get the hang of a workflow in Airtable, it really isn’t bad – especially if you get the hang of keyboard shortcuts et. al.
If I were building this base, I would probably use at least 5 tables:
- Houses - each record represents a House and any data you need at the House level
- Rooms - each record represents a Room and any data you need at the Room level, and each Room belongs to a House (linked); a House can have many Rooms, but a Room can only belong to one House
- Room Designs - each record represents a Design for a Room and any data you need at the Room Design level, and each Room Design belongs to one or more Rooms (linked); a Room could have many potential Designs, and a Design could be reused for any number of Rooms by being linked to multiple Rooms
- Appliances - each record represents an Appliance and any data you need at the Appliance level, and each Appliance belongs to many Designs (linked); the same Appliance record is reused in any Design record that uses that Appliance
- Materials - each record represents a Material and any data you need at the Material level, and each Material belongs to many Designs (linked); the same Material record is reused in any Design record that uses that Appliance
By using a structure like this, an Appliance, for example, is represented in a Room by way of a Design – a Design record is where an Appliance meets a Room.
If there are a couple pieces of data about an Appliance that you need to pull into the Room record, you can do that by using chained Lookups retrieving only those couple pieces of data – first into the Design record, and then through the Design record and into the Room record.
You may be thinking to yourself that this sounds like a lot of table jumping – you may feel like you’ll be clicking all over the place to navigate around. But I assure you that Airtable accommodates this workflow very well. For this example, let me assume you already have your Appliances and Materials tables populated with your regularly used items.
You can easily create a design without ever leaving the “Houses” table. Starting there, you create a House and fill in all its data. When you reach the field where you link to Rooms, you expand that field and select “+ Add New Record”. When you do that, you remain in the House table, but a Room record will be created and expanded vertically for you to fill out. You create your Room record, and when you get to the field where you link to Designs, you, again, select “+ Add New Record”, and, as you are still in the House table, a Design record is created and expanded on top of your Room record. When you finish filling out the Design record, you hit the escape key and it will close the Design record and you will be back at the vertically expanded Room record. If you are done adding Designs to the Room, you can hit the escape key again and you are back at the House table.
