Skip to main content

Comparing the DATE field of all records in a table versus DATE RANGES of ALL records in another table

  • November 19, 2021
  • 4 replies
  • 44 views
J

The setup:

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.

4 replies

Justin_Barrett
Forum|alt.badge.img+21

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.

What you want can only be accomplished using a script. Are you familiar with JavaScript? If not, are you interested in learning, or would you like to hire someone to write a script for you?

J
J
  • Joshua_Vogel1
  • Author
  • New Participant
  • November 19, 2021

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.

What you want can only be accomplished using a script. Are you familiar with JavaScript? If not, are you interested in learning, or would you like to hire someone to write a script for you?


Hey Justin… thanks for getting back to me. I am familiar with javascript.

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.

ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • November 19, 2021

Also, in addition to writing your own custom JavaScript script to do this, you could also use a low-code automation platform like Integromat as well. I have several clients who are doing something nearly identical to what you’re trying to do, and I’ve got them setup with Integromat.

Justin_Barrett
Justin_Barrett
Forum|alt.badge.img+21

Hey Justin… thanks for getting back to me. I am familiar with javascript.

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.

Because you mentioned that you’re familiar with JavaScript, I would encourage you to run some tests in the Scripting app if you’re interested in attacking the problem yourself. Feel free to share your endeavors here if you run into hiccups, or reach out directly if you’d like more focused help in solving the problem.

Terms & ConditionsAccessibility statement