Calculation of Qty Based on Two Linked Fields

[Matt_Jeselnik]

I have an AirTable of product specifications for various hotel properties.
Table 1 - List of Properties
Table 2 - Rooms Matrix (each room, type of TV, TV Mount, Bed Type, etc)
Table 3 - Product Specs

In Table 3, I have a column: “Count based on:” to link to options Guestroom, TV, Bed, etc as certain specs total qty’s need to be based on total number of rooms (as every room gets one) or based on # of King beds (king sheets) for instance.

I would like the final qty column to calculate the qty based on which Property the spec applies to (Column 1) as well as on what the count should be based (“Count Based On:”, Column 23) and roll up the total.

Example: I have a Radio that goes in Suites for any property. I would want to select Suites in “Count Based On:”, then have formula reference column that it is present at every property and also that it is present in only suites, pulling count of suites from the Rooms Matrix table.

Welcome any suggestions on how to do this or how to reorganize my data. Thank you!

[W_Vann_Hall]

I hate to say it, but I think the code and referenced base in my reply to this post may be kin to what you need. (I say ‘I hate to say it’ because I’m not exactly sure how it works, so if you need assistance, I’ll have to try to figure out what I did.)

The problem I was trying to address in that base was as follows:

1. The original poster was in charge of ordering clothing for a group of coaches.
2. She had a list of individuals and the size they wore of different types of clothing — for instance, a zipper hoodie, or men’s jogging shorts.
3. She had a list of groups to whom the individuals belonged — for instance, girls’ high school soccer coaches, or junior mens’ trainers.
4. She had a list of clothing items and the class of clothing to which they belonged — for instance, a ‘gray city logo sweatshirt’ might be ‘adult unisex zipper hoodie.’

Here’s the fun part:

5. She wanted to be able to select ‘gray city logo sweatshirt’ and ‘girls’ high school soccer coaches’ and have the system
   a. figure out the class of clothing.
   b. figure out which individuals belong to the group.
   c. calculate how many sweatshirts in what sizes she needs to order.

If that seems to parallel somewhat your needs, have at it. :winking_face:

Create sum based on a table's fields/records Ask the community

As part of my job, I order a variety of clothes for a variety of people. Currently, I have a table (table one) that lists each item to be purchased, the type of item, who it’s being purchased for, and how many of each size (examples of each below). Examples Item: Long Sleeve Crew Neck Shirt, Half-Zip Pullover, Short Sleeve V Neck Shirt, Running Shorts Type: Men’s Shirt, Women’s Shirt, Women’s Sweatshirt, Men’s Shorts Who It’s For: Players All, Coaches All, Coaches Female, Coaches Male Sizes…

[Matt_Jeselnik]

Hi there. I haven’t had a chance to dig into this further and see if it would work for what I want. Can we chat offline and maybe I could get you into my Airtable to show what I’m trying to do? Do you do ad hoc work for this kind of thing?

Thanks,

[W_Vann_Hall]

Replied offline; check messages.