Prevent bookings 6 months or more away (in the editor, not the sheet)

I’m trying to build an IFTHEN in the Glide Editor that would perform the same action as this formula:

ARRAYFORMULA(IF(C2:C="","",IF(C2:C>(today()+180),“Too far away”,"")))

The goal is to prevent a booking that is 6 months (180ish days) into the future. It currently works with the ARRAYFORMULA, but there is a delay because the sheet has to perform the calculation.

There aren’t any real date calcs in the editor and I can only use Today or Now as options when building conditions based on dates. Does anyone have an idea for a work-around?

Is it a pro app with background refresh turned on? You could probably do the now + 180 calc in the sheet (maybe in user profiles or it’s own sheet). Then use it from user profiles or pull it in with a single value column and compare if the entered date > calculated date. The calculated date should only need to update once a day.

This one isn’t a Pro app, but the date field is a date component that someone has to select each time they want to book a new meeting, so the calc happens whenever someone picks a new date to add a booking (so the sheet refresh is forced when the selection is made). This all happens on the User Profile sheet. The screen is filtered by signed-in user, so everyone’s choices are saved when they come back into the app.

Even if it’s not a pro app, data edits in the app should pull the refreshed calculated date. It may be a day or two behind depending on how often data is added/updated through the app, but that should get you pretty close if nobody is pushing that 180 day limit on a regular basis.

For sure it will. The issue is that is someone picks a date that is 180+ days away, the active button to book the meeting is still clickable for 5ish seconds while the sheet refresh is performed to pull in the calculated field — once the calculated field (the one that says “Too far away”) is pulled into Glide, that button is hidden to prevent the booking from happening. This delay presents the user with an opportunity to successfully book the meeting that is 6+ months away. If this calc could somehow happen in the Editor, the delay would not be present and the booking system would be more reliable.

Sheet refresh shouldn’t come into play because you already have the today+180 part calculated and accessible in the app for the compare. The rest of your if statement is in the app. Not the sheet.

Ahhh I see what you mean! Sorry, wasn’t getting it at first, but yes your suggestion would help for sure. It just relies on a refresh to pull in the current date, though like you said, it’s so far in advance that the refresh that happens with regular submissions will refresh it anyway and there shouldn’t be a need to be that precise.

Appreciate your help and clarification. I’m going to give this a go!

1 Like

Genius! Works like a charm. Appreciate your help on this!

I may build in my manual refresh method into it to help prevent any issues. It’s a way to add a forced refresh for Today calcs for non-Pro apps. It involves using a Single Value column of a Today calc (in the sheet) to check if that column is “today” in the Editor. If it’s not, it shows a refresh button that is tied to an increment action (basically just to force a value to the sheet so it gets refreshed). If that helps anyone, there you go!