Unable to fetch app definition. Error: The added or subtracted values results in an unrepresentable

[Harriswe]

I've recently started seeing this error appear when the app starts up, both in the editor and in the web-browser for users. There have been no recent updates and this error is affecting the stable version of the app. Additionally, there have been no changes to the database tables.

Strangely enough, the app continues to work on mobile devices.

Has anyone else experienced this error recently?

[WillowMobileSys]

It's possible that you already had something implemented and AppSheet recently added/corrected some validation around DateTime.  Double-check any date processing that app might be performing to make sure it is still good.

If it all checks out, then report this issue to AppSheet support.

[Harriswe]

@WillowMobileSys  Thank you for the suggestion. I have reviewed all the date, time, and datetime fields, removing any calculations for the time being. Despite this, the issue persists. The app was working on mobile devices, but it stopped functioning on mobile last evening, even though no changes were made.

The issue was reported to the Support team 10+ days ago and they are currently investigating. Since then, I have been unable to run the app in the editor at all.

Interestingly, three other apps using the same database continue to work without any issues, which might indicate that the problem is not due to a rogue date-time value.

There is no specific table or column reported in the audit log, so I am awaiting feedback from AppSheet support. Another option is to trace the database activity to identify the culprit. 🤔

[Harriswe]

Subject: Urgent Help Needed: Synchronization Issues Crippling My Application

I've been facing synchronization issues that seem to be catastrophic for my application. The problem even prevents me from switching a user account to run on the stable version.

Updates made in other apps that share the same database do not appear in the problematic app. While it seems to function normally on a Samsung tablet , it fails to pick up updates created in the fully functional apps. It has stopped working at all on all other devices IOS and desktop. 😥

It has been two weeks since I raised this issue with the support team and I have not received any guidance on where to focus my efforts. Having removed all data & date-time calcs for initial values, I'm working completely blind, as the information provided in the audit log doesn't indicate which table or field might be causing the error.

This issue renders the app unusable since it won't run in the editor either with the synchronization error.

Does anyone have suggestions on how to resolve this issue? I would hate to throw away two years of work and start over.

Thank you.

[Suvrutt_Gurjar]

You mentioned the app is not working in desktop mode.

So one option you may want to give a try is if you are using "new desktop" mode, you may want to switch to "legacy" desktop mode or vice versa.  This may eliminate , if at all, any possibility that the error could be to do with either legacy or new desktop mode being selected. 

Also just in case you are using Google sheets as backend, you may want to try setting all the backend columns , especially those having "date, date time values" to "plain text" if those are not set to that format.

Of course, please do all these suggested steps with utmost caution, preferably in a copied test app, so that your main production app does not start showing any additional errors. We from the community may not be able to help with any additional errors because the community helps just based on shared information.

Edit: Also there are two previous posts on the topic with solutions that those respective posters found. Please take a look just in case those help.

Search - Google Cloud Community

[Harriswe · Accepted solution]

Wow what a terrible journey 🙁 to uncover the issue. Many thanks @Suvrutt_Gurjar  one of the posts mentioned did help me find the culprit.

Thanks to this post: Un-representable Datetime Error (the added or subtracted value,  I was finally able to identify the problem. However, waiting for 14 days for a response from the AppSheet Support Team proved futile and their only suggestion to check the initial values turned out to be incorrect.

After trying numerous options—blanking date fields, date-time and time fields and even deleting a recently added table (desperate times call for desperate measures) 😳—the post pointed me to a virtual column. The issue stemmed from AppSheet's handling of virtual columns and date calculations, even when the formula is correct and works fine in an actual column. 🐜 (Bug-1)

The problematic virtual column was calculating the LeaveByDate, ensuring compliance with the law that requires the exit day to be the day before the next payment is due, which must also be a workday. Here’s the formula that caused the synchronization error that completely killed 🛑  the app:

>>>
WORKDAY(
DATE( TEXT([TenancyDate], "dd-") & TEXT(EOMONTH(Today(), 2), "Mmm-YYYY")), -1)
<<<

This needs immediate attention from AppSheet for the following reasons:

1. **Server-Side Synchronization Error**: The error was captured on the server side, but there was no useful information in the Editor or Audit to identify the issue. Even the AppSheet support experts couldn't pinpoint the problem after 14 days.  🐜  (Bug-1)

2. **Stable Version Issue**: Setting a user's account to use the stable version did not work because the synchronization cycle failed to pick up the change. Users could not revert to the stable version, which is a significant bug. 🐜  (Bug-2)

3. **Rollback Failure**: Another major failure is reviewing previous versions before roll-back could not resolve the issue.  The AppSheet editor reported the same synchronization error across multiple stable versions, making it impossible to revert to a working version. 🐜 (Bug-3)

4. **Historical Stability**: This virtual column wasn't a recent addition. It was added to coincide with the Notice to Quit letter templates and had been functioning correctly for some time. Yet, it suddenly caused a catastrophic failure. 🐜 (Bug-4)

5. **Need for Stronger Error Prompts**: I welcome @William_Robins 's comments on the need for stronger error prompts to guide users more effectively.

These bugs, triggered by an issue with a virtual column, led to a catastrophic failure of the app, halting development and impacting all users. This is a critical issue that needs immediate attention from the AppSheet team to prevent future occurrences.

Thank you for your attention to this matter. I hope for a swift resolution so others can avoid these significant issues in the future.

[Suvrutt_Gurjar]

Thank you for the update. Nice to know that you were able to find a solution. 

[Harriswe]

Unfortunately, this bug has resurfaced unexpectedly. Everything was working fine when recording entries in the log on 28-Aug-24, but after returning from annual leave at the Paralympics in Paris, the synchronization errors started appearing again.

This issue is fatal to the app since it prevents even using the app in the editor. I initially reported this bug to the Support team back in June/July and it's still unresolved. Rolling back to the two previous versions, as I’ve done before, hasn't resolved the issue either. To make matters worse, I'm unable to switch my account to the stable version because the synchronization process fails early on.

Has anyone else experienced similar issues or found any potential remedies? 🧐

[Suvrutt_Gurjar]

Could you check if the following expression works for the VC

WORKDAY(EOMONTH(TODAY(),1)+DAY([TenancyDate]),-1)

in place of the existing expression of 

WORKDAY(
DATE( TEXT([TenancyDate], "dd-") & TEXT(EOMONTH(Today(), 2), "Mmm-YYYY")), -1)

Also you may want to delete the VC with the existing expression if possible and load the app just to check if the app works without that VC again and if it is loading without the VC, then you could be sure that the existing expression in the VC is an issue again.

Of course, please do the needful with utmost care and at your discretion, since the app is in production. 

[Suvrutt_Gurjar]

I think now I understand why you are getting the error "Error: The added or subtracted values results in an unrepresentable" 

I believe the expression 

WORKDAY(
DATE( TEXT([TenancyDate], "dd-") & TEXT(EOMONTH(Today(), 2), "Mmm-YYYY")), -1)

will not work on certain days if the [TenancyDate] date is 30th or 31st December in any  year. You can check the expression in any VC by entering a [TenancyDate] of 30th or 31st December because it computes the date as 29th Feb or 30th Feb that are not legible dates. ( of course 29th Feb will work if the previous year is one year before leap year)

I could reproduce the error as follows when I tested for the date of 31st December.

The app produces the above error when I try to set the [Order Date] as 31st December as shown below. It will compute LeaveByDate as 30th Feb with your expression. Note that the app is not syncing and if I try to force sync the above error is displayed.

This is so because in your existing expression, the day of the date is computed from the [TenancyDate] such as DATE( TEXT([TenancyDate], "dd-") and a date of 29th and 30th does not exist in February. 

On the other hand , the expression I shared 

WORKDAY(EOMONTH(TODAY(),1)+DAY([TenancyDate]),-1)

simply adds to the calendar days with +DAY([TenancyDate]) part. So it will not fail in February dates.

Hope this helps.

[Harriswe]

Thank you @Suvrutt_Gurjar for your response. I did try the legacy desktop mode but encountered the same issue. During synchronization, the following error appears. I also changed all time fields to text in a copy of the app, but the problem persists.

I've reviewed all the tables used in the problematic app that are not present in the other functioning apps, but the issue remains.

What’s particularly concerning is that I cannot roll the problematic app back to a previous version or even a stable version. When I look at those versions before performing a rollback, they present the same issue in the editor. I have even tried using the editor on different devices, including: Apple MacBooks, Chromebooks, iPhones, iPads etc, but to no avail.

I have also checked the database for any strange time values.  Forgot to mention that these apps are running on a MySQL database, not spreadsheets.

Any further suggestions would be greatly appreciated. 🤔