Sum of Max or Min values in an array

[AlliAlosa]

I tried to post this yesterday in much more detail… but got an alert saying the post had to be approved? Not sure why, that’s never happened to me before, and it hasn’t been approved over 24 hours later… so let’s try this again!

Background: I’m building a base for employee clothing orders. Based an employee’s title, they get a certain allotment of clothing. If they want to get more than their allotted amount, they are responsible for paying for the overrun.

Let’s say a manager is allotted 5 sweatshirts. If the manager wants to get 7 sweatshirts, they are responsible for paying for the extra 2, and we will pay for the 5 most expensive items.

What makes this tricky, is that larger sizes have an associated upcharge. Say $2.50 for an XXL, $3.50 for a 3XL, and so on.

Table set up:
[Clothing Standards] holds each title and their associated allotments for each type of clothing.
[Items] holds all clothing items, sizes, and prices.
[Clothing Orders] links to [Clothing Standards] and [Items]. Here the items chosen are compared to the allowed allotments.

If I have all the sweatshirt prices in an array, can I sum just the 5 most expensive items? Or alternatively, the 2 least expensive items?

[Justin_Barrett]

I can’t think of an easy way to do what you need. That would require sorting the items by price, and aside from sorting records (which wouldn’t get you anywhere), Airtable currently doesn’t have a way to sort anything else. It likewise is quite limited in what you can do with arrays. There’s no way to pull part of an array, for example.

That said, your sweatshirt example leaves me slightly confused. If an employee is going to order 7 sweatshirts, why would they get different sizes, which would cause you to have to figure out which are the most expensive? My gut says they’d order 7 of the same size, so it then just becomes a matter of figuring out the quantity difference between their order and their actual allotment, which should be pretty straightforward. I guess I can’t picture a situation that would actually require you to calculate the overage by price instead of by quantity.

[AlliAlosa]

Thanks Justin, I’m starting to agree that there is no easy way to do this.

As for your second thought - you’re correct that there’s a slim chance that they would order different sizes, but it has happened in the past. For example, if a manager gets 5 size 2XL sweatshirts for themselves, and then wants to get a size Medium for their spouse or child.

Another example; we have an assortment of long-sleeve/short-sleeve polos and long-sleeve/short-sleeve buttondowns. An employee can mix and match up from that group up to their allotted amount, and each set has a different associated cost.

I think you’re right in saying that without being able to sort the array, this isn’t going to be possible :frowning: I guess if there is an adjustment needed, we can just put it in manually. Maybe someday we’ll get some advanced array functions!

[Justin_Barrett]

Gotcha. I somehow assumed that these clothing items were for work use only, and didn’t think that someone might order for family as well.

[JonathanBowen]

Hi @AlliAlosa, @Justin_Barrett - thought I’d have a look at this one. I’m getting close to it (maybe you’ve got this far too?)

Airtable

Uniform - Airtable

Explore the "Uniform" base on Airtable.

In this base, once you purchase more than 2 items, it removes the least valued item from the total:

Problems with this:

1. I can only get it to remove the least valued item, not the two least valued items (if you purchase 4 items, for e.g.)
2. I can only work it so that you can purchase each item once (is there a way around this?)

Not sure if this can be worked up to your exact requirements. Let me know if you can see a way!

JB

[Mike_McLaughlin]

Well, i worked out how to do this with a Zap, but 2 downsides, 1) zap has more than 2 steps, so you need to get a paid plan for past the trial period, 2) only auto-runs every 5 minutes.

[AlliAlosa]

Wow! Thanks everyone for the help on this.

@JonathanBowen I was pretty much at the same point as you, but also couldn’t extract more than one MAX or MIN value from the array of prices. Thank you so much for bringing up a HUGE point that I had neglected to mention until now - the quantities of each item haven’t come into play here yet.

I planned to spread this out across a couple of tables once this part of the puzzle was solved - Rather than linking directly to the [Items] table, I’ll link the [Clothing Orders] table to a table called [Outgoing Items]. That table links to [Items], and essentially becomes a list of line items for each Clothing Order, to which I can add the quantities.

@Mike_McLaughlin - I seriously can’t thank you enough! I do have a paid plan with Zapier already, and would have been setting up a Zap for this workflow regardless of solving the array sorting issue - so THANK YOU so much for this. Can’t wait to test it out. Also, the panda thing cracked me up! :rofl:

[Mike_McLaughlin]

Not thats its complex, but in case you wanted to take a shortcut to meet the Pandas:

  priceList = input['prices']
    print('these are the prices:', priceList)
    strLst = priceList.split(',')
    fltLst = [float(i) for i in strLst]
    print('Some Pandas split this up:', fltLst)
    fltLst.sort(reverse=True)
    print('Then they sorted it:', fltLst)
    if len(fltLst)>5:    
        x=1
        for x in range(0,4):
            fltLst.pop(0)
            x+=1
        amtDue = sum(fltLst)
    else:
        amtDue = 0.0

    print('Then they got hungry and ate part of the list:', fltLst)
    print('Pandas poop:', amtDue)
    output = [{'id': input['recordID'],'Amount_Due': amtDue}]