Hello all,
This is my first time posting here! Apologies if the explanation is not the most succinct.
I need help with creating a formula to work within an intricate email and list management system I've set up.
I have two tables:
- Email --> Each record is an email being produced, tracking copy, design, and deployment
- E.G. "Women's Board meeting 2 invite email"
- List management --> Each record corresponds to a named list of contacts from our CRM.
- E.G. "Women Board members"
Both of these tables are linked to each other
- Each record in the email table is linked to at least 1 list record, indicating the audience the email is going to
- Each record in the list table has 1 or more emails linked to it, indicating the past and future emails that have or will utilize this list's audience
Fields to be aware of
- The Email table has a "Deploy date" field --> indicating when the email will drop
- The List Management table has a look-up field pulling in the "Deploy date" data from the Email table
- Since a list is usually linked to more than one email, the look-up field pulling in the "Deploy date" data contains multiple dates, not just one
- We build our emails in advance, so the look-up field pulling in the "Deploy date" data can display dates from the past, and MULTIPLE dates in the future
- (this happens when we plan to use the list record for many emails in the future)
Formula needed
I need a formula that will use the data in the List Manager table's look-up field pulling in the Email table's "Deploy date" data to find the closest date to today in the future. In other words, find the most "upcoming" date of when the list record will be used for an email deployment.
- E.G. Today is 04.21.23, the "deploy date" look-up field contains the dates
- 04.19.23 --> (Past date)
- 04.24.23 --> (Future date: closest to today, 04.21.23) I need the formula to pull out this date
- 04.21.23 --> (Future date)
---------------------------------------------------
After searching online for multiple solutions and coming up short, I've decided posting here may be the best way to help source this assistance. Very grateful for any and all suggestions this community may have. Please let me know if I can clarify the problem in any way, and thank you for your time!
