Skip to main content

Create sum based on a table's fields/records

  • April 26, 2017
  • 3 replies
  • 45 views
K

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: 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!?

    3 replies

    P
    • Peter_Atkin
    • Participating Frequently
    • July 21, 2017

    Any headway on this? I think this is what I am looking for?

    W
    Forum|alt.badge.img+5
    • W_Vann_Hall
    • Inspiring
    • July 25, 2017

    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:

    • Names of individuals, their preferred sizes for various types of clothing, and the groups to which they belong.
    • The groups to which one may belong.
    • The types (categories) of clothing supported (e.g., “Women’s Shirt”) and the sizes considered valid for each.
    • Items of clothing and the categories to which each belongs.

    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):

    • While the base does do what you asked, I’m not sure how robust it is. Depending on what other functionality you require, it could very easily be impossible to support both the auto-population of desired sizes and other essential functions.
    • So far, I’ve not found a way to get it to work that doesn’t require at least one axis to be hard-coded. In the current implementation, while People, Groups, Items, and Sizes are all data-driven, clothing Types must be hard-coded. (Or, more accurately, Types are both data-driven and hard-coded.) If a new Type is defined – low-rise athletic socks, perhaps? – the administrator first creates a new record in the Types table, specifying the name of the type and the sizes in which it is offered.[1] At the same time, though, a new column must be created in the People table with a hard-coded reference to the new Type name; this allows the base to capture references to the Type. The People>SizeString formula must also be updated to allow new Type data to be collected.
    • Currently, each Order must be for 1 Item and be requested on behalf of only a single Group.
    • If a new size is added to the base – for instance, ‘P-’, for ‘Petite’ – the formula for Orders>Itemized Orders must be updated to reflect this.
    • Size indicators are limited to 2 characters. While this is sufficient to support the examples given (to which I’ve added ‘1S’, for “1 size [fits all],” and ‘XX’, for “XXL” [Extra-Extra Large]), such common sizes as ‘XLT’ [Extra-Large Tall] could not be accommodated without code revisions.
    • This base thinks ‘QA’ is a duck with a stammer. Accordingly, any even slightly off data could bring it to its knees.
    • This would not be the easiest Airtable base to maintain. :slightly_smiling_face:

    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[2] 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.

    1. ‘Valid Sizes’ are specified as a concatenated string of 2-character size indicators – for instance, ‘XSS-M-L-XLXX.’ Any 1-character indicators are padded by appending ‘-’; any indicator > 2 characters in length are clipped.
    2. In this case, ‘potential’ means ‘a valid size for at least one Type contained in the base’ rather than ‘a valid size for the Item being ordered.’ While the base attempts to warn the user when an invalid size has been selected, it cannot ensure the selection of valid sizes only.
    W
    Forum|alt.badge.img+5
    • W_Vann_Hall
    • Inspiring
    • July 25, 2017

    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:

    • Names of individuals, their preferred sizes for various types of clothing, and the groups to which they belong.
    • The groups to which one may belong.
    • The types (categories) of clothing supported (e.g., “Women’s Shirt”) and the sizes considered valid for each.
    • Items of clothing and the categories to which each belongs.

    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):

    • While the base does do what you asked, I’m not sure how robust it is. Depending on what other functionality you require, it could very easily be impossible to support both the auto-population of desired sizes and other essential functions.
    • So far, I’ve not found a way to get it to work that doesn’t require at least one axis to be hard-coded. In the current implementation, while People, Groups, Items, and Sizes are all data-driven, clothing Types must be hard-coded. (Or, more accurately, Types are both data-driven and hard-coded.) If a new Type is defined – low-rise athletic socks, perhaps? – the administrator first creates a new record in the Types table, specifying the name of the type and the sizes in which it is offered.[1] At the same time, though, a new column must be created in the People table with a hard-coded reference to the new Type name; this allows the base to capture references to the Type. The People>SizeString formula must also be updated to allow new Type data to be collected.
    • Currently, each Order must be for 1 Item and be requested on behalf of only a single Group.
    • If a new size is added to the base – for instance, ‘P-’, for ‘Petite’ – the formula for Orders>Itemized Orders must be updated to reflect this.
    • Size indicators are limited to 2 characters. While this is sufficient to support the examples given (to which I’ve added ‘1S’, for “1 size [fits all],” and ‘XX’, for “XXL” [Extra-Extra Large]), such common sizes as ‘XLT’ [Extra-Large Tall] could not be accommodated without code revisions.
    • This base thinks ‘QA’ is a duck with a stammer. Accordingly, any even slightly off data could bring it to its knees.
    • This would not be the easiest Airtable base to maintain. :slightly_smiling_face:

    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[2] 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.

    1. ‘Valid Sizes’ are specified as a concatenated string of 2-character size indicators – for instance, ‘XSS-M-L-XLXX.’ Any 1-character indicators are padded by appending ‘-’; any indicator > 2 characters in length are clipped.
    2. In this case, ‘potential’ means ‘a valid size for at least one Type contained in the base’ rather than ‘a valid size for the Item being ordered.’ While the base attempts to warn the user when an invalid size has been selected, it cannot ensure the selection of valid sizes only.

    M
    K
    Terms & ConditionsAccessibility statement