Background: I'm currently building a surfboard reservation system and have been running into roadblocks trying to stick to the free plan because I can't utilize the advanced calendar features. I have two tables currently, 'Surfboards' and 'Reservations'. The reservations table uses a start and end date that can not be viewed on a singular calendar view.
I've thought of a workaround that would require a third table, 'Schedule', and would have a single date field. I want to use automations to create a new event on the schedule for each day of the reservation.
Ask: Is there a way to automate the creation of multiple records, one for each day between a date range (start date, end date).
Details:
Patrons use a form to submit their reservation requests. They indicate a start date (w/ time) and an end date (w/ time). There is a maximum number of dates you can reserve a surfboard for (5). I am using the following formula to generate an array of dates for each date between the start and end date:
ARRAYCOMPACT(REGEX_REPLACE(CONCATENATE("[" &
DATETIME_FORMAT(
{Start Date},
"M/D/YY"
) & ", ",
IF(
DATETIME_DIFF({End Date}, {Start Date}, 'days') >= 1,
DATETIME_FORMAT(
DATEADD({Start Date}, 1, 'day'),
"M/D/YY"
) & ", "
),
IF(
DATETIME_DIFF({End Date}, {Start Date}, 'days') >= 2,
DATETIME_FORMAT(
DATEADD({Start Date}, 2, 'day'),
"M/D/YY"
) & ", "
),
IF(
DATETIME_DIFF({End Date}, {Start Date}, 'days') >= 3,
DATETIME_FORMAT(
DATEADD({Start Date}, 3, 'day'),
"M/D/YY"
) & ", "
),
IF(
DATETIME_DIFF({End Date}, {Start Date}, 'days') >= 4,
DATETIME_FORMAT(
DATEADD({Start Date}, 4, 'day'),
"M/D/YY"
) & ", "
),
IF(
DATETIME_DIFF({End Date}, {Start Date}, 'days') >= 5,
DATETIME_FORMAT(
DATEADD({Start Date}, 5, 'day'),
"M/D/YY"
) & ", "
)
), ", $", "" & "]"))
This is successfully creating an array of the dates, including the start and end dates.
However, I am trying to use this array in a repeating group iteration and it is not accepting the list. After reading the AT documentation, it seems that it will only accept the following data sources for a list: Supported input list sources
Supported source data inputs for use in repeating groups include:
- Find records action
- User fields that contain multiple users
- Linked record fields that contain multiple linked records
- Script outputs that are in an array format
I'd use a script, but I need this to run automatically. The only idea I currently have is creating a 'calendar' table and just populating it with every possible date over the next two years.
Does anyone have ideas or solutions to create a new record in a new table for each day in between the date range?
