How do I iterate through a Date Range?

In this episode, we'll learn how to iterate through a Date Range to create records for each date within the range using Make and Date Range fields.

Features Discussed:

1. Webhooks (Time: 01:38)
   Learn how to create a webhook, connect it to your application using API credentials, and configure it to trigger on specific events like creating or updating records. You'll learn how to select the appropriate data table and integrate the webhook into your automation workflow. When a record is updated, the webhook captures and sends essential data, such as the table ID, record ID, and field values, to the specified URL for further processing.
2. Outgoing Webhooks (Time: 05:20)
   Learn how to subtract the start date from the end date and convert the difference from milliseconds to days using a formula. It also covers the adjustment needed to ensure accuracy, such as adding one extra day to account for the inclusive date range. This method is useful for creating records for every day within a specified date range.
3. Repeater Module (Time: 07:08)
   Learn how to specify an initial value and determine how many times the action should run. In this case, the module is configured to run for the number of days calculated previously, plus one. It starts with zero, meaning the first record generated corresponds to the start date plus zero days, ensuring accurate record creation for each day in the range.
4. Creating the Records By Webhook (Time: 08:10)
   Learn how to create records dynamically by using the "Create a Record" database module. In this example, records are created in a connected child table, with dates calculated by adding days to the start date. The number of days is derived from the Repeater module. Additionally, the connection field utilizes the record ID from the initial record received through the webhook, ensuring that the new records are correctly linked to the parent record.

Transcript Summary

Introduction

Hey everybody, welcome back to another episode of Build It with Tadabase. On today's episode, we are talking about some extracurricular functions that we are doing outside of our database application, and we're going to be using Make to do this. We're going to be iterating through a date range field so that we can find out all of the dates within the date range field and create new records in another table for each one of the dates.

That's what we're going to be doing. We'll take a look at the builder itself, and of course, Make, and build this out for you.

Setting Up the Data Structure

Tim: First things first, we have a very simple data structure. All I want to show you here today are the minimum requirements for doing this. There are going to be two tables: one table that actually contains the date range field, and the second table is going to be a connected child table. This table is going to contain a connection field that goes back to the date range table.

For every single date range that we have, we're going to find out all of the days between the start and the end date, and then we're going to create individual records with that particular date. Each one is going to connect back to the parent record, which is the one that contains the actual date range.

Building the Scenario in Make

We're inside of Make, and what we're going to be doing here is building a fairly simple scenario to handle all of this logic. There are going to be about four steps that we need. Your scenario might vary if you need to build in any additional logic, but in my case, these are the base requirements for the operation that we're doing.

1. Receiving Data: We're going to somehow receive the data from our database application. This can be done in a number of ways—you could schedule it, or trigger it via a webhook. I'm going to show you the webhook method.
2. Calculating Days: We need to calculate how many days are between the start date and the end date of the date range.
3. Repeater Module: We're going to use something called the repeater module in order to output a certain number of bundles that Make users will use to fire the next module.
4. Creating Records: The next module will create a record for each date, starting from the start date and adding the necessary number of days.

Setting the Time Zone in Make

The first thing I want to cover here in Make is in your profile. I suggest going to your profile and editing your time zone to GMT plus zero (UTC) when working with dates inside of Make. This makes things more compatible between your database application and Make, as both have a time zone associated with them. Setting Make to GMT plus zero ensures that you don't have to convert anything.

Setting Up the Webhook

We're going to start with that webhook. I've already created one here, but you'll need to search for the database module and select "Watch Records." Then, create a new webhook, add your API credentials to make the connection between this particular webhook and your application, and select the data table. It will provide you with a web address.

Inside your application, go to Automation and Outgoing Webhooks, and paste those values in the URL. I've set up two webhooks: one for a new record event and one for an update record event. When I now update this record, it will receive the record data we need, such as the table ID, record ID, and individual field values.

Calculating the Number of Days

Next, we're going to set up our two tool modules. The first one is located in the Tools category and is called "Set Variables." We're going to calculate the number of days between the start date and the end date. The formula subtracts the start date from the end date, divides it by 1000 (to convert from milliseconds to seconds), by 60 (to minutes), by 60 (to hours), and by 24 (to days). Then, we round that value.

When we run this, we'll get the number of days. It's not 100% accurate because it either doesn't count the start date or the end date, so we need to add one additional day to the value we received to ensure we're creating records for every single day within the range.

Setting Up the Repeater Module

The next thing we're going to set up is the Repeater module. This module allows us to define an initial value and the number of times we want it to run. We need the number of days plus one for the bundles, starting with zero because the first record should be the start date plus zero days.

Creating the Records

Now, we're going to add another database module called "Create a Record." Select the table where we're going to create records (the connected child table). For the date, we're going to add days to our start date. The number of days will come from the Repeater module. The connection field will use the record ID of the initial record that came in through the webhook.

Running the Scenario

We're going to run this now. We'll edit the record and switch back over to Make to see everything working. We'll receive the webhook, get the number of days in between (adding one), and then create records starting from the start date and moving forward.

Viewing the Records

Now, we can go back to our application and look at the dates table. You'll see a record for each date inside this particular date range, each one connected back to the parent record.

It's just that easy. It's four modules, the logic is pretty straightforward, with some additional calculation to convert things from seconds to days. Depending on your time zone preferences, you might need to add some date-time conversion. But that's essentially it—pretty straightforward.

I've had this asked a few times based on different use cases, so I'm excited to see what other use cases people have for it. It can certainly help out with various applications, such as booking applications.