Filtering relationship and dates

I’m working in a Flex Office booking app for my company and I’m struggling with 2 things:

  1. I would like to prevent booking too far in the future, like being able to book only for today or tomorrow. For today, I can make a form and use the “now” feature for the date. But I’m stuck for tomorrow’s option. Any idea?

  2. When presenting the seat availability, I would like to be able to see the seats without booking for the selected date (today for ex.) … but I don’t find a way to filter the elements.

If you guys & gals have any idea, I’m open to it :smiley:

For the first question, you can use an arrayformula to dynamically update the range of date choices users can make.

The formula would look something like this:

=ArrayFormula(TEXT(ADD(TODAY(),row(INDIRECT("A1:A"&2))),"ddd dd mmm yyyy"))

For your second question, what do you expect the screen to look like?


Nicely spotted. But that would force me to review the structure of my booking table since I don’t repopulate anything.

My booking table so far is pretty simple:

Date User Email Seat
2020-07-07 ASN-1-SALES-1
2020-07-08 ASN-1-SALES-1

With your solution, I would need to create all the seats available for all the dates possible isn’t it? But maybe I didn’t get how to use the formula (plus, I though formula can’t be automatically replicated on new row insertion isn’t it?

I wanted first to do something like a simple form where you can only seat which are not taken by someone for the selected date (with the previously mentioned schema). However, I ended up doing this in another way :

  1. User select a Agency > Floor > Area > Seat
  2. User see the availibility of the seat (thanks to relation + rollup to get the latest date, then a IF ELSE to compare if the date is before TODAY)
  3. Buttons for booking forms are conditionally displayed (if the user hasn’t already booked, and if the seat is available)
  4. I have two different form, the one for “today” use the magic today date component, therefore and that’s my issue, the tomorrow booking can’t have date automated so far.

Can you show some screenshots of how you’re structuring the data in the Sheets and your app? Thank you.


Employees (I removed the non essential data like picture, email, name…)

Seats are a little bit trickiers

1 Like

So you have fixed seat IDs that would be available only if it has not been booked, and for any new day it’s automatically tagged as available right?


What I would advise in this case if you don’t have to alter these things too much, is to have two single value column in your Seats sheet.

In another sheet, let’s say the “Admin” one. You have one column stored as “Today” and the =TODAY() formula to take the right date for that. One further column is “Tomorrow” and the only value of it is =TODAY()+1.

Back to the Seats sheet, create 2 single value columns named as “Today” and “Tomorrow”, taking the values respectively from those 2 columns you created in the “Admin” sheet. Then we will have two columns storing the right “today” and “tomorrow” for every sheet.

In your form, for the “Today” form, capture the “Today” value via the Columns component and write it to the Date column in your Bookings. Same goes for “Tomorrow”.

Hopefully it makes sense.


I see your point, that’s very smart.
I have then this for the Seat table

And then use a column component image

That’s working perfectly :white_check_mark:

Thank you very much @ThinhDinh :trophy: