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

[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

[Keat · Accepted solution]

Hi, thank you for your Step by step guide👍🏼, it's really useful for the beginner like me,  now I'm more understand where the setting should be apply, the target formula is not working for me, I'm searching from another Q&A & it's works as what I'm looking for.

The target formula below is working for me:

IF(
  IN([Barcode], Products[Barcode]),
  LINKTOROW([Barcode], "Products_Form"),
  LINKTOFORM("Products_Form", "Barcode", [Barcode])
)

Thank you again for your reply, appreciated it.

---

[WillowMobileSys]

Yes, your expression works if Barcode is the key column.  In my example I was assuming it was not.

As a side note,  I would very strongly suggest against using the Barcode as a key.  Barcode for products change all the time or the same Product might be inserted with different barcodes to reflect different versions of the item.  In these situations, having barcodes as a key value will cause data issues.

One might think, "I am ok now and will change it later when needed".  You can do that but presumably the app would have grown and once the key value is propagated to other tables it is a very tedious process to find and replace all of the occurences.

I recommend, from the start, to use a dedicated "dataless" key column - key values NOT tied to any of actual data.  AppSheet provides the UNIQUEID() function for this purpose.  It is only one extra column in each table but once a key value is set and propagated, it will never need to be changed again.

[Keat]

Hi, thank you for your suggestion, due to now i still I'm facing 2 issues as below.

- Create Form view on this new table, and set the "Row Key" value to that manually inserted ID.  - What value i should key in?

- Facing error when follow your Target Formula - "Function 'ISNOTBLANK' should have exactly two parameters, a table name and a filter condition"
will try to figure out again & follow your suggestion.

Thank you.

[WillowMobileSys]

What value i should key in?  -  You can use any value you wish - e.g. "abcd1234"

"Function 'ISNOTBLANK' should have exactly two parameters, a table name and a filter condition" - I have seen this message and it is horribly inaccurate for the ISNOTBLANK function.  Its presence likey means your syntax  for the function is wrong.  It should be similar to:

ISNOTBLANK ( [ <<some column name>> ] )

OR

ISNOTBLANK ( <<some expression that returns a list or value>> )

[LosEndos]

Hi there! I found this detailed reply to the OP's question this week as it is very similar to what I am trying to achieve. Took me a few nights to get it working as you described, Willow, but thanks to your detailed help to the OP, I just needed to take my time through trial and error to understand how it all works for my case. I am a newbie too so the above was VERY helpful.

I have one question that, if you can answer (if you're still around 2+ years later!!) would mean my app does everything exactly as I wanted going into this. I am happy with things as they are but one more piece would be the icing on the cake! Let me explain what I am doing...

I have created an app meant to track the 3D filaments I have in my collection (many!). In it I have 3 views...well 4 if you include one in the "Menu Navigation" only. What I have under "Database of Filaments" is a list of all filaments I have now or have used in the past. In that table I have two slices, one to track filaments "On Hand" and another to track filaments "Needed". When I add a filament, I identify how many fall into each category or I just leave then at zero if I have run out and don't need to order more right now. 

I also have a UPC column in the first table that is scannable. I also have a third view at the bottom of the app beside "On Hand" and "Needed" that launches the "Add New Filament" action. When this last one is clicked, it now opens a form that only has the scannable UPC field. Thanks to your help to the OP, I now have it working (with the second table created plus all else described) so that when you scan or type in a UPC code, it checks to see if it already exists in the UPC column on the main table. If it is not already there. it correctly launches a full form to enter in all details for that filament like Brand, Name, Color, Type, etc and the UPC field in that new form is also prepopulated with the new UPC...cool.

If it DOES exist already, it loads the same form but with all fields filled in from the existing row of data from the main table. Also cool. Ultimately, however, the one final step I am trying to have it do is as follows...

IF the filament does not exist, act exactly as it does. Perfect!

IF it DOES exist, however, instead of pulling up a pre populated form that I can edit, what I would like it to do is simply add 1 to the On Hand field for that row. I scan the barcode in the app...if it does not exist I wind up adding all data and its now in the database but if I scan and it does exist, it means that I just got a new one in the mail and all I want the app to do is show that I have one more "On Hand" in the database/table/slice. 

Would you so kindly mind letting me know how to achieve this last step? I doubt it will take much!

Thanks for any help you can be and sorry for digging up an old post!

Mike

[WillowMobileSys]

Hi @LosEndos !  Yes. I'm still around!  

If I understand your ask correctly, when a filament exists in the database, after scanning the barcode you wish to "silently" add 1 to the database and NOT launch any Form.

I assume you ended up with an attached action to the From where the barcode is scanned.  And that action uses an IF() to decide which Form to navigate to.  Correct?

You'll need to change this to a Grouped action and use the Behaviors of the actions to determine which actions in the group are executed. Be aware that AppSheet has a restriction that when an action launches any view, it will be the last action executed - i.e. all other actions after it are ignored.  BUT...if the Behavior prevents that action from executing, it will skip the "navigation action" and continue to the others.

I will also assume that when a scanned filament does NOT exist, you enter the Form to add it AND there is some other action that will bump up the On Hand count.  That action will also be used in the Group.

So, first thing to do is to change the "navigation" action to ONLY execute when the filament row does NOT exist and go to a new Form. Move the In(Barcode, Products[Barcode]) portion to the action Behavior section.  Remove the IF() and leave only the LINKTOROW() expression.

Created the grouped action with you other actions in this order:

1)  Modified "navigation" action described just above.
2)  Action to bump the On Hand count

Attache this Grouped action to the Form Saved behavior of the barcode acnning Form.

The way this will work is when the barcode is saved and goes through the auto-save the newly attached Grouped action is fired.  IF the barcode does NOT exist then the modified "navigation" action is fired, launches the Form and does what it normally does.  The Grouped action is done.

If the barcode DOES exist, then the first action is skipped and the action to bump the count is executed.  No forms are launched and Group action is done.

I hope this helps!

[LosEndos]

Thanks for your response!!

Your understanding of what I am trying to accomplish is correct. Your second paragraph, however, is where I get just a bit lost in my interpretation.  When you ask if I "ended up with an attached action to the From where the barcode is scanned.  And that action uses an IF() to decide which Form to navigate to. ", I assume you are referring to the View I created under Primary Navigation (launching the "Add new Filament" is set as a View initially rather than an action that shows as an icon in another view). When I click this View link at the bottom, it is set to show the "ScannedUPC" from the scan table only and when that form/view is saved, under Behavior, Event Action is set to the Action "Add UPC to Table" which runs on the new scan table. This "Add UPC to Table" action runs the exact IF statement that you wrote for the OP.

So, right now, when the form launches via the Add Filament view icon at the bottom bar, the "Enter UPC" field which is tied to the ScannedUPC column from the new label is the only one that is visible. Once a UPC is scanned or manually entered and the Save button is clicked, the full form comes up and if the UPC is not in Table 1 already, all fields are blank except the UPC code just scanned but if it does exist, the same form comes up but with all fields pre populated. This could work just fine and just make the user scroll down to change the On Hand number, but I am just trying to set it so that the pre populated form never even comes up if the row/UPC already exists in Table 1 and the app just adds 1 to On Hand behind the scenes.

As an aside, I do have an Action also set to manually increase On Hand by 1 as well as another to increase Order by 1. These are set to show in the Prominent area above an existing filament. This way, if someone doesn't want to bother scanning, they can also go into the row/filament that shows in the main Database listing and in the Prominent area near the top, there are Edit, +1 On Hand, +1 Order, and Notes actions so a user can either just add 1 to either column , add notes to that filament, or edit it completely as there are about 10 fields for each filament. The +1 for each of On Hand and Order are simply setting a new amount to its respective column by setting the column with a ([On Hand] + 1) or ([Order] + 1) expression.

I am going to have to chew on the paragraph that states "You'll need to change this to a Grouped action and use the Behaviors of the actions to determine which actions in the group are executed. Be aware that AppSheet has a restriction that when an action launches any view, it will be the last action executed - i.e. all other actions after it are ignored.  BUT...if the Behavior prevents that action from executing, it will skip the "navigation action" and continue to the others." If you can elaborate on this a bit it might help.

In this sentence, "I will also assume that when a scanned filament does NOT exist, you enter the Form to add it AND there is some other action that will bump up the On Hand count.  That action will also be used in the Group.", the first part is true but their is no other action in this case to bump up On Hand count since the app won't know how many I might have gotten in. It is up to the user to complete the On Hand or Order fields manually for a new filament.

After that paragraph above, I am initially a little lost as I am not sure which form/view/action some of these refer to. I will play around with it until I get it to work based on what I think you mean above. The last two paragraphs make sense to me, no worries there.

Is there a way to "copy" the app so that I can leave it as it is currently and just play with a copy of it so that if I truly screw it up I can always delete it and go back to the original?

Thanks again ever so much. You are clearly very well versed in AppSheet!

[LosEndos]

I did figure out how to make a duplicate so I'm good there.

[Steve]

This is a common request. Have you tried searching?