Automated email after 90 days + users updating their own records via a form sent in the email

[BGWiki]

I’ve been working on an automated loop for a couple of weeks and I don’t seem to be making much progress. This is for a research participant database.

I have a record of the last time they were contacted. In 90 days past the date of last contact, I would like an email sent automatically asking if they would like to continue to be in our database. They fill out a form, hopefully with their names/usernames/emails/phone numbers already populated, but with the option to edit their personal info in case something has changed. The form also asks if they want to participate in future research. This form automatically updates their entry in the database and timestamps when they opted in/out of future research. After 90 days, those who opted in get another automated email, and the cycle repeats.

So after a couple of weeks of research/trial/error, this is the process I imagine I should be creating but have not yet been successful in executing:

I’m trying to get Integromat to identify when the field “Next Contact Date” is =TODAY. When this happens, it creates a new record in the “Interactions” table of my database. This new entry triggers the automated email via Zapier, which contains a link to a form (not sure if I should be using a FormNano or simply the “form” view of another table in my database). I would rather have the “next contact date”=Today to trigger the email, but that doesn’t seem to be an option. The research participant fills out the form, which automatically updates the newly created record in the Interactions table with the questions I’ve asked (name, email, future participation?). This is record update is timestamped, 90 days is added to the timestamp in the “next contact date” field, and the cycle continues.

I’m getting a little lost in which 3rd party apps do what. Just generally integrating the 3rd party apps into airtable has only partially worked, mainly because the options they give for setting them up are not quite the options that I feel like I need. Any advice would be great. Thanks

[Arlo_Haskell]

Hi @BGWiki –

You’re laying out a multi-step problem combining multiple third-party apps, and I have a feeling you could step back and simplify the whole thing.

For example, while I’m not familiar with Integromat, it doesn’t seem like you need to be using it at all in this case. If you want to create an automated email, you can just do that with Airtable and Zapier alone.

First, create a filtered view in Airtable that shows only those records where “Next Contact Date” = TODAY. I can’t tell if you’re doing this within Airtable or trying to rely on Integromat for it, but within Airtable one way to achieve it would be to create a new formula field using the IS_SAME function; then set the filter to look for “1” in that field.

Once you’ve configured the filtered view to show the records that you want to use to generate the email, then you can go ahead and use the “new record in view” trigger option in Zapier.

This is only a partial answer to what you’re looking for, but I hope it helps.

[Jeremy_Oglesby]

A big thumbs up to @Arlo_Haskell’s suggestion to handle as much of the logic as possible in Airtable. I have found that the more complexity you load onto Zapier or Integromat’s end, you begin to exponentially increase the fragility of your system. With those external automation tools, I would always suggest trying to think in terms of how you can trigger a simple action in Zapier (for example) with all the logic handled in Airtable – and usually there is a way to make that happen (sometimes you have to be a bit clever and do some interesting field jockying, but you can make it happen!).