BUG: Using the Update row action causes duplicate ...

WillowMobileSys · 06-23-2022 09:09 AM

Some time ago AppSheet introduced "upsert" capabilities for the action type of "Data: add a new row to another table using values from this row". It was in response to many requests to add the capability for updating an existing row in another table based on/using values from the current row.

While not perfect, it works!

However, I have noticed an issue where immediately after the action is executed, there are duplicate rows displayed in the UI (see image below) - one old and one new. Then after several seconds, depending on elapsed processing time, the old version of the row is removed (second image).

Obviously, when this action is executed a new row with the same key is inserted and then there is some clean-up to remove the old version of the row.

This is less than professional in the app visualization but more importantly it raises concern about data integrity based on the fact that duplicate row keys are even allowed to exist in the table - even for a brief few seconds.

EDITED: I have also observed that if back-to-back edits are made, ONLY THE FIRST IS RETAINED!

Please spend the necessary time to correct this flaw and perform the update in a more appropriate manner. For instance, the supplied column updates should be applied to the existing row rather than a new dup row inserted.

Viewing Table after action is executed

After a few seconds - old row is automatically removed

Marc_Dillon

I didn't think this was actually an official feature.

WillowMobileSys

I hope it isn't because it's a pain to use ... especially considering that you need to supply EVERY column or else it'll be overwritten.

If it isn't official then I have to wonder why there isn't yet, after all of these years, an official version. It is SOOO desperately needed....but I don't have to tell you that!!

Marc_Dillon

Well, I'm not sure if an unofficial feature can even be said to have "bugs", you know?

I personally have never used this "feature", and I doubt I ever will. We have "execute an action on a set of rows", combined with the INPUT() functionality if needed, which I feel will always be a better option.

WillowMobileSys

I agree it's probably not official. I still think its a bug since we CAN make it happen in a production app.

Regardless, for now, I need to find an alternative way. Thank you for reminding me of the INPUT() function.

I have never been able to get the INPUT() function to work and I have tried many times following several different examples. I MUST be missing something since others seem to make good use of it.

I need to spend the time to figure out what I am doing wrong. I believe I understand how it's supposed to work and agree for my use case it would be a better option.

dbaum

I haven't seen that option, and don't find it in my editor. Maybe it's only available via preview? Maybe only available for data sources with native support that AppSheet can leverage?

That implementation sounds problematic for any apps with logic premised on relative row number sequence matching row creation chronology.

Here's a definition of upsert for anyone else unfamiliar with the term.

WillowMobileSys

This is not a published feature. Knowledge was passed by word of mouth. As @Marc_Dillon mentions above, it's probably not official. It does work but has some quirks that I fear could led to serious problems.

"Upsert" capabilities are perfectly acceptable if implemented correctly. The AppSheet version (official or not) needs work so I don't recommend using it.

For those reading who don't know, there has been a long standing gap in the ability to UPDATE a row from values in another row - whether the same or different table. Currently, when you navigate through a series of actions to get to the one that will edit the desired row, that action only has access to the row to be updated. It doesn't have access to any other row. In many cases, other rows have required changed values to help decide HOW to update the row being operated on.

The INPUT() function is supposed to help with this problem by allowing values to be "remembered" and then retrieved in following action(s).

Steve

A great many of the AppSheet behaviors where "published" only because I took the initiative to document observed behavior. The existence and utility of this "upsert" feature is as valid as using ANY(...) rather than INDEX(..., 1), and using recursive actions (as for looping), among others.

dbaum

@lizlynch, it would be great to have this upsert functionality documented in applicable AppSheet help articles, such as the following. It is really handy that when adding a record via action or API if you specify an existing row's key that existing row is updated and no new row is added.

WillowMobileSys

@dbaum wrote:

@lizlynch, it would be great to have this upsert functionality documented in applicable AppSheet help articles, such as the following. It is really handy that when adding a record via action or API

As stated above, I do not recommend using this function and in its current state of implementation I would not advocate publicizing it. It can create more problems than it fixes. It actually works by inserting a second row with the same Key value. A process then cycles through to detect the "duplicate" rows and removes the old one.

The fact that two rows with the same Key value are allowed into the system is a major design flaw - it is BIGGER than a plain bug. It breaks the primary principle of this platform - Every row must have a unique Key Value. To knowingly break that core principle is ill-advised.

The Issue...

,and reason why I don't recommend using it, is that for several seconds (how many depends on processing saturation) the 2 rows co-exist. Any expression that then needs to retrieve THE row has 2 to choose from and may select the wrong one. Indeed, using ANY() or INDEX(...,1) functions WILL return the old row because without any sorting, it will always be the first row between the two dups.

The Frustration...

The above issue (old row displayed) becomes very difficult and frustrating to analyze. If a developer is not aware, or forgets, how this works, by the time they realize the issue and start investigating, the old row has been physically and automatically removed from the system.

Even for an experienced developer this can be a problem. Its hard to remember after 6 months what was implemented and even harder to remember that there is the possibility of the above described problem.

The Solution...

I am all for an "Upsert" feature but it needs to be implemented in a good way. Before this is advertised as a feature these changes should be made:

It actually retrieves the existing row and updates it - not insert a dup row.
It should behave like an Edit not a NEW row. Currently, if a column is NOT specified it will activate the "Initial Value" expression - overwriting the existing value. A developer is currently forced to re-assign all existing values.

If there is no intention of fixing this "feature" then the capability should be removed/disallowed altogether.

dbaum

@WillowMobileSys wrote:

It actually works by inserting a second row with the same Key value. A process then cycles through to detect the "duplicate" rows and removes the old one.

FWIW, in some rudimentary testing recently this isn't what I observed. Rather, the existing row was indeed revised. The specific context was that I was using the AppSheet API and an AppSheet database data source.

WillowMobileSys

Because your changes are being done on the server, I suspect the fact that a dup row was inserted is being obscured. Only one of those rows, the latest one, is pulled in by the Sync. My concern would that any subsequent processing AFTER the Upsert would pull in the old row.

Of course, maybe this bug only resides on UI side of things and doesn't occur on the server at all?

I have encountered this same issue as recently as a month ago when I attempted to try to use the Upsert it to solve an issue - but (sigh!) I couldn't.

dbaum

@WillowMobileSys wrote:

Because your changes are being done on the server, I suspect the fact that a dup row was inserted is being obscured. Only one of those rows, the latest one, is pulled in by the Sync.

Actually, in my testing I was looking at the data source directly (i.e., not via sync to app). The existing row was updated (i.e., the same row number) and no new row was added. Of course, it's possible that an undetected duplicate row was created and deleted but I didn't realize. Nonetheless, it gave me confidence that the existing row persisted (with the intended data update) since I understood your original observation to be that the existing row was deleted and the newly added row persisted:

@WillowMobileSys wrote:

the old version of the row is removed

lizlynch

All -- Just letting you know that the issues identified in this post have been raised with the AppSheet team. Thank you for communicating them!
Liz

Koichi_Tsuji

In the database world, the UPSERT is common sense, so I understood the AppSheet documentation is not really mention about it, but it is for sure to mention clearly about that.

With our apps, we are using this UPSERT features for the particular advanced tips and tricks, which is really helpful to improve the UX of AppSheet.

Add a new row to another table, Add row through API, and import from CVS. All the same. It is UPSERT, which should be supported formally by the appsheet, as it is telling how those functions are working, not a bug, not a supported feature bra bra bra.

The reason why we see duplicated row on app ux is pretty much simple.

If or not there is existing row with the ID value as we add through those function, that is not a task for CLIENT, but it is server side job technically speaking.

The one of the simple use case to verify this argument. Lets assume we have security filter where KEY column wih A001 value are ruled out by the security filter. SO we are not seeing this row on the client (App) . But we add new row through action, API, import CSV, or whatsoever, then CLIENET should not be a person to determine if or not there is existing row with the same value. So when we fire action, AppSheet view show the 2 x rows on the appsheet view. After the sync is finished, then the single will appear on the app view.

I dont think there is any Gremlins is behind the scene.

All in all, this is supported behaviror for years.

AppSheet dev team should agree with me, hopefully.

@takuya_miyai

@Aleksi_Alkio

@Steve

WillowMobileSys

@Koichi_Tsuji wrote:

Add a new row to another table, Add row through API, and import from CVS. All the same. It is UPSERT,

Your examples are NOT Upsert's. Upsert is a specific use case where the system decides to either Insert a new row or update an existing row.

Just to be clear...this post is NOT about eliminating Upsert. I too think it is perfectly acceptable to include it when it is implemented correctly. (It should be noted that Upsert has fallen out of favor in many systems. including databases, because of its confusing and sometimes problematic nature)

However, AppSheet is a relational system. The core principle of a relational system is that every row has a unique key. This principle should never be violated. The system should NEVER allow insertion of rows with the same row keys.

HOW can this be a problem?

I don't want to over-dramatize it but this can become a SERIOUS problem for an app. I have already shown in the original post how BOTH dup rows are being retrieved and displayed. This means, for a short period, the rows co-exist and treated as separate rows.

Now, Imagine if these rows contained some numeric data and after an Upsert there were calculations made including the "updated" row - meaning both dup rows are retrieved and included in the calculation. The calculated values will be WRONG. And this could be compounded if additional updates are made in the same manner - Upsert and then a re-calculation.

But the worst thing is that since the OLD row(s) are automatically deleted after they are potentially used in a calculation - an unsuspecting App Creator will never be able to figure out why the calculated values are wrong.

Steve

@WillowMobileSys wrote:

However, AppSheet is a relational system. The core principle of a relational system is that every row has a unique key. This principle should never be violated. The system should NEVER allow insertion of rows with the same row keys.

Concurrent usage with multiple users - AppSheet Help

WillowMobileSys

I assume you are providing this article to imply that AppSheet DOES allow rows with duplicate keys - when it is two different users?

That actually is not the case. Each user is operating in separate distributed environment. At the time the rows are INSERTED, in those environments, AppSheet has no knowledge of any other rows with the same row key. Each mini-environment still meets the core principle of only a single row key.

When these distributed mini-environments are merged/Synced back to the server, the first occurrence INSERTS and the second OVERWRITES - at least I hope that is how it is being handled. Core principle is still intact.

Maybe you are referring to this section:

This is what I EXPECT...BUT it is NOT what's happening. If I perform an ADD action and provide an existing row key, a SECOND row is being INSERTED with that row key - at least when this add is performed on the client/device side. I can actually visualize these two rows in a view for a brief few seconds - as indicated in my original post - though you cannot see the row keys.

I suppose I need to create a demo app to illustrate this.

WillowMobileSys

Ok, I have created a video demonstrating this issue. Please let me know if you think I am missing something or if you have any questions.

https://drive.google.com/file/d/18_EW-7hDXwwykt--Xx5mCrl_e-bFiiVc/view?usp=share_link

Koichi_Tsuji

If I understand your setting correctly, the sum result should end up with 9, as your app calculated.

Once the loop action is fired, then it will add new row (to the same table) and then move to the next action to calculate sum by adding new row to another table. At the time the sum calculation is made at that point of time, there are two records in source table (although keys are duplicated, but this is indicating Appsheet never know the row keys ar duplicated, so first row is saying 2 as a result of calculation, then the same loop happens another 7 time, so ending up with 9.

This is tricky subject, so I m not perfectly sure what the best answer to your query. However, I know this behavior, so we always apply "force sync" on firing upsert action OR we fire upsert through the BOT. I understand this is not going to be a solution to your case though.

lizlynch

@WillowMobileSys - Thank you for this video demonstrating the issue!

Liz

WillowMobileSys

I am not clear on why others are defending the current Upsert processing of inserting a row when one with that same key already exists. It is just plain wrong in a relational system.

Here's another take on it, if I were to try to add a new row through a Form View and manually assign the row key with one that already exists, it is rejected by AppSheet. The same should happen within the Upsert action - i.e. it doesn't matter if a user is trying to insert that dup row or an action is - they both should be rejected.

And because this is a distributed system this needs qualified as - "NEVER allow rows of the same key to be inserted within the same distributed dataset". This goes for server-side as well as client/device-side. Synchronization will take care of any dups across distributed datasets.

Your mention of a "force sync" won't work, as you said, in the use case I outlined because it ALL processing occurs client/device-side.

You are the second person who has mentioned using Upsert on server side without issues though I expect there are no calculations performed immediately after the Upsert. I will change my test to run the Upsert from a Bot on the server to see if the same issue occurs.

WillowMobileSys

For what its worth, I ran the exact same Looping action demonstrated in the video above but now triggered by a Bot. The end result is just as I would expect. Below is an image of the resulting Sums where on each iteration the SUM was 1 as expected.

For those who haven't seen the video above, when this same Loop action is run on Form Save, the "Sums" table has incrementally increasing sum amounts because the Upsert is inserting dup rows instead of updating the existing row - leading to increased SUM amounts.

Bottom-line...it appears the Bug I have outlined is only on the client/device-side.

Running Upsert iterations on server yields expected results

BUG: Using the Update row action causes duplicate rows to appear for short period.