Related field using a formula using NOW()

[Brite_Admin]

It seems like this should be easy but I am so confused.

I have two tables. The NAMES table has a NAMES Field and a SEQUENCE field that is a formula field that calculated how many days ago the record was created. Therefore, The SEQUENCE Number for a record increases by 1 each day.

The MESSAGE Table has a MESSAGE# field with 100 records numbered 1-100 and a text field called MESSAGE.

I am trying to make them related fields so I can see which people from the NAMES table have the same SEQUENCE # as the number in the MESSAGE# field.(so the associated records for a MESSAGE Number will change each day).

If it makes any difference, I will be using this to automate sending out SMS messages but am stuck with the setup for some reason/

Any help is appreciated.!

[Databaser]

Hi @Brite_Admin

You could set up an automation like this:

make a linked field in the “names” table to “message” table

trigger: “when a record is updated” > only the field “sequence”
action: “update record” > update [name of linked field in “names” table"] with the value in “sequence” field.

As the sequence field changes 1 time every day, the number of that sequence (eg “2”) will be put in the linked field. As your primary field in the “message” table also contains only a number, it will overwrite the value of the linked field each day. In the “message” table, you will then have a list of all the “names” who have the same sequence/message.

You can then build your SMS automation from there.

Does that help you?

[Brite_Admin]

I appreciate your help but sorry but I am still confused or perhaps there is a miscommunication. Maybe me calling it a sequence caused some confusion. There is only ONE sequence with 365 different messages, one for each day.

What I am trying to accomplish is the ability to have a series of messages so that if personA is added on January 1, they receive message 1 on January 1, message 2 on January 2, etc and if personB is added on January 15, they will get message 1 on January 15 and message 2 in January 16th, etc.

The table with Message Number and Message seems correct but the linking and set up isn’t working for me to bring a message.

What confuses me is the second table. I am using a formula to determine which message# each person should receive today (DATETIME_DIFF(NOW(), Created,‘Days’)+1)

What I can’t figure out is how to associate the result of that formula so it brings in the correct MESSAGE from the Message table that corresponds to the Message#. I tried using a lookup field in the Names Table to bring on the correct message based on the Message# but it doesn’t allow me to do that, Is there a way to LOOKUP

[Databaser]

Hmm, I’m stuck… I don’t think it is possible (keeping your Airtable clean) without external parties like Zapier or Integromat. Finding and sending the first message isn’t a big problem, but to change the message number each day and sending the corresponding message… :man_shrugging:

But maybe someone with more expertise than me will show up in this thread :slightly_smiling_face: