How to Store Dates in Alternative Formats Inside Data Stores
Retrieve information from Airtable and store it inside a data store
Scenario: Retrieves information from Airtable and stores it inside a data store. This scenario watches a table within an Airtable account and is triggered when a new record is added.
Level of difficulty: Intermediate
Implementation Time: Approximately 15 mins
Prerequisites: The following is needed to set up the scenario:
1. An Airtable account with a table containing fields with data
2. A data store and a data structure
In case you’re not familiar with a data store, it is an inbuilt database feature of Integromat that makes it easy to store and retrieve critical data.
Creating a data structure that is used within a data store is pretty easy, but you may find that the default date format may not suit your needs. The good news is that there is a solution for this. Let us show you how to store dates in a particular format inside a data store.
Here we have a scenario that begins with an Airtable > Watch records module that watches for new records. Each record consists of an activity, email address, the due date and time. When the scenario is executed, the new data from the Airtable module will be sent through and stored on a data store via the Add/replace a record module.
Data Structure Configuration
First, let’s take a look at the data structure. The Due Date and the Remind Date & Time are both fields of type, date. In a data store, a date field uses the ISO 8601 representation. By default, data in a date field is displayed in the following format: MMMM D, YYYY h:mm A.
All the tokens for date and time formatting on Integromat can be found here.
If you wish to use a different format, you need to use the text field instead of the date field.
We will begin by using the date field to show how the dates are stored in the data store, thereafter the text field will be selected to format the date:
Building and Executing the Scenario
Next, let’s go back to the scenario builder. Here is a quick look at the Airtable module configuration. The connection is added and all relevant fields are selected accordingly:
Now let’s look at the configuration of the data store module. The data store with the data structure shown earlier is selected and the elements are simply mapped to the corresponding fields in the module:
For the Reminder Date & Time field, the reminder will be set to 1 day before the due date. To set this up correctly, the addDays function needs to be applied. To do this, click on the Date and Time tab and select addDays function:
Next, map the Due Date element followed by the addition operator, which is found under the Math functions tab:
Lastly, map the Time element after the addition operator and enter -1 after the semicolon, which subtracts 1 day from the due date:
The scenario was executed once, and the data was sent through from the Airtable module and entered in the Data Store. The Due Date and Reminder Date & Time in the data store are both date fields, therefore, they are in the default format. The Reminder Date & Time contains the correct time as the time element was mapped to this field in the Data Store module. The Due Date contains the default time since a separate column was created for the time:
Selecting the Text Field and Formatting the Date
Now, let’s assume that you want the date to be stored in a different format. For example, DD-MM-YYYY. As mentioned earlier, the text field will need to be selected to format the date. Let’s change the Due Date to a text field and leave the Reminder Date & Time as a date field so that a comparison can be made after the next scenario execution:
Let’s go back to the Data Store module in the scenario builder. To format the date, the formatDate function will need to be used in the Due Date field. Again, click on the Date and Time tab but this time select the formatDate function:
Next, map the Due Date element followed by the date format after the semicolon:
The scenario was executed again and the data has been entered in the data store. In the Due Date column, the date is in the format DD-MM-YYYY as compared to the Reminder Date & Time column, which contains the original format:
So there you go!
Now you know how to store dates in alternative formats within a data store.
Happy automating! 🙌