Upsert (Update when exists, Insert when new)

[Douglas_Thom]

When I look up a key (in this case, a scanned barcode), it may be that the record already exists. It would be nice if there was an option on key fields to look up the key when entered and go to the record vs. just fill in the key and wait for the rest of the fields if the key did not exist. The current behavior is to LINKTOFORM with the data filled in, but then get a “key already exists” error; or LINKTOROW but the key doesn’t exist and get a “missing key” error.

Kind of like LINKTOROW and LINKTOFORM had a baby. Something like LINKTOFORMROW which allows the key first like LINKTOROW, and optional fields afterward like LINKTOFORM. Then, assuming you have some internal indicator saying the row already exists or not, you could set that appropriately so the correct behavior is executed upon Save.

[Steve]

I agree, this would be nice to have. You post inspired me to concoct a workaround:

1. Given table Inventory with columns Barcode and ID.

2. The Barcode column’s Valid_If allows duplicates, Required? is ON.

3. 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):

IF(
  ISBLANK(
    FILTER(
      "Inventory",
      ([Barcode] = [_THISROW].[Barcode])
    )
  ),
  [Barcode],
  UNIQUEID()
)

4. 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).

5. 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.

6. 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.

7. 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.

8. 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.

9. 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.

[Douglas_Thom]

Thanks, Steve! Yes, this was inspired by my post and what I did here: [Edit instead of insert for existing key]. And thank you, again, for your contribution there that helped me get around it. .

[Steve]

Heh. I thought the problem sounded familiar!

[Firdaus]

How if I want to add up the total “Qty” in the Request Material table, into the “In” column in the Material Stock table if the PIC and Material are the same