PLEASE HELP! Cell values disappear from backend when updating other columns

[Lindsey_Lindow]

I apologize for the long post, but I assure you it's all necessary in painting a picture of this issue. I'm unsure if the best way to articulate this is by stating that column values are being reverted to blank cells or deleted.  My app is used in an Auto Repair shop and I was recently asked to add required image fields when users add vehicles to be repaired.

In the form view for adding new rows, users are required to capture two images of every vehicle (one vehicle per row in the backend sheet). Since the purpose of the images is to document pre-existing damage, after the form is saved, users cannot delete or edit the images. Our problem is that the images often disappear from the Google Sheet after syncing updates made to other columns. By comparing the affected cells' Edit History in Google Sheets with the Edit History of other cells in the same row, I'm able to deduce which cells were updated through Appsheet that apparently triggered the image values to be deleted. I've done this with many affected rows and found no pattern, as the deletion occurs after syncing updates for as many as 12 different columns. Oddly, the issue has also occurred immediately after saving new rows, long before any other column values were updated.

After thoroughly checking the app for possible causes and finding nothing, I opened a Support ticket. I was asked questions including: What timezone I'm in (answer: Central), Are security filters applied (answer: [DATE] >= EOMONTH(TODAY() - 240, -1) + 1), Does the app sync after every change (answer: Yes), Are there any empty rows followed by rows containing text (answer: No). After a week, my ticket was closed and I received an email stating:

"We have thoroughly checked your application and found no action, formula or filter is causing the images to be deleted. We have also checked the provided timestamp's audit log and there was no image deletion. It might be possible that there is some issue with the database(spreadsheet) which is causing this issue."

I don't understand how the Google Sheet could be causing the issue when the deleted value's Edit History timestamp matches the Edit History timestamp for updates made to other columns through Appsheet.  The backend Google sheet does not use any formulas or scripts. I don't know where to go from here and I'd be grateful if the great minds in the Community could share their thoughts. I'm including screen shots for 6 different examples where I compared Edit History timestamps to identify which Appsheet updates I believe triggered the unwanted image deletion.

• Example 1:

• Example 2:

• Example 3:

• Example 4:

• Example 5:

• Example 6:

[Marc_Dillon]

Are you using appropriate keys?

Do you have multiple users on different devices who are editing the same records around the same time?

[Lindsey_Lindow]

Thanks for reading my post, Marc. I have a designated key column and the value is set with the UNIQUEID() expression. Yes, sometimes records are edited on different devices around the same time, resulting in the "last update wins" behavior so the first user's update is overwritten by the second one. 

[Lindsey_Lindow]

@Marc_Dillon When I added the new columns, I placed them between existing columns vs. after the last column on the Google sheet. Could that cause any unwanted behavior?

[Marc_Dillon]

It could, yes. GSheet records are updated based on column position, not by matching column names to the header row.

Assume you start with this:

id	val1	val2
a	1	2

Then you add a column in the middle:

id	val1	newcol	val2
a	1	some new value	2

Then a user edits that "a" record, by changing [val2] from 2 to 3, but without syncing to a new version of the app, the backend will change to this:

id	val1	newcol	val2
a	1	3	(the 2 value might remain here?)

*assuming it doesn't result in an error.

[scott192]

Do you have Reset on Edit ticked for the disappearing column data?

[Lindsey_Lindow]

I just double checked that and no, it is not enabled. The issue doesn't
occur with every row edited, but I'd say it happens on about 40% of the
rows. I can't find anything the affected rows have in common that
differentiates them from unaffected rows. Also, sometimes it occurs on the
first edit made to a row, and other times a row can be edited multiple
times before the image values get deleted, and sometimes the image values
disappear from the backend sheet during the sync from adding the row. It's
extremely unusual and I'm wondering if it might be a bug?

[scott192]

Is there an indication of which users are triggering the erroneous data?

I had a case once where I had updated an App and assumed it was all working.  Weeks later I discovered data errors and I (and Appsheet Support) managed to discover that some users were using an old version of the App.  They had left the App open in a tab and never closed down their devices (Chromebooks), simply closed the lids and suspended their sessions.

The only way to track this down is within the Audit History log or if the data rows indicate which user is making the changes...It's worth a try anyway as you seem to have exhausted all other options...