Bug: Appsheet Script Randomly Replace Row Value

[desmond_lee]

Hi Appsheet Expert

There is a very serious issues in my e-Maintenance apps. I don't whether this is really a bug or this is a bug that I created from the way I develop the e-Maintenance apps. The issue is appsheet script is randomly replacing row data which is set to edittable mode. I corrected it many times and this phenomina keep happening randomly. The key (Col B) is a number that combine the value with a few columns to form TICKET ID. But some how appsheet does not add on the next row when a user save a form instead she randomly picks one of the row and replace the whole row value except this not editable.

I am able to prove this because every data submitted google sheet script will backup a copy in the TICKET OVERALL tab. See evidence below

Evidence LInk

https://drive.google.com/drive/folders/1U22yTxipVCRpmb6OHqDx-ZxAiQxftjpO?usp=sharing

Thank very much

[desmond_lee]

Setting...

[Joseph_Seddik]

What do you mean by AppSheet script?

Would you please show the expression used for the Row column?

[desmond_lee]

I am referring to After user save form the data will tabulated to the google sheet table. The capability I refer to the script of the appsheet. Maybe I am wrong and not call it script due to I am new with Appsheet.

[desmond_lee]

Here is the expression

[Joseph_Seddik]

Thanks. When the user is in the form, what value is shown in the Row field?

[desmond_lee]

I am not sure on your question but I will give you this info. User at form cannot see the ROW field. The ROW field hidden and its is just an apssheet formula generate ID and there is no value to see. So I don't which value you refer to.

[Joseph_Seddik]

This is the Key column. It determines which row will be edited or if a new row will be added to the table. 

To help troubleshooting don’t hide this column in the form, and check what value is being generated. 

[Steve]

What is a key? - AppSheet Help

[1minManager]

My hunch is that you've got something thats causing the [Ticket ID] column to re-run its App formula.  So ANYTHING which adds to or updates any column in the Ticket Issuance table will cause this App Formula to be recalculated.  This could be a Bot, API, Action or maybe something else.

Simon@1minManager.com

[desmond_lee]

I have a lengthly discussion whit one of the Appsheet support and what he suspect here is due to the sequencial key and the users. This column B sequencial key is good for single user and I have 500 user out there key in randomly reporting breakdown. Some is have very old phone some having latest Iphone. There are time when user A and user B submit and they happened to be trying to add on the same row and cause data to be replaced. I am tring to change the key format and see this could be resolved. Below is the article that appsheet share to me

[Steve]

Serial Numbers, If You Must - Google Cloud Community

[desmond_lee]

Thanks. Trying a new format key now and see monitor for a week

[Joseph_Seddik]

If you absolutely want to use sequential rather than random numbers as a key, even if you format your key to include a sequential number in combination with other values, then there is only one secure way to do that. There's no other way, and no formatting or combination could prevent multi-user duplication of sequential numbers unless you do this:

Assign the sequential number to the record only after the record has been created not before.

To do that, you should have your key as UNIQUEID(), then setup a Form Saved action to set the sequential Row value only if it is empty. 

Should you for some reason still want to use the sequential Row value as key, then your users will. have to add the rows to a buffer table with a UNIQUEID() key, then a bot would copy the records to your original table, setting the sequential Row value, then deleting the record from the buffer table. 

[desmond_lee]

I have change the key to timestamp with real time initial value =Now(). That resolve the whole issue of random replace row value however I encounter a new issue.  Column B (ROW) now become a pure running number which I use appsheet formula (MAX(Ticket Issuance[ROW]) + 1). And you can see Row 190 and Row 194 is repeating the number 188. Its and independent column running number. I just can't understand why.

[Joseph_Seddik]

As I told you, in green, there is only one way. 

[desmond_lee]

"setup a Form Saved action to set the sequential Row value only if it is empty." I am not good in doing this. Can you show me some screenshot to get there?