Camera Reservation System: Checking Availability and Form Integration

User Talk Formulas
1

Hi everyone :grin:

I’m setting up a camera reservation system in SeaTable. I have two tables:

  1. “Camera’s” table: Contains all available cameras
  2. “Reservations” table: Linked to the “Camera’s” table, contains reservation details

Objective

I want to create a way to check if a camera is available on a specific date. Ideally, I’d like to have a column in the “Camera’s” table that shows “Available” if there are no reservations for that camera on a given date, and “Not Available” otherwise.

Additionally, I want to use a form where users can select a date, and the form will then only show the cameras that are available on that selected date.

Current Setup

  • The “Camera’s” table has a column for the date I want to check (let’s call it “Check Date”)
  • The “Reservations” table has “Start Date” and “End Date” columns for each reservation
  • The two tables are linked

Questions

  1. Is it possible to create a formula in the “Camera’s” table that can check the linked “Reservations” table and determine if a camera is available on the “Check Date”?
  2. If a formula is possible, what approach would you recommend? What functions might be useful for this?
  3. If a formula isn’t the best approach, would a Python script be more suitable? How might I approach this using a script?
  4. How can I integrate this availability check with a form, so that when a user selects a date in the form, only the available cameras for that date are shown as options?
  5. Are there any limitations or best practices I should be aware of when implementing this kind of availability check and form integration in SeaTable?

Additional Considerations

  • The solution needs to handle multiple reservations for each camera
  • It should be dynamic, updating the availability status when the “Check Date” is changed or when new reservations are added
  • The form should dynamically update the list of available cameras based on the user’s date selection

I’ve tried a few approaches using countlinks and date comparisons for the availability check, but I’m running into issues. I’m also unsure how to best integrate this with a form for user selection. Any guidance on the best way to tackle this would be greatly appreciated. Thank you in advance for your help!

2

I would create a list containing the cameras, another with the dates and link those two tables. as soon as the count for a camera and day is two I would use a automation to deny the request.

3

Hi Matthias,
Here are my thoughts about your case:

  • In the “Reservations” table, I created a Check formula column with the following formula: and({Cam.Check Date}>={Start},{Cam.Check Date}<={End})
  • Then, in the “Camera’s” table, I created a Status formula column with the following formula: if(lookup(findmax("Reservations","Check"),"Check"),"Not Available","Available"). It’s working but “not so dynamic” (you have to refresh the page or switch to another table and back to see the updated status when you modify the Check Date).

As far as I know there is currently no built-in feature in SeaTable to manage simply what you need, because Forms can’t update depending on anything. In App forms, you can filter the displayed options for a linked column, but the condition is only static (feel free to upvote my suggestion about that :grin: even if it’s not exactly the same as in a form filling the date might no be already available).

I had once to deal with a similar problem and my solution, briefly exposed here, was to recreate a form by myself (html + JS) interacting with a n8n automation via webhook: the form page sends a request to n8n that sends back either all the reservation ranges of every cameras once or the list of every available camera each time you change the date.
This second solution may be more dynamic, even if the case of someone booking the same camera between your last date change and the actual booking is not directly covered, but you can make another check just before submitting the form. Of course, you have to be able to host your form somewhere…

Bests,
Benjamin

4

Currently there is no good solution for this use case.

There are several people saying about the same use case. At present, we are working on a new page type called “Booking page”. This page type would meet this use case once it is done.

2 Likes