How to create one list that adds and merges like items?

[Emily_Magid]

I’d be so grateful for any advice on how to clean up a rollup that returns this:

1 shirt(s), 1 pant(s), 1 set of socks, 2 shirt(s)

To this instead:

3 shirt(s), 1 pant(s), 1 set of socks

Basically, my question is how to merge and add like items from a list generated by a rollup.

(In case it helps, the list needs to be dynamic - not one list for the entire base if that makes sense. Each item is associated with a packing list, and then packing lists are added to trips. I want to be able to create one packing list for each trip that does not repeat items when there are multiple packing lists per trip.)

Thank you!!

[Justin_Barrett]

Welcome to the community, Emily! :grinning_face_with_big_eyes: You didn’t describe your base setup, but my hunch is that you’re rolling up all of these items into the record for the trip on a [Trips] table. Because the trip is pulling from several packing lists, and each packing list has its own count of shirts (for example), that’s the reason for the duplication of shirt entries.

Unfortunately Airtable records effectively have no knowledge of each other. There are limited ways around that, but they won’t work for all scenarios. Long story short, without some cross-record knowledge it’s impossible to know how many similar items may exist across several packing lists.

That said, there may still be a way to get what you want, but the solution will largely depend on the structure of your base. Could you share some more details (screenshots would be a huge help) of the tables you’ve made, and the relevant fields in each?

[Emily_Magid]

Thank you for your help, Justin! I really appreciate it!

Here are screen shots of the base. My goal is to get one final packing list where like items are merged and added. (E.g., instead of listing shorts twice for the Bar Harbor trip, it would simply say “3 pairs of shorts”) As you mentioned, it sounds like I’ll have to change the structure of my base? Or maybe there is some fancy formula that would convert the “Final Packing List for Each Trip” rollup? I’m open to anything!

Any advice?

[JonathanBowen]

Hi @Emily_Magid - Rather than show the list of items against the Trips table, I think you want to get the trip name onto the items table, which you should be able to do using a lookup against the packing lists field (my simplified version below):

Now I can create an additional view on this table for, say, Bar Harbour. I can filter where trip = Bar Harbour and group by item. You end up with this:

Hope this helps!

JB

[Justin_Barrett]

Thanks for sharing your base breakdown. After seeing what you’ve got, my suggestion is to build your final packing list from the [Packing Items] table. That’s where you have everything coming together for each item. Using the links made on the [Itemized Packing Lists] table, you can add a rollup field pointing to the {Amount} field, using SUM(values) to aggregate them. To go a step further, you can actually write a full formula in the aggregation function area, so you don’t just have a number. (I put the {Descriptor} as part of the [Packing Items] table, as it felt more appropriate there, which is why it’s in this formula)

[Emily_Magid]

Hi Justin,

Thank you so much for working on this and helping me. It is so helpful and I appreciate it so much! This is amazing!! My only question is - when I do this, it seems to roll up every single packing list in the entire base, rather than only including the items on the specific packing lists needed for each individual trip? Is that right or am I missing something? Is there a way to limit what is rolled up so that it only includes the subset of itemized packing list items needed (the subset associated with the specific packing lists needed for a trip)?

Thank you so much again!

[Emily_Magid]

Hi Jonathan,

Look at that! Amazing! Thank you so much. I really appreciate your help!

Could I just ask a few quick follow up questions:

• do you know if there is a way to make this final list printer friendly?
• is there a way to get the description (e.g., “vanicream brand”) to show up on that top line where the item and sum are listed?
• do you have any advice on how I could keep track of what is packed so I can take it off the list as I go? Perhaps using checkboxes?

Many thanks again!!

[Justin_Barrett]

Emily_Magid:

it seems to roll up every single packing list in the entire base, rather than only including the items on the specific packing lists needed for each individual trip? Is that right or am I missing something?

That’s right, and admittedly that’s a detail that I overlooked when making that suggestion. My apologies.

Emily_Magid:

Is there a way to limit what is rolled up so that it only includes the subset of itemized packing list items needed (the subset associated with the specific packing lists needed for a trip)?

…maybe? I haven’t explored your setup much, but the way you’re breaking it down in such detail actually makes re-combining certain data elements more complex. I’ll try to give this some more thought, but it may be tricky to do around other work I’m committed to address.

[JonathanBowen]

Hi @Emily_Magid - on your follow-up questions:

• You can do a print of the grouped view, although not too pretty:

• You could create a new field - Item/Brand - which is the concatenation of these two fields:

Then, in the grouped view, group by this field, rather than the item field:

• Yes, checkboxes sounds like a good idea, although this will be at the item level, not the “grouped” level, i.e. you will need to check off, two shorts, then 1 shorts:

JB

[Emily_Magid]

Hi Jonathan,

This is so helpful! Thank you so much!! I really appreciate your help!