Add if new barcode, update record if barcode exist

[Keat]

Hi, I'm a beginner here, looking for some advice from the expert.

When add item by barcode, if the barcode already exist then i want app update/replace the Price & Stock for the item without creating new row, only add new barcode if does not exist, may i know how achieve?

Or have any portfolio or sample to refer would be great 😃

Thank you & have a nice weekend.

[WillowMobileSys · Accepted solution]

What I would do is create a scanning Form, that allows you to scan a barcode and then Auto-Saves executing an attached action.  This action determines if the barcode exists or not and then performs the appropriate view function to either open a new form to add a row (with barcode pre-populated)  OR opens the existing row for editing.

To do this, You will want to:

1) Create a scanning table and add to the app - a table with and ID column and a barcode column.  Manually add a single row with the ID manually inserted.  Once added to the app, set the Barcode column to "Reset on Edit".  This table will only ever have a single row used solely for scanning barcodes. (see how in #2)

2) Create Form view on this new table, and set the "Row Key" value to that manually inserted ID.  This makes sure the scanning Form always opens the same table row and since the Barcode column is set to "Reset on Edit", it will always blank out any pre-existing value ready for a new one - but you may never notice...see #3.

3) Turn on Auto-Save on the new form.  Once a barcode is scanned AND because it is the LAST (and only) field to populate, the Form will automatically save firing off the attached action described below.

4)  Create a new action, called maybe "Add Edit Product" set to the newly added table. Define this action as "App: go to another view within this app".  In the "Target" field add an expression similar to (it may need to be tweaked for your data structure - I am assuming your main table is named Products) :

IF (ISNOTBLANK(LOOKUP([_THISROW].[Barcode], "Products", "Barcode", "<key column>"),

        LINKTOROW(LOOKUP([_THISROW].[Barcode], "Products", "Barcode", "<key column>"), "Products_Form"),

        LINKTOFORM("Products_Form", "Barcode", [_THISROW].[Barcode])
) 

      NOTE:   The above expression is an example, you will need to change names and it may require small syntax corrections.

5)  Set any other properties in the Action to your preferences.

6) Attach to the action to the Form-Saved Behavior.  You will find this section at the bottom of your new Form.

7) I would also recommend turning on the "Advance Forms Automatically" feature but this does affect all Forms in the app - which you may want anyway.  In this case, when you open the Scanning Form, because the Barcode field is the first, and only, field on the Form it will automatically advance the Scanning window ready to scan without any additional button clicks

😎  Putting it all together...With the implementation above and including the Auto-Advance and Auto-Save features, the process will work like this...

Tap the Scanning View button to open the scanning form.  It will automatically "advance" to the scanning window ready to scan a barcode.  Use physically scans a code and because of Auto-Save, the scanning form automatically saves executing the attached action.  The action compares the scanned code against your Products table and if the barcode is present, the action will open that row ready for edit.  If not, then a new Form open ready for entry.

Additional Information

LOOKUP Information

LINKTOROW Information

LINKTOFORM Information