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).
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: XS, S…
I also have a table (table two) with a record for each person I’m ordering clothes for and fields for the type of item and the “who it’s for” (i.e. Players All, Coaches All, Coaches Female. One record can have multiple “who it’s for” tags). Note that on table two the field for type of item is similar to item type on table one but not exactly the same. However, if making them the same will create the solution to my problem, I can adjust so the type of item is the same on both tables. I will then input each person’s size in each field according to each item type. (Example: The record is Jill, in field Mens Shirt she is a S, in field Women’s Shirt she is a M, in field Men’s Shorts it is blank, in field Women’s Sweatshirt she is MT, etc.)
My goal is to have the quantity I need to order for each size auto populate into my first table based on the information in the second table. For example, I want to order the Half-Zip Pullover for Coaches Female. In the Women’s Sweatshirt column on table two there are two female coaches that list S, three that list M, and one that lists MT. I want those sums to appear on table one wherever I assign an item (Half-Zip Pullover), the type (Women’s Sweatshirt) and the who (Female Coaches).
Is this even possible!?
It depends on how much you want it… :winking_face:
I had to think about it for a while, but I managed to put together an example base that does as you asked: From the order table, you specify the item of clothing you wish to order and the group for which it is intended, and the system displays a list of required sizes and the quantity needed of each. (While this implementation doesn’t include the code to do so, the base contains additional variables and data hooks to allow such information to be displayed on per-recipient, per-type, per-group, per-item, and per-time-frame bases.) In addition, the amount of supportive data entry has been kept to a minimum:
And, as best I can recall, that’s it!
(Here’s a link to the base: https://airtable.com/shrKUkPeiVkz2sfOJ . I’ve used the field descriptions to explain some things, but I haven’t used them fully to document the formulas as I usually do, to get around the inability to examine formulas in bases shared by links. So much of the action takes place in hidden fields, it’s probably best to make a duplicate of the base to be examined in leisure.)
So, that’s the good news. Here’s the bad (or, at least, the unappetizing):
A detailed discussion of how this works would likely be pointless without the base for reference, so probably the best thing to do, should you be interested, would be to clone the base, unhide all its fields, examine the formulas – and then get back to me if you still have questions.
To give you an idea of what you’re looking for, though, here’s a quick overview: In brief, the individual required sizes for each member of a Group are concatenated into strings, with one string created for each Type of clothing in the base, and with the name of the Type prepended. These named size strings are concatenated into a single string, with the resulting super string passed through to the Order table. There, the string specific to the Type of Item being ordered is extracted from the super string, the Type name is whacked off, and the resulting order string is processed once for every potential size supported by the base to count the number required of each size. Like many of my sample bases, this one [usually] works, but it certainly isn’t very pretty in action.
FYI, this is actually the second version of this base. The first had a slightly different method for specifying sizes. While neither could be called ‘user-friendly,’ the second, released version appears slightly more efficient and seems slightly less of a pain in the ass to use. For those interested, the original implementation – poorly documented, and most likely containing extraneous, “!”-flagged calculated variables broken by the deletion of another table’s columns – can be found here.