I agree, this would be nice to have. You post inspired me to concoct a workaround:
Given table Inventory with columns Barcode and ID.
The Barcode column’s Valid_If allows duplicates, Required? is ON.
ID is the key column, with an Editable? expression of
FALSE and an initial value expression of the following, which sets the column value to the bar code value if the value doesn’t already occur in the table, or to a random unique value if the bar code value is already in use (therefore, if
([ID] <> [Barcode), the row has a duplicate Barcode value):
([Barcode] = [_THISROW].[Barcode])
Create a slice called Upsert against the Inventory table with a row filter expression of
FALSE (to keep the slice empty; it’s exclusively for adds), only the Barcode and ID rows, allow only Adds (again, exclusively for adds), and remove all slice actions (the slice is for a very specific purpose).
Create an action called UpsertEdit for the Inventory table, of type App: go to another view within this app, a target expression of
LINKTOROW([Barcode], "Inventory_Form"), a prominence of Do not display, and to be done only if
TRUE is true. When invoked, this action will take the user to the Inventory_Form of the entered bar code. If the entry already existed, the user will go to the form of the preexisting row; otherwise, the user will go to the form for the row just added.
Copy the Upsert_Form system-generated view to Upsert, set the position to menu, and and set the Form Saved event action to UpsertEdit (the action created above). Users should use this form when scanning bar codes. This form will present only the columns included in the Upsert slice (which should just be Barcode), and will invoke the Upsert action automatically when the form is saved (which will redirect to an existing row if present). Note that this form will create a new row whether the bar code value is present already or not, but the user will be redirected to the preexisting row if there is one.
Create a new workflow rule called Upsert targeting Inventory for ADDS_ONLY only if
([ID] <> [Barcode]) is true, in which case, invoke the existing, system-supplied Delete action. The idea here is that if the AppSheet servers receive a new row for the Inventory table where the ID column value differs from the Barcode column value, that row should be deleted out-of-hand, automatically. You’ll recall from above that we specifically set the ID column to a random value if the entered Barcode value was a duplicate to indicate it’s a duplicate. This workflow rule ensures new rows so marked are deleted so they don’t cause confusion.
If you want the user to automatically return to the Upsert form after saving the Inventory_Form, create an action for the Inventory table with a type of App: go to another view within this app with a target of
LINKTOFORM("Upsert") and attach as the Form Saved event action for the Inventory_Form view.
If you want the user to start the app in an Upsert view, set the starting view (UX > Options > Starting view) to Upsert.
To summarize the above, we’ve created an abbreviated form to capture the scanned bar code. The form always creates a new row, but marks the row for automatic deletion if it would duplicate an existing row. The form then sends the user to the whichever row should persist, and any duplicate row is deleted automatically.
I did test this, but not in a production environment and not rigorously, so apply some discretion should you choose to implement it yourself.