Re: Major issues when working across different tim...

Jonathan_Beaton · 05-20-2023 05:44 PM

Hey all!

Been having a great time working with Appsheet, and testing out Appsheet database.

I just wanted to make a mention of some issues I have been experiencing most recently as I have been traveling across a number of timezones while working on my app.

I have been using Google Apps Script along with Google Calendar API to write Calendar Event information to some rows in one of my Appsheet Database Tables. Things have been going well, that is, seemingly until I work in a different timezone. For example, 9/10 tables of my Appsheet Database were created in New York timezone.

I recently created another table in Rome timezone and it seemed to be working, but I couldn't create references between it and other tables in the database. Even though I had switched the timezone in the table settings, it still wouldn't permit any Table-To-Table permissions to be created.

I had another issue with the API calls not working in the Rome timezone that I just couldn't figure out.

Once I returned to the New York time zone I was able to recreate the table, and in addition I was also able to get the API working. All I did was start from scratch again in the New York timezone. Both with the Apps Scripts and the Appsheet database Table, separately.

Then, more recently, I moved to Chicago timezone and once again my API calls were behaving strangely. I wasn't sure what I was doing wrong. I tried generating a new table to see if I could send data there, and it's not working at all. I am still in this timezone and I am unable fix the issue or get the API calls working.

I know this could be a whole slew of issues totally unrelated to timezones, and I am also keenly aware that Appsheet Database is in Preview Mode, which seems to be everyone's first comment when I mention this, but I thought I would share this with the community anyway for your information! For me it's not causing issues since I am just experimenting with the app anyway- but I wonder if anyone else has had similar bugginess when developing on the move!

WillowMobileSys

Most likely what you are experiencing is a mis-match in locales across the devices involved. Make sure that sheet, the device you are editing the app from and any other devices you are testing from - all have the same locale set. Also, note that Locale can be set in each individual table inside of the app.

Jonathan_Beaton

I understand. The first time I began to identify these issues, I began checking the locale and timezone of all my app settings, apps scripts settings and the timezone/locale of each individual table within the database and ensuring that they were set correctly. However, when duplicating an individual table or creating a new table within the Database (when this original Table has a New York timezone for example) when you are in a different timezone, Appsheet will automatically generate that table in the timezone you are working in- so the first time that copy is created, it will be created in "the wrong" timezone, and even when you change it to "the right" timezone, it will fail to connect with other tables.

Can you elaborate on setting the locale of my device? I only have one laptop I use for all my editing/work on the Appsheet apps/database- I have never changed any of it's settings. Do you mean that I need to constantly adjust the apparent timezone of my computer in order to work on Appsheet from different timezones? Or do you mean that if I have worked on different computers in different regions with different locales (I haven't) that this is what could cause the issue?

Best Regards,
Jonathan

WillowMobileSys

@Jonathan_Beaton wrote:

Can you elaborate on setting the locale of my device? I only have one laptop I use for all my editing/work on the Appsheet apps/database- I have never changed any of it's settings. Do you mean that I need to constantly adjust the apparent timezone of my computer in order to work on Appsheet from different timezones?

No, I'm not saying you need to constantly change timezones, but depending on what you are doing in the app, your laptop (or just the Browser session) does need to represent the timezone you want reflected. If you want to develop the app as if you are in Eastern timezone, then set the locale to Eastern while working in the app.

The Editor is just a web app that operates within a Browser page, I'm not certain if it directly uses the locale setting from the laptop itself. I am thinking it does not. So you may be able to create a dedicated Browser session for ONLY AppSheet work and then change the locale in that session to the one you desire.

I recommend posting about each issue you are experiencing separately. We can then help determine if its a "circumstances" issues or an actual bug in each case.

@Jonathan_Beaton wrote:

<<table>> will be created in "the wrong" timezone, and even when you change it to "the right" timezone, it will fail to connect with other tables.

What do you mean by "it will fail to connect with other tables" after you have changed the Locale setting? It fails in what way?

For the API issue you didn't specify the problem. I can say the Scheduled Bots do also have a Locale setting so maybe that contributed to the issue you were seeing?

Data concerns - You also need to be aware of how the DateTime values are written into the database. They will reflect the Date and Time of the locale at the time they are written BUT Timezone is not preserved. So if two people entered rows, one in New York and the other in Rome, at exactly the same time, they actually would reflect a 6 hour difference in the data - even though they were added to the app at the same moment.

These data differences could affect calculations and automation triggers if they are DateTime based.

AppSheet does offer UTC DateTime functions. With these you can add a DateTime that will be represented in a common timezone to accurately compare/collect rows across multiple timezones. When displaying these UTC values in the app, there are ways to translate them into the timezone they are being viewed in so they reflect DateTimes of the current locale.

I hope this helps somewhat!!

Jonathan_Beaton

Thanks so much for your help! Really appreciate you taking the time to understand the problem.

@WillowMobileSys wrote:

No, I'm not saying you need to constantly change timezones, but depending on what you are doing in the app, your laptop (or just the Browser session) does need to represent the timezone you want reflected. If you want to develop the app as if you are in Eastern timezone, then set the locale to Eastern while working in the app,.

Okay I think I understand what you mean. I didn't realize this was necessary but it makes sense, thanks for the explanation!

@WillowMobileSys wrote:
I recommend posting about each issue you are experiencing separately. We can then help determine if its a "circumstances" issues or an actual bug in each case.

To be honest, the truth is that I wasn't and am still not 100% confident on what the issue or issues were in the first place. While they all seem to be related to me changing timezones, it was hard for me to figure out what was going on, since it seemed like everything was working incorrectly all at once, all of a sudden. But in the future I will make sure to try to dissect a bit further.

@WillowMobileSys wrote:
What do you mean by "it will fail to connect with other tables" after you have changed the Locale setting? It fails in what way?

I have two tables, "Events" and "Subtasks". Subtasks reference, and "are a Part of" the "Events" table.
When I crossed over timezones, this relationship was broken. Adding a subtask to an event detail page would result in an error along the lines of "Invalid Request" and it would cause the Table-To-Table permissions to be removed between the two tables. The only solution for me was to rebuilt both tables in the new timezone, and then of course, once I returned home they broke again, so I had to rebuild them again (I was only rebuilding since I didn't know what the issue was at first, but it was clear that neither would link up ever again. As far as I can tell, the only change was that I may have edited one of the tables in a different timezone, otherwise I have never seen such an issue before with Appsheet Database. This coincided with all my other "Switching Timezone" problems- making it really difficult to distinguish one issue from the other. Not sure if that makes sense.

@WillowMobileSys wrote:
For the API issue you didn't specify the problem. I can say the Scheduled Bots do also have a Locale setting so maybe that contributed to the issue you were seeing?

It's a bit tricky to explain, but I was using the "Find" action to retrieve some data from a column in a row, and via numerous console.log functions, I was able to determine that this "Find" action was failing when I changed timezones. I still don't fully understand the issue, when I tried to dive into it with Appsheet support they basically told me it's not their problem since I was writing the code in Apps Scripts lol I understand that I guess but I eventually figured out a workaround. Unfortunately I cannot even tell you what the exact problem was or how I fixed it, I just used a FILTER() expression instead of a SELECT() expression in the "Find" API call and that ended up working.

Apologies if my explanations are not helpful, I'm doing my best but I never took a class for any of this, so it's possible I'm just making everyone's lives more difficult.

That said, I love what I am doing with Appsheet, I spend a lot of my time working with these tools and just want to help in any way I can.

Thanks again very much for your help, it's super appreciated!

WillowMobileSys

@Jonathan_Beaton wrote:

Adding a subtask to an event detail page would result in an error along the lines of "Invalid Request" and it would cause the Table-To-Table permissions to be removed between the two tables.

Since it appears to occur when in a different TimeZone, this error implies maybe you are using DateTime as part of the Key? If so, I would strongly encourage NOT doing so. In fact, I recommend to ALWAYS use a "dataless" key column - a dedicated column assigned as a random value (e.g. using UNIQUEID() function) that servers as nothing other that the table key.

@Jonathan_Beaton wrote:

Apologies if my explanations are not helpful, I'm doing my best but I never took a class for any of this, so it's possible I'm just making everyone's lives more difficult.

No apology needed. You're learning. I like to help get to the bottom of issues, at least try, it is many times beneficial to everyone.

I will say trying to analyze problems through text based descriptions and responses is a bit like trying to pull a tooth with a toothpick - it really only works if the tooth was about ready to come out anyway. Likewise solving a problem through text only works if the answer was right there just that the poster didn't understand it.

It is much better to capture and show the errors you get as well show images of the app implemented around the issue - tables or columns etc. To best help the community needs to review your app setup.

Jonathan_Beaton

Hey!

Thanks so much for the info!

I can confirm that I am not using DateTime as part of the key. The Event table used UniqueID initial value for the key column, as did the Subtasks.

And I definitely hear you on analyzing problems by text, just describing them by text is hard enough lol!

Thanks again, hope you have a great weekend

Major issues when working across different timezones.