Edit instead of insert for existing key

[Douglas_Thom]

I have an app where the user scans a barcode and then enters some inventory information about it. They do inventory once a day. Thus, if the inventory was already recorded, there’s no point doing the inventory again.

So is this behavior possible?

• User scans barcode
  • If barcode exists, retrieve and edit the current record
  • If barcode does not exist, present an empty form for new values

Thank you for you time,

Doug

[LeventK]

@Douglas_Thom
Sure it’s possible. You can use the below expression in Initial Value of the fields:

IFS(
IN([Barcode], TableName[Barcode]),LOOKUP([Barcode],"TableName", "LookupColumnName","ReturnColumnName")
)

[Douglas_Thom]

Handy little shortcut, and thank you for taking the time to send it, but I think that doesn’t solve the problem of the record being there in the first place. When it saves, the app thinks it’s a new record but the data knows it already exists.

I was thinking something along the lines of an “upsert” (insert if new key, update if key exists).

[GreenFlux]

You could do this using 3 Actions:

Action 1 & 2: Go to View in this App
New Item >Inventory Form
Existing Item > Inventory Detail (using LINKTOFORM( [ID]=[_THISROW].[Scan]) )

Then create a 3rd grouped action to execute the first two every time you scan a barcode. I’d go with @Steve 's idea about using a simple table to capture the scan.

Then, under Behavior of Action 1 & 2, use a formula in ‘only if this condition is true’ to make the New Item only run when the barcode value is NOT found, and Existing Item when it IS found.

You can use the IN() function to test for the scanned barcode value in the list of existing Inventory.

IN([Scan],Inventory[ID])
List Expressions and Aggregates

[Steve]

Neat idea! Here’s how I imagine it working (untested!):

1. Create a simple table (e.g., Scan) with one column (e.g., Barcode) to receive the scanned bar code. The Barcode column should (necessarily) be both key and label.

2. Create a (or use the AppSheet-provided) form view (e.g., Scan_Form) for the Scan table. Enable Auto save and Auto re-open.

3. Optionally make Scan_Form the app’s starting view by selecting it in UX > Options > Starting view.

4. Create an action (e.g., Edit_Target) to send the user to the appropriate target. Use an action type of App: go to another view in this app and a target formula of:

IF(
  IN([Barcode], Inventory[Barcode]),
  LINKTOROW([Barcode], "Inventory_Form"),
  LINKTOFORM("Inventory_Form", "Barcode", [Barcode])
)

5. Create another action (e.g., Handle_Scan) with an action type of Grouped: execute sequence of actions. For Actions, choose the Edit_Target action created in (4), above, as the first action, then the AppSheet-provided Delete as the second action. This group action will (once attached, below) send the user to the appropriate Inventory row, then delete the Scan row (without affecting any Inventory changes).

6. Attach the Handle_Scan action to the Scan_Form view as the Row saved event action. This will cause the action to occur automatically when the user submits the form (which should itself happen automatically as soon as a bar code is scanned because we enabled Auto save for the form in (2), above).

[rmsmeltz]

It took me forever to get this set up and running just the right way, but it works like a charm! The only tweak I made is keeping an ID column in the simple scan table and setting it as the key column. This allows me to scan the same item more than once. Thanks a ton, @Steve!

[mlNiks0110]

Thanks a lot for this Steve, I had the same issue and now its working like wonders.

[NJLOON]

Hi Steve,

For step no.5, i created an action with "grouped: execute sequence of actions." However, it did not delete the scan table barcode column data after it link to inventory form view. Even i completed filled the inventory form and saved, it re-directed back to the scan form but the data in the scan table still existed. Do you know why the "delete" action did not execute?

could it be the action will not execute after it link to another form view?

[Douglas_Thom]

Thank you, all, for your suggestions! They all helped me to come to a solution.

I’ll share it in case anybody is interested later. The entire thing is a bit more complex due to the key to support multi-users, but here is the summary.

Requirement

• I have a list of inventory entries that have been taken today
• The idea is to hit “+” and add a new entry
  • If the inventory count had already been done today, edit the current entry

Solution

• Inventory_LIst (view): List of existing entries – simple table on a view, nothing fancy. The user likes to see what the counts are for the day. Add opens a blank Inventory_Form.
• Inventory_Form (view): Field entry including (part of) key [UPC]. Save invokes a Finishing Form which is the Scan Barcode_Detail
• Scan Barcode_Detail (view): is an edit-in-place form of just one field from a User Settings data source that just contains settings and field selections (e.g. for this and contextual reporting). It has a Scan Barcode Action on it
  • Scan Barcode (action): basically reads
    IF( IN( [Scanned Barcode], TodayInventory[UPC] ), LINKTOROW( [Scanned Barcode], "Inventory_Detail" ), LINKTOFORM( "Inventory_Form", "UPC", [Scanned Barcode] ) )

Thanks again, Team, I really appreciated the suggestions.

-Doug

[Nbl]

Can you provise detailed version please. Iam stuck with the same situation 

[Douglas_Thom]

Quick postscript…

Now I’m looking for a way to change the “+” on the list to the Scan Barcode action. Even though I’ve assigned it to the Data and View, it will not show up with the “+” or in place of it.