Recurring Booking System

AppSheet AppSheet Q&A
1

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.

2

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