New Bug Encountered: Critical Workflow issue when multiple data changes triggers multiple occurrences of a workflow

[WillowMobileSys]

I have encountered an issue when an app has submitted several independent row changes resulting in multiple triggers of a Workflow. The short of it is that it seems the updated row is reset to its original state at the time the changes were made when each trigger instance is processed. For example, I have boolean flags that the Workflow sets to TRUE in an attempt to additional triggers of the same Workflow on the same row. But when the next workflow trigger is processed, this row value is set back to unassigned.

The data changes to the same row made during workflow processing DO NOT stay. However, processing to any OTHER rows during workflow processing DO stay. This creates an inconsistent state of the data between execution instances. In my case, calculations end up performed multiple times when only once is desired.

I have uploaded a video to YouTube to help with understanding of the issue I see.

Video:

The tester app (link below) works like this…I have a sheet named Calculation which holds result values. Each is identified by a Code. I enter in the Main sheet a record that indicates a Code and value to be subtracted from the result. When the record is saved, that generates 1 workflow trigger. I have added an Action on the Save function that simply sets Flag1 to TRUE and then resets it back to FALSE. This generates 2 additional workflow triggers.

When the Workflow is processed, it sets a flag StopFutureProcessing? in an attempt to prevent any additional updates from occurring for this row, it performs the calculation subtracting the entered value from the result and then sets a Calculated? flag to try and prevent additional calculations from happening.

I am finding, because the row reset to original state, Workflow triggers 2 and 3 are processed and the calculation is performed 3 times.

Please post any questions or requests needed for further clarification.

App:
https://www.appsheet.com/start/e676fd64-7ec9-4231-a30b-33dbe153ed24

[Phil]

Hi John,

You did an excellent job of describing what you are seeing! Thanks for doing that, because it makes it much easier to respond.

What you observed is correct. This is a consequence of our “last writer wins” conflict resolution strategy. See this article https://help.appsheet.com/articles/962224-concurrent-usage-with-multiple-users

In your case, the client is sending the row three times. Each time the row is sent by the client, it overwrites the previous row value residing at the server. In your case, that overwriting is simply more obvious because it undoes any changes made by the workflow rule.

There is currently no mechanism to block the second and third client changes from being applied at the server and overwriting earlier changes. That includes changes made by workflow rules triggered by earlier client changes.

We are exploring offering a range of conflict resolution strategies in the future. For example:

1. We could apply updates at the field level rather that at the entire row level. I this model, if you only change a handful of fields on the client, then we would only update those fields. Fields you did not change through the client would remain untouched on the server.

This is attractive at one level, but it can introduce a new class of “consistency” problems. For example, if two fields in the record should always match, then applying changes at the field level can make the record internally inconsistent when only one field is updated. In real applications, the consistency rules for fields within a record and between records can be quite complex.

2. We could keep track of the current “update level” of each table row. When a client reads a set of rows from the server, each row would have an associated “update level” reflecting the state of the row when it was read from the server. When the client performs an update and syncs that change to the server, the updated row would contain the original “update level” that was obtained when the row was read. The server would compare the “update level” of the incoming updated row to the current “update level” of the same row on the server. If the server detects that the row has been changed by some other client, then the server would reject the current client’s update with a “version update mismatch” error. The client could then reread the row and attempt to apply the change to the latest version of the row.

Version conflicts are inevitable on distributed systems with multiple clients. They are aggravated when offline updates are allowed because offline updates increase the window in which conflicting updates can occur.

Does my explanation describe what you are seeing?

[WillowMobileSys]

@Phil Yes, your explanation makes sense. And I should have realized that’s what’s going on. After all I have been telling others that AppSheet is a row-based processing system.

I guess what threw me is that the Workflow happens after the data save. It just didn’t dawn on me that any processing there is also overwritten.

I have been in software development for a while. Row-based processing is a bit different animal than I am used to. Now that I am implementing more complicated functions, I simply need to adjust my manner of thinking about the problem.

[Juan_Monroy]

Hi John, I’m going to paste here something that I just wrote having an issue similar as yours, may be that would help with your problem…

"Found the problem within the Workflow IF THIS IS TRUE Condition.

Since I had many Actions related to each step of what I named the Consolidation Process, I established a broad open Condition so that the sequence of several (many) consecutive actions could be triggered. That was my mistake, cause I din’t know that AppSheet is a redundant based software, which means that every time an Action modifies data, it might triggered the Workflow all over again, which could derived in chaos during the process. The way to solve it is by creating multiple Workflows that satisfy each and every precondition for it to be triggered.

Lesson: Carefully verify your Workflow Criteria and don’t hesitate to create many, as long as they do what you really want AppSheet to do."

As I said, hope this could help you with your issue.

[WillowMobileSys]

@Juan_Monroy Thank you for your response. And your realization of breaking into more succinct Workflows may prove helpful.

In my use case, I need to perform several functions upon a single save. Each of those have decision trees to determine the proper action. For example, I want to send an invoice BUT I need to determine which invoice - unpaid invoice or paid invoice. I was using flags, as suggested within the documentation to prevent multiple sendings. But setting and reseting flags then results in data updates as well which led to more flags in attempts to prevent multiple processing of calcs.

I need to rethink the problem. It may be that I need to use more complex conditions with more Workflows as you suggested. Or maybe the data used for control of processing is separated from the client data. Or a combination of both.

I’m sure there is a solution to my issue!!

[Juan_Monroy]

I’m sure too that there’s a solution. What worked for me was by creating multiple similar Workflows, but this time restricting each one of them with such Conditions (pretty elaborated restriction expressions) that only apply to what I know was going on with the data I was updating due to the preconfigured Actions. Would be like… IF THIS (BUT NOT THIS) THEN DO THAT

Other tool that could be used to have better control is the formula AND(ISNOTBLANK([Column]),[_THISROW_BEFORE].[Column]<>[_THISROW_AFTER].[Column]) will check if that Column’s value is changed or not. If it’s changed, trigger the Workflow.

Does this make sense to you?

[WillowMobileSys]

@Juan_Monroy Yes, makes perfect sense. I am familiar with all of those constructs.