I have one table which is used to schedule marketing emails (each record contains a single date).
I have another table which is used to schedule multiday promotions (each record contains a start date and and end date).
What I’m looking for:
For each record in my email-scheduling table, I would like a field that links to the records of all the promotions from the promotion-scheduling table that are ongoing during the day the marketing email is scheduled.
Any help would be appreciated. Thank you.
Welcome to the community, @Joshua_Vogel1! :grinning_face_with_big_eyes: You put your post under the “Formulas” category, but unfortunately formulas can’t do this. Formulas execute at the record level, meaning that when a formula runs in a given record, it’s pulling data from the same record. Direct access to other records—in the same table or other tables—isn’t possible with formulas. On top of that, formulas don’t currently have the ability to iterate through collections of items, and formulas can’t change the contents of other fields.
It’s the iterations that are killing me. A foreach() formula would probably carry me across the finish line, because by linking all the records of each table to a single record in a third table, I can use lookups and rollups to get all that data into a single table.
But… yeah, if I can’t iterate through the arrays this doesn’t work.
I don’t think you’d need a third table. In your original post you talked about just connecting the two tables (emails and promotions), which is doable without adding a third table to the mix. It’s just a matter of writing a script to do it.