I have one table that’s a list of PURCHASES. Each purchase can be one of multiple colors, has a quantity, and is going to one of multiple LOCATIONS - so it’s many-to-many, each color can go to many locations and each location can have many colors.
On a separate LOCATIONS table I’d like to be able to neatly roll up / lookup the quantity and color of ITEMS stored at each LOCATION. Since there are 20+ colors I’d love to avoid doing a separate column for each color.
In a perfect world I’d have one field that tidily summarized the entirety of which colors and quantities are at which location from the ITEMS table, e.g. for each location a single field showing ‘red - 23, blue - 47, green - 14’ or similar.
I am able to look up the quantities for each purchase per location, but it’s a list of numbers with no labels, some of which will be different orders of the same color (not an array of sums of each color) so it’s hard to tell what’s what by looking at that.
I have tried some other stuff with rollups and different formula displays to no avail. I suspect it needs a bit of clever work upstream in combining the color/quantity data before looking or rolling it, but I haven’t cracked it yet.
Maybe doing it by column brute force is the only way, filtering by color and doing a sum and a formula to link the label – but it feels like there should be a more elegant judo move somehow. This is either super hard, or super easy, but in either case I’m not seeing it.
Hopefully this makes sense, and any help appreciated, thanks in advance!
