linking dates from a calendar table with multiple linked record fields

[hubbird]

Hi all,

We have a marketing calendar table which links a CAMPAIGN to individual DISCOUNT records in another table. Each CAMPAIGN has 4 or 5 DISCOUNTS in successive panels, and so we have 5 linked records from each CAMPAIGN to the DISCOUNT table, labeled Panel 1 - Panel 5. I would like to have ideally one field in the DISCOUNT table corresponding to the date of the associated CAMPAIGN, but I can't figure out how to aggregate the multiple PANEL fields into one single lookup. Is this possible?

I've tried Concatenate & run into issues with datetime format, as well as issues concatenating empty records). I'm sure there's a way to set it up with IF functions that might work, but figured I'd see if there's an easier way!

For context, I know this might not be the most technically "correct" way to set this up, but I'm trying to convince my team to get on board with Airtable by replicating the functionality of a Google Sheets calendar they were using and adding in some additional bells & whistles.

Thanks for any assistance you can offer!

[TheTimeSavingCo]

Hmm, could you provide screenshots of some example data within your tables, and an example of what you'd like to see from those examples?

Sorry, would love to help but I can't really visualize your setup!

[hubbird]

Here's our "Marketing Calendar" table (or one view of it anyways)

Each "Panel" field (Panel 1, Panel 2, Panel 3 etc) is a linked record in the "Codes" table here: 

I want the records in the codes table to display the date corresponding to any of the "Panel" fields they're linked to on the Marketing Calendar, ideally aggregating the earliest date if there are multiples. Does that help?

[TheTimeSavingCo]

Yes, that helps a lot!

Does this look right to you?

I wasn't sure whether you wanted the earliest date for each specific panel, or the earliest date for the code so I ended up providing both

The earliest dates for each panel's easy enough to set up with a Rollup field with `MIN(values)`.  The earliest date for the entire code was harder to deal with and I ended up converting the dates to a timestamp, using MIN on them, and then converting it back into a time heh

You can access the formulas used by duplicating the base.  Let me know if you have any questions!

[hubbird]

Thanks Adam,

This looks perfect! I'm gonna try to duplicate what you did on my own for the learning process, but just in case I need to cheat — I didn't see a link to the base in your message. Did I miss it?

Thanks again for the help, this is awesome.

Ben

[TheTimeSavingCo]

Oh so sorry, I forgot the link!  Here it is