Create an entry for each specified day between two dates (ie for each Monday next month)

Hey y’all,

I’m racking my brain trying to figure out how I’d do this with Appsheet. I sell a recurring service where we stop by the customer’s house at specific frequencies. I’d like to use Appsheet to make my service schedule. To do this, I would need to take a list of each active subscription on a route (day of the week), and have the system create an entry for each service day in the next time period.

So I’d like to take a list of each Monday subscription, then set a date range and have the system create an entry (schedule) for every upcoming Monday in the specified date range, creating scheduled jobs for each recurring subscription.

Hopefully my post makes sense, I’d love to hear any ideas. Thanks!

You can do this, but it is an advanced method and involves using the AppSheet API. Here is the general process to follow:

  1. Create a new spreadsheet table called “Dates” that contains a list of dates from today until 2 years in the future. (you can do longer or shorter, just know your app will not work past the last date on this list. I would also create a scheduled action that adds a new date to the bottom of the list each day so you always have 2 years of dates.

  1. In your table where you define the start and end dates, create a virtual column called [Date List] that will be a list with the formula:

select(Dates[Date],and([Date]>=[_thisrow].[Start Date], [Date]<=[_thisrow].[Finish Date]))

This creates a list of dates for that specific record.

  1. Now is where it gets a little complicated. You need to enable the API by going to Manage->Integrations

  2. Create a workflow that will run for each new record added to your task table. This workflow will trigger a webhook which will then loop through each date in the virtual column specified above and write a record for each date in the range specified. I created an example webhook body below to show you how to make Appsheet loop through and create a record for each date.

For more details about adding records via the API check this out:

Let me know if anyone has any questions.

2 Likes

Rich, thank you so much for taking the time to show me this. It looks like I can do what I want with this method! I was looking at it from a much more complicated angle using a mix of webhooks and google apps script on the sheets side of things.

I appreciate it!

Your post inspired me! I spent the last day and a half putting together this sample app to demonstrate how to do what I think you want entirely in-app.

https://www.appsheet.com/samples/community25013?appGuidString=eab9aab8-f1ad-4fd1-9fa4-d22e0f961067

I went a little overboard, so there’s more than you expressed an interest in (as I said, I was inspired!), but the basics are in there. If you need help teasing out only what you need, just speak up!

Email and related notifications are implemented with workflows, so will require you make your own copy of he app to test.

4 Likes

I like how you named the app after the ID for this post lol. Good way to keep track of community sample apps!

2 Likes

This is great, thanks Steve! I didn’t know about IFS functions before, now I’m just trying to wrap my head completely around what’s going on here. I really appreciate the sample!

1 Like

Feel free to ask questions.