Case Overview

This article introduces how to determine whether a selected time period is available for reservation in a multi-day reservation scenario based on the reservation start time and the number of usage days. It also explains how to retrieve the specific dates that are unavailable for reservation.

Application Scenarios

In an equipment management scenario, when a member needs to reserve a device for multiple days at once, this method can be used to check whether the device has already been reserved.
In a meeting room reservation scenario, when a member needs to reserve a meeting room for multiple days at once, this method can be used to check whether the selected meeting room has already been reserved.
In a course management scenario, when scheduling multi-day courses for a teacher, this method can be used to check whether the teacher already has existing course arrangements.

Solution Design

Add the basic reservation information to the form, including the device to reserve, reservation start time, and usage days.
Use the auto-numbering configuration of a SubForm to generate the corresponding number of SubForm rows based on the usage days.
In each corresponding SubForm row, generate a unique reservation value, which is the combined value of the reserved device and the reservation date.
Use an Aggregate Table to record all reservation records, and retrieve data from the Aggregate Table through data linkage based on the unique reservation value of the current meeting room reservation.
If the linked result is empty, it means that no reservation record exists for the selected date and device, and the time period is available for reservation.

Expected Effect

Taking equipment reservation as an example:

1.A member has already reserved Device A in the Equipment Reservation form. The reservation start time is 2023-04-06, and the usage duration is 7 days.

2. When another member tries to reserve Device A again within the same time period, the field will show that the device is unavailable for reservation and display the unavailable dates. The form cannot be submitted.

How to Set It Up?

The following steps use the equipment reservation scenario as an example.

Step 1 Building the Forms

1.Create an Equipment Reservation form and configure the fields as follows:

Field Name	Field Type
Requester	Member
Reserved Device	Single Select
Reservation Start Time	Date&Time
Usage Days	Number
Reservation Availability	Single Line
Unavailable Dates	Single Line
Date Details	SubForm
No.	Subfield - Single Line
Query Date	Subfield - Date&Time
Date + Device	Subfield - Single Line
Number Helper	Subfield - Number
Aggregate Table Query Helper	Subfield - Number
Reserved Date	Subfield - Single Line

2. Create a helper form named Subform Base Table and configure the fields as follows:

Field Name	Field Type
Total Rows	Number
SubForm	SubForm
No.	Subfield - Single Line
Number	Subfield - Number

Step 2 Entering Data in the Subform Base Table

1.Go to Subform Base Table > Manage Data > Import and import the sample data from the attachment: Full Set of 200 Row Numbers for the Subform Base Table.xlsx.

2. When importing the data, confirm that the mapping between the sample data and the form fields is correct.

Step 3 Setting Data Linkage for the SubForm

1.Go to the Equipment Reservation form, select the entire Date Details SubForm, go to Field Properties > Initial Value, choose Data Linkage and configure the settings.

2. On the Data Linkage Settings page, configure the following settings:

Linked Form: Subform Base Table
Triggering conditions: Total Rows equals Usage Days
Triggered linkage:
- The Date Details field displays the value of the SubForm field.
- Add the No. subfield and display the value of the No. subfield.

After completing all settings, click OK.

In this way, the number of rows added to the Date Details SubForm will be the same as the value entered in Usage Days.

Step 4 Setting Formulas for Helper Fields

Now set formulas for the following helper fields:

Date Details.Query Date
Date Details.Date + Device

1.Select the Date Details.Query Date field. Go to Field Properties > Initial Value, set the initial value to Formula, and click Edit below.

2. Set the formula for Date Details.Query Date to:

DATEDELTA(Reservation Start Time,Date Details.No.-1)

This formula automatically generates the reservation dates based on the reservation start time and usage days. For example, the first row displays the reservation start date, the second row displays the reservation start date plus 1 day, and so on.

Remember to click OK to save the settings.

3. In the same way, set the formula for the Date Details.Date + Device field to:

CONCATENATE(TEXT(DATE(Date Details.Query Date),"yyyyMMdd"),Reserved Device)

This formula uses the CONCATENATE() function to join the reservation date and device name as a unique value.

Step 5 Setting the Default Value for the Helper Field

Select the Date Details.Number Helper field. Go to Field Properties > Initial Value, set the initial value to Custom, and enter 1 in the input box below. This field will be used as a metric in the Aggregate Table later.

Step 6 Setting the Aggregate Table

1.Go to App Management > Aggregate Tables, and click + New Aggregate Table.

2. Add the Equipment Reservation form as the data source. Since the detail data in the SubForm needs to be summarized here, select the Date Details SubForm as well.

3. Configure the dimensions and metrics of the Aggregate Table as follows:

Dimension：Date Details.Date + Device
Metric：Name: Date Details.Number Helper. Edit the metric formula:

SUM(Equipment Reservation.Date Details.Number Helper)

Step 7 Setting Data Linkage for the Aggregate Table

1.Select the Date Details.Aggregate Table Query Helper field. Go to Field Properties > Initial Value, choose Data Linkage and configure the settings.

2. On the Data Linkage Settings page, configure the following settings:

Linked Form: Aggregate Table Equipment Reservation
Triggering conditions: Date Details.Date + Device equals Date Details - Date + Device
Triggered linkage: Date Details - Aggregate Table Query Helper displays the value of the Date Details.Number Helper field.

After completing all settings, click OK.

Step 8 Setting Formulas for Validation Fields

Following the method in Step4 Setting Formulas for Helper Fields, set formulas for the following validation fields:

Date Details.Reserved Date: IF(ISEMPTY(Date Details.Aggregate Table Query Helper)==0,TEXT(DATE(Date Details.Query Date),"yyyy-MM-dd"),"") When the Date Details.Aggregate Table Query Helper field is not empty, it means that a corresponding reservation record has been found in the Aggregate Table. In this case, the corresponding reservation date is displayed. Otherwise, the field remains empty.

Reservation Availability:IF(OR(ISEMPTY(Reservation Start Time)==1,ISEMPTY(Usage Days)==1),"",IF(LEN(CONCATENATE(Date Details.Reserved Date))==0,"Available","Unavailable")) When Reservation Start Time or Usage Days is empty, this field remains empty. When the number of characters in Date Details.Reserved Date is 0, the field displays Available. Otherwise, it displays Unavailable.

Unavailable Dates: IF(OR(ISEMPTY(Reservation Start Time)==1,ISEMPTY(Usage Days)==1),"",IF(Reservation Availability=="Unavailable",Date Details.Reserved Date,"None")) When Reservation Start Time or Usage Days is empty, this field remains empty. When Reservation Availability is Unavailable, this field displays the existing reserved dates. Otherwise, it displays None.

Step 9 Setting Form Submission Validation

Set form submission validation in the Equipment Reservation form. When some dates for the selected device have already been reserved, the form cannot be submitted.

1.Go to Form Properties > Form Submission Validation, and click Add Data Validation Condition.