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!
2 Likes
I also have a use case that requires limiting events to be booked within a certain time. The date concepts discussed here make sense, but I am not sure how you are using it to prevent an invalid booking from happening. How could hiding a button prevent a form submit?
The hidden button is the only link to complete the form. The fields to gather the date of the booking are captured before the form is opened (then those fields are brought into the form — if the form button is displayed — as column values in the form).
1 Like
Ah, so a two screen process. I didn’t realize the data would carry forward like that.
I implemented this but after the user clicks submit on the second screen, it goes back to the first screen where they were originally asked to enter a date. This is a bummer for my design. In fact, maybe any design because it could give the impression that it didn’t work the first time.
Yup that’s one issue that’s been around for a while — lack of a confirmation screen.
For an app that I just did, I only need one submission per day, so I have a bunch of background Glide formulas and lookups to determine if the user has submitted a form today. If they have, then the tab that had the “pre-form” fields and the form button is hidden, then a new tab is displayed that acts as the confirmation screen. This wouldn’t work for a process that needed to be submitted more than once a day though. I suppose it could be adapted to show/hide the tab after a minute or so, but that might depend on background refresh processes, which may take longer than a minute to process anyway.
1 Like