Math formula with Precision

My app’s URL: https://omfnp.glideapp.io/

I am trying to recreate @Robert_Petitto’s Meet with Me app, with buffer and meeting duration pre-selected.
As part of the slots availability check, I am subtracting 2 dates expressed as numbers (Robert - here’s a hint for you to add/subtract dates in Glide :slight_smile:).

Imagine: I am trying to book a 30 min slot for 03:30 when 04:00 is already booked.
In the 4th row in the screenshot below, the result is 0 (which is correct).
It is the result of: new booking slot end time (04:00) - existing booking’s start time (04:00).

In the dependent IF condition, I am checking for this difference to be >0.
The result is 0.0000 but the IF condition evaluates to 1.

I suspect that there is one rogue field which is not constrained with precision to 4 digits.

This may not be a bug after all, but an extreme corner case of a math result greater than 0.0000.

1 Like

I noticed this with other scenarios as well. You can add a go-between template column that converts the math column to a string and then evaluate 0s and 1s that way. If this works…you’ll need to show me!

Haha, Robert. I have spent the last 24 hours trying to figure this out (that one bug that doesn’t let you sleep).
I designed a simpler app with no preselected meeting duration (e.g. as a result of which you could book a 60 minutes meeting at 03:30, even if 04:00 is booked). I struggled with something similar there too, until I figured that Precision is the culprit. That app works perfectly, with configuration for buffer between meetings too.

I was planning on sharing this app with you once this app was bug-free.
But this ‘bug’ is stopping me from making the announcement. :-/

Also, I have made it work with numerical values of date/time. So converting it to string again will be ironic. :slight_smile:

Ha. I get it. Ya. I wish Glide would just let you compare dates…but I gues comparing date values could work. Exploring this now.

Sorta got this working. The trick was actually to use two relations (one for start times not matching) and the other for starttime + durations not matching. I used values and I see what you were talking about about the precision. So what I did was use a text formula to crop off the values past “00000.0000” for both the selected datetime as well as the unavailable datetimes:

Here’s the result. Only SOME slight lag when first selecting/matching date times w/ duration. …but NO lag when selecting other durations:

1 Like

Robert, I don’t know if you have seen Jeffs booking form on his concepts app. I noticed that it prevents time conflict by service provider. Only problem was I could not get, as he could for whatever strange reason, the sheet to record the entries even though it displays then on the calendar display on the app. Because of this I am approaching things in a differing manner inside the Glide editor and got rid of all Jeffs formulae. But it’s works for me because mine is based on a weekly/monthly basis beyond which I am asking customers to book using the booking form based on subject to availability. I’ll share the final product once I’ve finished.

Yours of course is a proper booking app. Mine is just a workaround that avoids use of google formulae altogether.

1 Like

And that’s really the point. You got to work around the Google formulas and use just glide’s data columns. Would love to see what you come up with!

1 Like

You should’ve been able to book a 45 min meeting at 10:15. This is one of the edge cases.

Also, the lag was the reason why I wanted to do everything in Glide’s Builder. Except for the ‘desired date’ nothing is being communicated back to the app.

Right. I tried building in a 15 minute buffer. That’s why it was unavailable.

1 Like

I agree that the lag is frustrating. The only source of the lag right now is that I’m converting a date time concatenation to a date value.

Robert - to give you an idea of what the implementation is like, this is wild web of a number of calculations. :sweat_smile: :sweat_smile:

Others testing the app: the app is broken now. I tried changing the format for the desired date in my app and it is not getting carried over int he right format. Will fix it when I feel fresh.

1 Like

Oh my goodness…I’m SO close. All I need to do is somehow get my date picker to write the datevalue (as a number) in Glide…thought maybe writing the date picker to a num column would work but it writes 0.0000 instead. Tried formatting my spreadsheet to be a number in 00000.0000 format and it works until I pick a new date…overwrites the format.

Only thing I can think of is use choice components to write the month value and day value separately and “math” them together to create the datevalue. Wish the date/time column had a “value” display.

Anyone have any ideas? @Jeff_Hager @George_B @Mark?

Ok…I went a bit crazy. I finally got it to instantly calculate by creating my own datevalue calculation using a several if/then columns.

Used two sets of unavailable time columns and two relations to check for conflicts:

  1. numeric start datetime value (my value) matching the array of other start datetime values + durations (missing the last edge value)

  2. numeric start datetime+duration value (my value + duration -> template column) matching the array other other start datetime values durations (missing the start edge value)

Whew! So…it works!!!

Would be a heckuva lot easier if we could just have datevalue to start with…but it was fun figuring it out.

The pitfalls is that it will only work for English/American Time format…currently only works for 2020 and 2021…unless I put in a few minutes work to add the next decade or so.

1 Like

It should be sufficient to format the whole date/time column in Google Sheets as a number. Then next time you let Glide write a date/time to that column, it should honor that format.

It doesn’t. When I write to that column, Google sheets switches the Date value back to timestamp which Glide then interprets as date/time column which means I can’t do calculations.

Nevermind. Faulty logic lol.

@Mark Evidence :frowning:

1 Like

I almost made it work with rounding up the numbers in the Google Sheets. But it again broke at some other places for other times.

I am going to take a break from this! :wink:

Robert - I can send you a link to copy the app in case you’re interested.

Hey…would love to see it!