Calculated Fields Form Tutorials

Tutorials / Mastering Connected Dates in Forms: From Simple Bookings to Complex Itineraries

Mastering Connected Dates in Forms: From Simple Bookings to Complex Itineraries

Categories: Controls, Form Features

Hello everyone! Today, we're diving into a fundamental yet powerful aspect of form design: working with connected dates. Whether you're booking a hotel, a flight, or a custom travel package, the dynamic between a start and end date is crucial. Let's explore how to build this logic, starting simple and scaling up to some really clever automated calculations.

---

The Foundation: A Basic Booking Form

Let's say we're building a booking form for a tour operator. Users will create their own custom tour, so we need a Start Date and End Date field. We'll call them:

• fieldname1 = Start Date
  
• fieldname2 = End Date

Step 1: Preventing Dates in the Past

First rule: users can't book a trip that starts yesterday. In the Start Date field (fieldname1), we set the 'Min Date' property. Setting it to 0 means "no date earlier than today."

Need a processing buffer? If your company needs 7 days to prepare, simply set the 'Min Date' to 7d. This ensures the start date is at least a week from now.

Want a default? In the 'Default Date' setting, using 7d will pre-populate the field with a date one week from today.

Step 2: Linking the End Date

The golden rule: the End Date can never be earlier than the Start Date. To enforce this, go to the End Date field (fieldname2). In its 'Min Date' setting, instead of a number, simply enter the name of the other field: fieldname1.

Boom! The fields are now connected. This covers the most common scenario.

---

Leveling Up: Calculating Dates Based on User Choices

Now, let's make it interesting. Our users are selecting points of interest for their Paris trip, and each site has a different time cost:

• Eiffel Tower: 0.5 days (transfers & lines included)
• Louvre Museum: 1 full day
• Palace of Versailles: 1 full day
• Montmartre & Sacré-Cœur: 0.5 days
• Musée d'Orsay: 0.5 days

The End Date now depends on two things: the Start Date and the total duration of selected attractions.

Example: Eiffel Tower + Louvre + Versailles = 3 days minimum.

How do we automate this?

Step-by-Step Calculation

1. Create a Checkbox Field (fieldname3) for the attractions. For simplicity I will set the value of each option to its duration (e.g., 1 or 0.5).

2. Insert a Hidden Calculated Field (fieldname4). This is our auxiliary brain. We'll hide it from the form view and exclude it from final data submission—it just works behind the scenes.

3. The Magic Formula: In the calculated field (fieldname4), enter this equation:

   CDATE(fieldname1+CEIL(fieldname3), 'dd/mm/yyyy')

   What does this do?

   • fieldname1: Takes the selected Start Date.
   • CEIL(fieldname3): Sums the values (durations) from the checkbox field and rounds up. So 3.5 days becomes 4 days.
   • CDATE(...): Converts the result into a proper date format.
     
     

4. Final Connection: Go back to your End Date field (fieldname2). In its 'Min Date' setting, now point it to the calculated field: fieldname4.

Just like that, the form dynamically calculates the earliest possible end date based on the user's chosen itinerary.

---

The Possibilities Are Endless

While this example uses tourist attractions, the potential of linking dates through a calculated field is huge. Think about adjusting for:

• Travel style: Fast business trip vs. relaxed honeymoon pacing.
• Transportation: Slow train journeys vs. quick flights.
• Meals & Extras: Fancy multi-course dinners vs. quick fast food stops.

By mastering this connection between fields, you can build incredibly intuitive and intelligent forms that handle complex scheduling logic seamlessly.

The key takeaway? Start with the simple link (fieldname1 → fieldname2), then unleash the power of a hidden calculated field to factor in any variable you need.