Building Packages of Equipment

[ucmavnerd]

Hey there!

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:

1. 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.
2. Link those summed costs to their room on another table.

Here is where I am stuck:

1. I cannot increase the quantities of items without it affecting all the items across packages.
2. 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.

[Mohamed_Swella1]

Hi @ucmavnerd,

Welcome To Airtable Community ! :grinning_face_with_big_eyes:

ucmavnerd:

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.

Yes, that is a good start.

ucmavnerd:

I cannot increase the quantities of items without it affecting all the items across packages.

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.

ucmavnerd:

I cannot seem to export the final list of equipment in any meaningful way.

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.

BR,
Mo

[ucmavnerd]

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.

[Justin_Barrett]

ucmavnerd:

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.

[ucmavnerd]

The trick is that several devices are shared across packages and with various qty. Example:

Device A
Device B
Device C
Device D

Package 1
Package 2
Package 3

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.

[ScottWorld]

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.

[ucmavnerd]

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

[ucmavnerd]

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?

[ScottWorld]

Right. Each record in the line items table would have: 1 (linked) package, 1 (linked) item, and 1 quantity specified.

[ucmavnerd]

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.