Skip to main content

Repeating Groups for function generated array

R
Forum|alt.badge.img+5

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?  


3 replies

pressGO_design
Forum|alt.badge.img+19

Instead of using arrays, try creating one field per rental day. I've done this before, but not on the free plan because of the low automation run limit. You're going to blow through your 100 automations in no time at all, and you're going to be be bumping up against the 1200 record limit in a base pretty quickly.....

Having said that, here's an option for you.

Patrons use this form to request their reservation.
Calculate the number of days the rental (1-5)
Use formula fields to create dates for Days 1-5.

  • Day 1 = {DateTime Start}
  • Day 2 = IF(Days>1, DATEADD({Day1}, 1, 'day'))
  • Day 3 = IF(Days>2, DATEADD({Day2}, 1, 'day'))
  • Day 4 = IF(Days>3, DATEADD({Day3}, 1, 'day'))
  • Day 5 = IF(Days>4, DATEADD({Day4}, 1, 'day'))

Create an automation that is triggered by a form submit and uses conditional groups to create x records for x number of days (for a 1-day rental, it creates 1 record; for a 5-day rental, it creates 5 records).

Link to shared base here.

J
1 person likes this
  • J
    jouejy
kuovonne
Forum|alt.badge.img+27
  • kuovonne
  • Brainy
  • 6002 replies
  • April 25, 2023

Sticking with the free plan is going to be difficult. You have only 100 automation runs per month and you will eat through those very quickly when creating multiple records if you cannot create an array. You could use a table of dates, but that is going to eat into you 1200 max record count.

Why don't you use scripting extension? You can have unlimited scripting extension instances in all plans. 

TheTimeSavingCo
Forum|alt.badge.img+28

Hm, you can just paste your formula's output into a linked field and that might work fine I think?  It would create one record per comma separated value like you need it to as long as a record with the same primary field value doesn't exist in the linked table

To ensure that, you could create an autonumber field in your reservation table and modify your formula to add the autonumber in front of the date, so that it outputs something like

- 1 - 4/1/23, 1 - 4/2/23
- 2 - 4/5/23

etc


Adam, Consultant @ thetimesaving.company

Reply


Most helpful members this week

Terms & Conditions