I’m still relatively new to AirTable, but I have grand plans. I’ll try to explain this simply, if I can.
I have a table full of a list of rooms that I provide support to. These rooms have equipment in them that needs to be refreshed occasionally and relies on standard packages to do so. These packages often share several items across them, but sometimes in different quantities. What I have figured out:
- Created the table with a list of items and I can link those items to another table that brings in their summed costs in the form of a package.
- Link those summed costs to their room on another table.
Here is where I am stuck:
- I cannot increase the quantities of items without it affecting all the items across packages.
- I cannot seem to export the final list of equipment in any meaningful way.
I tried looking at the “Product Catalog” template, but nothing immediate jumped out as a solution. The only way I’ve been able to make it work is to create a new table for each package; which would be fine except there are around 15 or so room packages, so the tabs start getting a little unwieldy.
Is this the right way and I just have to live with it, or is there another clever way and I am just missing it?
Thanks for any help in advance.
Welcome To Airtable Community !
Yes, that is a good start.
The qty in each package should be in the Package table not in the items table. i.e.: In the Packages table, each record will represent an item (not all items in 1 record) and a quantity.
How do you want to see this list? It can be a View, or a Page Designer template for each room, depending on your needs.
Feel free to share more.
I think my own playing, some forum searching, and your response confirmed my suspicions which is I’ll need an intermediate table to have the list of items in that package that I rollup on to another table that has an overview of the packages and their costs that I can then assign to the rooms on the room table. So:
[Master Equipment List]
[Equipment Package XX]
- Link to Device Type
- Add devices from master list
- Look up for make/model
- Look up for costs
- Change quantities
- Calculate subtotal
- Calculate taxes
- Serves as a list I can export to a CSV and provide to a vendor
[Package Summary Page]
- Link to Equipment Package XX
- Rollup subtotal from each package
- Rollup taxes
- Calculate labor
- Calculate the final total
[Master Room List]
- Link to packages
- Rollup final total
- Create groups by priorities
- Create groups by room age
- Create blocks with pivot tables or other diagrams
I was hoping to avoid having to make each Equipment List table, but I don’t think there is a way around it. Hopefully AirTable doesn’t get too cranky if I end up with 20 or so tables.
20 tables is no problem. I’ve got a base where I run random tests, and it currently has 35 tables. I’m sure there are some out there with even more. To my knowledge there’s no set table limit, and Airtable is designed to be very efficient with large amounts of data.
That aside, I’m wondering if a high table count is necessary. Instead of a different table for each equipment package, would it be possible to use a Single Select field to specify which package each record is tied to? You could then use custom views on that table for each package, which would still make it easy to export for vendors.
The trick is that several devices are shared across packages and with various qty. Example:
Package 1 - 2x Device A, 1x Device B, 1x Device D
Package 2 - 1x Device B, 1x Device C, 1x Device D
Package 3 - 1x Device A, 2x Device B, 1x Device C, 3x Device D
I can’t seem to get that scenario to play nice.
That said, the plan I posted above seems to have an issue too. Creating links to the various package tables causes the summary table to not work since I can’t have a rollup calculate something different per row. I’ll see if I can take some screenshots.
It sounds like you need to create a 4th table called “Packages Line Items”, which would act as a “join table” that links both “Packages” & “Items”.
So your Packages table would essentially be not much more than just the name of the package. But it would be linked to a Packages Line Items table. In this Line Items table, each record would link to a single package and a single line item with a quantity for that item.
Okay, lets see if this plays nice.
STEP 1 - Master Equipment list with descriptions and pricing.
STEP 2 - Creating Packages
STEP 3 - Summary of Packages to assign to rooms
I’m not sure I follow or if the screenshots I shared change the perspective on what you were proposing.
@ScottWorld Are you saying that this Packages Line Items would essentially have all the equipment for all the packages with their qty in one table?
Right. Each record in the line items table would have: 1 (linked) package, 1 (linked) item, and 1 quantity specified.
Thanks for the suggestion, I just can’t seem to follow the logic to get the outcomes I am hoping for. I can make it work in Excel, but that is different situation of course. I’ll keep playing with it, but this may not be the right tool for this.
No, without the quantities. So you have all the items in one table, then in the Packages table you add these items with the required qty.
As for the screenshots, why do you need a different table for the rooms? Why not add a Field (lets say its a single select field) that specifies where those packages are ?
I was like a dog with a bone with it last night and I think I got it working the way I need it to. What I keep forgetting is that views and grouping can solve problems that linking doesn’t need to. What I have now:
1.) “Master Room Table” - Has the list of all the rooms and their various other details. Has a link to the room total calculated on “Room Packages Overview”.
2.) “Room Packages Overview” - Has the rollups and calculations from the “Room Packages Table”.
3.) “Room Packages” - Has a Link from “Room Packages Overview” which generates the name of the package. I grouped it by Room Package and started adding items and their qty from the “Master Equipment List.” I then created views that are filtered by package that I can generate CSVs from to send to vendors. Without grouping I imagine it would be a mess to look at. XD
4.) “Master Equipment List” - Has a list of all the equipment that could be used in any package and it’s cost.
One thing I am starting to notice is that things are getting a little more sluggish. Not sure what AirTable offers insofar as optimization or it takes care of that on it’s own over time. I don’t have that many records in the base (less than 1000 total), so I’ll have to play with that maybe.
Thanks again for making me think through it all and for the suggestions.
OH! I did have another question. What do you recommend/use for your key column when you don’t have any info to put into it but still need it to be unique? I did RECORD_ID, but it doesn’t look super great and I can’t hide it.
I’ll sometimes use a formula to build a unique label based on other field data.
This topic was automatically closed 15 days after the last reply. New replies are no longer allowed.