Hi All,
ISSUE/BUG:
This is a critical issue that I will email to support directly, but thought I check in this wonderful community first if anyone has experienced this issue?
I've noticed that oftentimes a user will edit a row, and will leave the app a fraction of a second too early - before the sync has fully completed. The edited row, however, is successfully sent to the database.
Later, the same user re-opens the app and the same change is sent again (as if it was still queued on the device and then sent immediately upon re-opening/syncing the app).
There are many examples where this presents a critical issue, in a number of our apps. One example is:
Contractor timesheet approval and invoicing EXAMPLE:
SOLUTIONS available (that I am aware of):
1. Add a force-sync to the 'Approve' button that will force the app to sync upon the approver approving the timesheet.
2. Add a trigger to the Database (MySQL DB on Google Cloud) that blocks the subsequent row-edit so that the previously inserted Timesheet[Invoice] value cannot be edited back to blank.
NOT a Solution:
Adding validation that makes the row editable-if = false if the [Invoice] value is inserted. As the device that has stored the row (as 'not synced by just a fraction of a second') will follow this rule diligently and proceed as there is no value for [Invoice] as far as the device is concerned upon immediately syncing upon re-opening of the app.
Another POTENTIAL Solution??
Can AppSheet change the order of operations upon syncing the device on re-opening? I.e. fetch the correct data from the DB before finishing (in this case, repeating) the save of the locally-stored edited row.
Broadly the issue is.... well, broad:
There are many many examples where this sort of behaviour is indeed catastrophic.... I hope it's an easy bug to fix, so I can avoid going down the MySQL trigger path everywhere I look in all of our apps, as this too is really just a bandaid.
Thanks all...
@Steve @Aleksi @MultiTech @Bellave_Jayaram @JuneCorpuz @LeventK
It sounds to me like what you might benefit best from would be to take a step back and review the whole process.
It sounds like there's some part of the process that having this difficulty, so if you can change how you're doing that data interaction process - you can probably smooth out this problem.
Thanks @MultiTech I know what you mean and appreciate that. Itโs usually how I solve seemingly complex problems actually - stepping back / up to a higher level of thinking with a wider viewโฆ
However the issue exists of row edits being sent to the database more than once, and this is not expected behaviour. (Really I should cut the original post down to include the first three paragraphs only).
Cheers
So you're saying that if someone closes the app too fast, the app will send the record to the server - but not get the confirmation signal from the server.
Yup. The audit log shows two identical rows being sent to the server โ JSON identical, with the same โtimestampโ in each one but different โrequestStartTimeโ
I would think that these would be reconciled on the server with no problem
Indeed they are. The issue is the second one is at that point, old data thatโs missing information that had be updated in between the two sends. So the second send overwrites data with out of date info โ hence the title of this post.
Anyhoโฆ just another AppSheet quirk to be aware of and design around (though a pretty dangerous one in critical info systems)
Hmmm... you're on to something here.
Each update sent to the server contains a timestamp, and a BUNCH of other metadata... so the server should recognize that the "duplicate" is an "old" record.
Based on that combinations of variables - the server could pick out an old duplicate record like this, and filter it out.
It should be as follows:
Yep, thatโs the one Matt, I agree.
It would appear that โrequestStartTimeโ is being adhered to but it really should be โtimestampโ - especially in what is essentially an offline app (working with local data copies).
Having said that, youโve made me realise that unless AppSheet stores row keys and โlast update timestampโs in another database somewhere and checks this each time an update request is received, then itโs not possible to reject โoutdatedโ requests.
SoโฆIโm back to the point of putting an [update timestamp] column into all of my tables, having an initial value of NOW() set and Reset On Edit turned on for such columns, and creating a Trigger on my DB to do the rejecting thereโฆ Ohโฆ
Hoping somebody from Google can bump this across to the dev/engineering teamโฆ?
User | Count |
---|---|
37 | |
31 | |
29 | |
22 | |
18 |