topic Re: Expiration Date Notification to Email via Survey123 in ArcGIS Survey123 Questions

Expiration Date Notification to Email via Survey123

JeffDavis7 — Wed, 12 Feb 2020 14:41:35 GMT

Hello,

I've created a form using Survey123 Connect that contains electric meter information. Within the form, I have added "date" field which allows me to select some future date. This future date lets me know when I need to change the battery in our meters. I use Integromat for all my notifications using the Survey123 module coupled with the Send Email module.

What can I use to integrate with Survey123 and Integromat to send me an email notification when this future date has been reached?

Thank you.

Jeff

Re: Expiration Date Notification to Email via Survey123

BenTurrell — Thu, 13 Feb 2020 04:49:45 GMT

Hey Jeff Davis‌,

You could write a python script that takes care of the email sending. I would write a script that checks todays date and the date field in your feature layer. If the date is the same or within the time window you send an email. You can then use the windows task scheduler to schedule the task to run daily.

Thanks,

Ben

If this answer has helpful please mark it as helpful. If this answer solved your question please mark it as the answer to help others who have the same question.

Re: Expiration Date Notification to Email via Survey123

JeffDavis7 — Fri, 14 Feb 2020 11:39:35 GMT

Hey Ben,

Thank you for responding to my inquiry.

Unfortunately, I do not have any Python experience.

Thank you though for your suggestion.

Jeff

Re: Expiration Date Notification to Email via Survey123

EricChez — Fri, 14 Feb 2020 11:48:31 GMT

You can probably dump the survey results to excel or google sheets, and have a filter condition to check for the date and send an email when that condition is met.

Re: Expiration Date Notification to Email via Survey123

EricChez — Fri, 14 Feb 2020 11:50:35 GMT

If you are good with JSON, you can probably query the endpoint for the hosted feature class, parse the json to get the fields, and plug it into an email

Re: Expiration Date Notification to Email via Survey123

JeffDavis7 — Fri, 14 Feb 2020 11:53:53 GMT

Hi Eric,

Thank you for responding to my post.

I must seem somewhat like a broken record, but...I am not a JSON manipulator either.

However, your suggestion of using Google Sheets has me curious. Is that a module in Integromat?

Thanks.

Jeff

Re: Expiration Date Notification to Email via Survey123

EricChez — Fri, 14 Feb 2020 12:16:59 GMT

Jeff,

No worries. Yes Google Sheets is a module in integromat. You can choose the "Add a row" function when a survey is submitted/edited to populate the spreadsheet, you can create a separate scenario to "Search Rows" for the date criteria, and send an email based on those matches.

Hope this helps.

Eric

Re: Expiration Date Notification to Email via Survey123

JeffDavis7 — Fri, 14 Feb 2020 12:25:05 GMT

Eric,

I've attached a screen capture illustrating the current scenario I've created in Integromat that supplies an email notification based on a submitted/edited survey from Survey123.

Would Google Sheets be integrated within this scenario or be a stand-alone scenario?

Thanks.

Jeff

Re: Expiration Date Notification to Email via Survey123

EricChez — Fri, 14 Feb 2020 12:35:28 GMT

add a router between the two modules so you can split and connect the "Add a row" google sheet function

Re: Expiration Date Notification to Email via Survey123

JeffDavis7 — Fri, 14 Feb 2020 12:39:41 GMT

Thanks, Eric.

I will definitely try it out.

As a "heads up"...I may touch base with you again if I have questions.

Jeff

Re: Expiration Date Notification to Email via Survey123

JeffDavis7 — Fri, 14 Feb 2020 12:47:37 GMT

Eric,

I've added another screen capture. Is that the workflow you were indicating?

Thanks.

Jeff

Re: Expiration Date Notification to Email via Survey123

JeffDavis7 — Fri, 14 Feb 2020 15:43:52 GMT

Eric,

I've expanded a bit beyond my earlier screen capture. Please see "Battery Inventory New Part2" screen capture. I am not quite sure what to apply for the "Spreadsheet" and "Sheet" sections for the Add a Row dialog box. Can you possibly offer some suggestions?

Thanks

Jeff

Re: Expiration Date Notification to Email via Survey123

EricChez — Fri, 14 Feb 2020 17:20:28 GMT

I suggest creating a Google spreadsheet with the columns you want to log first, and in your Google Sheet module, change the mode to "Spreadsheet and Sheet", select the google spreadsheet and sheet, and then map out each field with the corresponding feature\attributes from survey123 data. See image:

Re: Expiration Date Notification to Email via Survey123

JeffDavis7 — Fri, 14 Feb 2020 18:24:11 GMT

Eric,

Thank you so much for sticking with me on this. I've uploaded...yes...yet another screen shot (...Part3). I am having difficulty...big surprise...with creating a spreadsheet...at least I think so since when I attempt to reference the spreadsheet in Google Sheets Add a Row...it doesn't see it. I am assuming somewhere in the spreadsheet there is a problem but I can't see it. Can you?

Thanks.

Jeff

Re: Expiration Date Notification to Email via Survey123

JeffDavis7 — Tue, 25 Feb 2020 13:02:04 GMT

Eric,

I've updated my attachments. Lots of changes since I started this adventure. Using Google Drive I was able to create a Google Spreadsheet featuring two headers; one for meter number and another for replacement date. The attachment entitled "Google Sheets-Add a Row" corresponds to my configuration of that scenario. I've tested this scenario and it functions very well. An alert is provided via email and the Google Sheet is populated with the corresponding meter number and replacement date from the survey. That's great!

Can you provide some guidance on the next stage? Following your suggestion, I have created a second scenario illustrated in my attachment as "Google Sheets-Search Rows". The final attachment to this posting entitled "Google Sheets-Search Rows Configure" illustrates a screen capture where I am a little stumped. I have a column in Google Sheets entitled Battery Replace Date (MM/DD/YYYY) and Meter Number. Ideally I would like someone to receive the reminder corresponding to the date and supply them with which meter requires its battery to be replaced.

Thank you in advance for any assistance you can provide.

Jeff

Re: Expiration Date Notification to Email via Survey123

EricChez — Tue, 25 Feb 2020 15:02:34 GMT

Hi Jeff,

You are going to want to setup a filter that looks at your battery replace date and checks to see if its the same as when the scenario runs. You can right click the connection between the Google Sheet and Email and set the condition formatDate([Battery Replace Date]; MMDDYYYY) equal to formatDate(now; MMDDYYYY) so that when the criteria is met, it will execute your Email part.

If you need help on working with functions within Integromat see Date & Time Functions – Integromat Support

Hope this helps.

Re: Expiration Date Notification to Email via Survey123

JeffDavis7 — Tue, 25 Feb 2020 17:57:51 GMT

Hi Eric,

The filter worked perfectly! Thank you for that suggestion. I really appreciate your help with my project.

The only other stumbling block I am experiencing is the scheduling function. Since the Google Sheet is not a Trigger, "Immediately" is not available as a selection. I'd like to have it just run one time without intervals. When I choose "Once" Integromat wants you to enter a date. The dates can obviously change depending on the replacement date of the battery so one absolute date will not be valid.

Jeff

Re: Expiration Date Notification to Email via Survey123

EricChez — Tue, 25 Feb 2020 18:12:24 GMT

Jeff,

Glad to hear it worked for you. For your scheduling, I would set it to run "Every Day" at a set time (probably before the work day starts). If you only want to run once for testing purposes, just select once and set the date and time for it to run.

By setting it to run every day, it will check to ensure the battery replacement date is set to that date, and email once for that day if the criteria matches.

Re: Expiration Date Notification to Email via Survey123

JeffDavis7 — Tue, 25 Feb 2020 18:16:50 GMT

Eric,

Thank you. The Every Day option is exactly what I need. Thanks again for all your help. You were an excellent resource. Appreciate your time.

Jeff

Re: Expiration Date Notification to Email via Survey123

EricChez — Tue, 25 Feb 2020 18:21:14 GMT

Glad to have helped. Take care.