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:
- Client approves one of our contractor's timesheets, but leaves the app a fraction too quickly (change successfully saved to Database, but the devices thinks not)
- Later (on a schedule) the app picks up all approved timesheets under each contract and invoices the client in accordance with contract terms. In short; A BOT creates a Draft Invoice, then Adds all un-billed timesheets ([Invoice] column is blank) to the Draft Invoice as child records - by adding the new draft invoice row-key (ID) to the [Invoice] column on each timesheet, adopting those timesheets as children of the Draft invoice, calculates the invoice value, prints the PDF invoice and emails the invoice and associated timesheets to the client.
- Later again, the original approver re-opens the app and the timesheet that was already approved is 're-approved' - row sent to database again as the sync finishes it's last fraction of a second. Issue here is that when the approver syncs through the change (a repeated save), the approved timesheet row (as it was still stored on the device) is sent to the database with a blank [Invoice] value in the row.
- Auto-billing system picks up the approved timesheet again and bills it a second time.... ๐ณ๐คฏ๐ฉ - NOT Good.
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.
- Band-aid solution, not repeatable, not good enough
- Annoying from a UX perspective if the user has say 5 or 10 timesheets to approve (though we can tell the action not to sync the device unless the timesheet being approved is the last on the list)
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.
- I have not yet tried this, wondering if others have?
- My main question here is - will a before edit type trigger stop all edits to the row? As I understand that AppSheet sends the whole row again to the DB (even when only one column value has changed). This is not desirable as I don't want a trigger to protect the whole row, only I want it to prevent deletion of the inserted [Invoice] value. (For example, one still needs to be able to add comments to the Timesheet[Comments] column).
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
LinkedIn
Twitter
