Recurring Booking System

[KaysonYang]

I am trying to create a recurring booking system. The added booking needed to be repeated based on the days selected. Eg: Monday & Thursday is selected then every week Monday & Thursday will be booked for the same client with all the same details as the first booking added. I don't want the bookings to be generated years in advance but a couple of weeks and then if the system detect that the booking is still active then it will generate more later. I also want to be able to adjust some info as needed and update the future bookings accordingly. Basically I want something like Calendar event where events are auto generated and can be edited at any point “just one event or this & future events” type, while leaving the past bookings as it is without any change. 

[Aurélien]

Hello, It's often best to use at least two tables for this:

RecurringPatterns Table (or MasterBookings): This table will define the recurrence rule.
* Assumed Columns:
* [PatternID] (Type: Text, Key) - Unique ID for the pattern.
* [ClientName] (Type: Text, or Ref to a Clients table)
* [ServiceDetails] (Type: Text, or Ref to a Services table)
* [DefaultStartTime] (Type: Time)
* [DefaultEndTime] (Type: Time)
* [DaysOfWeek] (Type: EnumList, Base Type: Text, with allowed values: "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday")
* [RecurrenceStartDate] (Type: Date) - When the recurrence begins.
* [RecurrenceEndDate] (Type: Date, optional) - When the recurrence stops.
* [IsActive] (Type: Yes/No) - To control if new bookings should still be generated.
* Other details common to all recurring instances.

IndividualBookings Table: This table will store each actual booking instance.
* Assumed Columns:
* [BookingID] (Type: Text, Key) - Unique ID for each specific booking.
* [LinkedPatternID] (Type: Ref, referencing RecurringPatterns) - Links this booking to its recurrence rule.
* [BookingDate] (Type: Date) - The specific date of this booking.
* [StartTime] (Type: Time) - Can be copied from the pattern or adjusted for this instance.
* [EndTime] (Type: Time) - Can be copied from the pattern or adjusted for this instance.
* [ClientName] (Type: Text or Ref) - Copied from pattern, can be useful for display.
* [Status] (Type: Text, e.g., "Scheduled", "Completed", "Cancelled")
* Any other details specific to this one instance.

1. Generating Initial Recurring Bookings (e.g., for the next few weeks)

When a new row is added to RecurringPatterns (or an existing one is set to [IsActive] = TRUE), you'll want to generate the first set of IndividualBookings.

* Logic:

• Determine the start date ([RecurrenceStartDate]).
• Determine the days of the week selected ([DaysOfWeek]).
• Loop for a defined period (e.g., 4 weeks from the start date).
• For each day in this period, if its weekday matches one of the selected [DaysOfWeek], create a new row in IndividualBookings.

* AppSheet Implementation:
* This typically involves an Action (or a set of actions) on the RecurringPatterns table, perhaps triggered on "Form Save".
* AppSheet doesn't have direct loops in actions. Common strategies: Helper Action to Add One Booking: An action that calculates the next valid booking date and adds it. This action might then be called multiple times or trigger itself under certain conditions (this can get complex).

* Calculating a List of Dates: More advanced, but you could use a virtual column to calculate a list of the first N booking dates, and then an action could iterate through this list to add rows.
* Key Formula Concepts for Calculating Dates:
* TODAY(): Gets the current date.
* WEEKDAY([Date]): Returns a number for the day of the week (Sunday=1, Monday=2, ..., Saturday=7). To find the date of the next occurrence of a specific weekday (e.g., next Monday) on or after* a [GivenDate]:

// Assuming TargetWeekdayNumber is 2 for Monday
// [GivenDate] + MOD((TargetWeekdayNumber - WEEKDAY([GivenDate]) + 7), 7)

I hope it's help