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/
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.
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
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: