Hello, I'm fairly new to AppSheets, and I'm ...

(Phil Robertson) #1

Hello,

I’m fairly new to AppSheets, and I’m hoping for some insight on how to best solve a problem.

I need to send an SMS to my customers when their orders are fulfilled, however I only want to do this after a specific time in the morning that corresponds to their selected pick-up time, so as not to disturb them too early in the morning.

For example, their orders might be fulfilled at 5:30am, but they might have indicated a 7:30am pick-up time, in which case I want to delay the sending of the SMS until 7:30am.

I allow discrete pick-up times on half-hour intervals, so I can create Reports that run at 7:31am, 8:01am, 8:31am and 9:01am, etc. to send an SMS to a customer if their order is fulfilled and they’ve indicated a pick-up before the current time (i.e. at 7:31 send SMSs to all customers with fulfilled orders who have 7:30am pick-up times).

The issue with this strategy (besides requiring a lot of reports!) is that I trigger multiple Reports (i.e. 7:31am then 8:01am, etc) unless I set a status field indicating that an SMS has been sent.

The challenge with that is that Reports seem to only allow me to take only one action, either to send an SMS or update a field.

In which case, I would need to create a 7:32am Report just to update my status field to “SMS sent”.

This is cumbersome to say the least!

Can anyone foresee a better way to solve this??

Thanks so much!

(Philip Garrett) #2

@Phil_Robertson

I can see two alternatives.

  1. If your “Pickup” is a date time field, and if you can live without updating the “Status” field, you could simply run the report on the half hour as you described. Your report could

send the SMS to those users with a “Pickup” date time within the next 30 minutes.

The drawback is that the “Status” field will not be updated, so you cannot be certain that the report has run. It is possible that a system error might prevent the Report from running as expected. The Report service is quite reliable, but it is not entirely failsafe.

  1. You could run a report on the half hour as you described. However, the report would trigger a webhook that invokes the API to update the “Status” field for each record that is targeted for delivery.

When the API performs the update to the “Status” field, it will trigger any workflow rules that you define.

You could define a Change workflow rule that is triggered by the update of the “Status” field. That workflow rule could send the SMS to the user.

Someone else in the community may have a better idea.

(Phil Robertson) #3

Thanks Philip!

I like your 2nd idea there.

Instead of using a Webhook and the API couldn’t I just have my Report “Change Data” to update the Status field, then still have my Workflow ChangeData rule trigger the SMS or am I missing something?

(Philip Garrett) #4

@Phil_Robertson

No, because Data Changes don’t trigger workflow. We were worried about infinite recursion. We avoided that by not triggering workflow rules for changes made by Data Change actions.

We trigger workflow rules for the API because the API is typically triggered by external clients. Since the clients are external, infinite recursion is less like.

Triggering workflow rules for changes made via the API is powerful, but several customers have unwittingly run in infinite recursion problems when they invoke the API from a webhook. We probably need to detect infinite recursion and block it somehow, but at present you can cause serious problems if you are not careful.

(Phil Robertson) #5

Great, thanks for the info.

I’ll give this a try.