App not updating database

I have an app running in free mode. It seems that the local database does not update properly and I end up displaying invalid times, other data seems okay as far as I can tell. This most certainly happens more frequently on Android devices. One thing that seems to alleviate (but not cure) the problem is to install the app by adding it to the Home screen. When I use the app I generally terminate the app after use, I use an iPhone, I have seen this issue very rarely. however, I have several users on Android see the issue.

This is a screenshot of Android acting up

This is screen shot of what it should look like

(from a safari browser)

The time strings generated from this

Where Start Hour and End Hour are generated from a JavaScript that returns a string.

Anybody else see this? Have some ideas?

There’s clearly an issue with the javascript code t how it’s interpreting the input parameters. Can you share the code and any input parameters that it’s using?

The calculation for End Hour

The Ordinal End Time is

This is a portion of database table

This is the Ordinal start time

This is another potion of the the same database table (shows input to ordinal start time)

Basically I calculate milliseconds since Jan 1, 1970 from the Sort Date and Time
to get Ordinal Start Time. Then add a bunch of seconds to get an Ordinal End Time, The Javascript then calculates the UTC string corresponding to the Ordinal End Time and it grabs the ending hour from that. This gives me a way to show a start hour and end hour of a period in a day.

The data in the database is always correct, so I am not sure how it can be the Java Script. I have one user that looks at her husbands iPhone and it displays properly but her android does not at virtually the same time. These dates and times change one a week.

I think it’s the Date Difference column for Ordinal Start Time that’s giving trouble. I generaly don’t trust it because results can vary from device to device. I won’t use anything for date math other than a math column or javascript.

Unix time is pretty easy to calculate in javascript.

It’s well past my bedtime, so I can take a look in the morning unless someone else gets to it first.

I would also reconsider even using unix time and going through all the work you are doing. It’s pretty easy to add days, hours, minutes, or second to a date column value and still have a valid date that can be formatted to show time only. But first you would have to explain why your Sort Date and Time column is text based, and if that is sourced from a date column somewhere up the line. I think the whole thing could be simplified with nothing more than a core date type column and a couple of math columns.

1 Like


So what you are telling me is each device is doing these calculations independently?

If true, I did not realize that.

Yes, that’s correct.
Other than a handful of exceptions with Glide Big Tables, all computed columns are evaluated independently on each users device.

If you can respond to Jeff’s question, we can almost certainly help you simplify the calculations and make them more robust.

1 Like

Some more information so the application can be understood a bit more. The application is an equipment booking application for my canoe club. We have sets of boats type (eg 22 canoes, 10 kayaks) that are loaned out. We assign a bunch of usage start times and length of usage (a period) at those start times throughout the day. Users then book boat usage in those periods. Many periods overlap so the app keeps track of the boat types in use, the boat types that are needed and tracks the count boats available in each period. The app disallows booking of a boat type once we run out in a period and allows booking of a boat type once the boat type becomes available (because they came back in). We do this on a weekly basis so we have a fixed schedule for the week and after a day ends we change the date of the day to the next week.

The use of the ordinal start and end times was an easy way to use a query to figure out the counts of boat types in the overlapping periods. And I thought would be efficient because it is doing the comparison on an integer, maybe that was naive and i could have just done it right on the time because they are probably stored as a number. I also use the start ordinal time to do sorts in the user interface.

To set the date I use a date picker that has no hours or minutes and put this in a table that keeps the date for a particular day (Monday, Tuesday…). I then use a relation


that matches on day. Followed by a lookup


To get the date from the matched record in the relation.

Which gets me to


To enter the Start time I use text entry field because there is no Time only picker and put it in this column


The reason I made it text based is because I had to add the start time of the period onto the date of the of the day (for each period). I can not remember if I just tried adding the day’s date and the start time together. I also had some issues with dates (which I can not remember) so adding the GMT solved them.


This post has reference to two other posts that I think will be very useful. Start there and let me know if you have questions.

Once you have a normal start date column created from the math column, then you can add the appropriate value in another math column to get the end date. Date math in glide is based on days. To add a day, to a date you simply add one. If you want to add hours or minutes, then you need add the corresponding decimal value. Each hour is ~0.0416666 (or 1/24). Each minute is ~0.00069444 (or 1/1440).

In the end you will have a start and end date that are true dates, so it will be easy to sort, filter, and query based on those dates instead of having to convert to integers.

1 Like

Thanks Jeff.

I will try a few things based on now knowing how to add time to a date. Hopefully, the date chooser will report midnight for the time portion of the day.

This is a lot of work to change the app so one question. How confident are you that if I make the changes I will get rid of the issue above? Not holding you to it but just a gut level feel.

Right now restarting the app seems to clear the issue.

Here is my first issue

I added 1 minute to the Date in the XXXX column

Not sure why Thursday (May 9) is using PM but all the others use AM. Obviously, the inconsistency is not good.

It might be because in the user interface some have used the date picker to have the date entered, and some have used an increment of the column. Guess I am going to have to write a small app to test this.

I’m fairly confident. Glide natively handles dates and date math pretty well. Where there has been problems is where people start to rely on those “experimental” date columns. There have been several past issues regarding people in different regions of the world or people using iPhones for example where those columns just break. Different devices may interpret date formats differently. You are expecting a particular format, which works fine on your computer, but certain phones or other devices may format the date a bit differently, so your Text to Date column can no longer figure out how to convert that text based date into an actual date. Many of us just stay away from some of those columns because of these issues. The alternative may be a little harder to set up, but the results are much more reliable.

I will admit that I have seen peculiarities like this and I really don’t understand why it what the cause is. Generally I feel that the date picker should be pretty safe if you are using that. There are some workarounds if you continue to experience this.

Can you expand more on this?

In the user interface one option on the collection allows them to set the date using the date picker. The other option allows the to just add 7 days. Here is the collection

The Set Date and Force Date use the Date picker on this edit screen (I only allow a Thursday to be set for a Thursday by enabling the save button only when the chosen day is Thrusday)


And the increment 7 days does this as an action


Now it could be that the at one time I entered the date by hand in the data field and this put in the PM and then we have only used add seven days since. I will fix my running app and test for a while as they roll dates.

I could do some extracting in another column where I check that the hours and minutes are 0:00 and if not reset (or warn the user) it somehow. Messy.

1 Like

Interesting. Yeah let me know if you figure out what happened. In the meantime, you could adjust your math column to take the date and subtract the hours and minutes from that date followed by adding the hours and minutes from your Time column. If there is an issue with noon vs midnight, this should correct it.

1 Like

I implemented the changes using just standard math to add hours and such to date and times. The calculations seem to work properly and with less effort. Here is the data in the builder

When I sort on Start Date and Time in a collection on my laptop it looks like this


When I run on my iPhone I get this

which is reverse order. In all my date fields (where I can) I specify Respect time zones as Off

If I switch the order to reverse order then the laptop changes but the iPhone does not.

If you haven’t done it, you should try to open your builder on Safari and navigate to the relevant columns to see where the difference is.

do you mean on my iPhone open the builder?

My builder is running on a mac laptop. Also just ran it in a safari window - works.
It works on chrome.

So both Safari and Chrome on Mac laptop works.
iPhone does not.

Must have just needed some time. Working now - sorry


1 Like

Good news is I implemented this on about half of the dates and times. One user (android) that could not clear the problem now has the app showing everything properly in the implemented fixes.

After I fix the other area I will post back here on the results. It will probably be next week sometime. This change is a little more involved as it is the one that takes into account the overlapping times.

Thank you for the excellent help again. :grinning: