Scanning Barcodes to my Data table in excel

[Jeanlem]

Hi hopping to find the write formula, I am able to scan and see the product I would like to scan and be able to pull up my item from the table data and add a 1 to say that I have stock once its open at the moment I can see all my inventory sku's on my app just missing the formula to make it all work. 

thank you, 

[v-bofeng-msft · Accepted solution]

HI @Jeanlem：

Could you tell me:

Whether the barcode scanner is a web barcode scanner or a barcode scanner?

Do you want to rely on a barcode scanner to find records in an excel file?

Do you want to increase the inventory in this record by 1 when the scan is successful?

What‘s the data struct of the table that in the excel file? Field name\Datatype\ A detailed description? Screenshots are better.

I assume the barcode scanner is a barcode scanner.

I’ve made a test for your reference.

Here is my table.

ID( type: text  Description: Bar code)

Inventory（type: General Description:in stock）

Step1:add a barcodescanner(BarcodeScanner1) control and set it’s OnScan property to:

Set(
    var,
    LookUp(
        Table1,                 /*Table1 is the name of my table */
        'ID' = BarcodeScanner1.Value
    ).Inventory + 1
);                              /*set a variable ‘Var’ to update the value of ‘Inventory’ */
Patch(
    Table1,
    LookUp(
        Table1,
        'ID' = BarcodeScanner1.Value
    ),
    {Inventory: var}                /*Patch the value of ‘Inventory’ */
)

Step2:add a form and set it’s datasource property to:

Table1                         /*Table1 is the name of my table */

Set it’s items property to:

LookUp(Table1,'ID' = BarcodeScanner1.Value)     /*Find the associated record of the barcode */

In addition ,if the barcode scanner is a web barcode scanner.

First, you need to enable this feature(file-settings-advanced setings-web barcode scanner)

Second, since there is no OnScan property in this control, you need to add a button and set it’s onselect property to :

Set(
    var,
    LookUp(
        Table1,                 /*Table1 is the name of my table */
        'ID' = BarcodeScanner1.Text
    ).Inventory + 1
);                              /*set a variable ‘Var’ to update the value of ‘Inventory’ */
Patch(
    Table1,
    LookUp(
        Table1,
        'ID' = BarcodeScanner1.Text
    ),
    {Inventory: var}                /*Patch the value of ‘Inventory’ */
)

Since you use barcode-scanner ,I think this link will help you a lot:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/controls/control-new-barcode-scanner

Best Regards,

Bof

[Jeanlem]

Hi 

thank you this work great my other issue is that I have 35 locations in with I need to collect each one there  own inventory know that I have set up the scanning to my inventory and adding inventory how do I add a filter for each store or location that when I arrive to one location I can scan there inventory and go to another location but have them separated 

here is a screen shot of my view so far do I need a new page or I can do this from the first view. 

let me know thank you, 

[v-bofeng-msft]

Hi @Jeanlem :

Maybe you can try this code:

Set(

    var,

    LookUp(

        Table1,                 /*Table1 is the name of my table */

        'ID' = BarcodeScanner1.Text&&locations=XXX  /* Edit your filtering rules here*/

    ).Inventory + 1

);                              /*set a variable ‘Var’ to update the value of ‘Inventory’ */

Patch(

    Table1,

    LookUp(

        Table1,

        'ID' = BarcodeScanner1.Text&&locations=XXX  /*Edit your filtering rules here */

    ),

    {Inventory: var}                /*Patch the value of ‘Inventory’ */

)

Best Regards,

Bof

[Jeanlem]

thank for your reply like I mentioned I am very new to this here is what I needed 

- how to add filtering box to my app

- do I have to add a formula for the drop down box 

- do I have to add a new table data with my customers name in it

- what would be the new formula for my scan box not sure what you meant for this part (Edit your filtering rules here)

thank you, 

[v-bofeng-msft]

Hi @Jeanlem :

Could you tell me:

Do you want to add a control for the user to choose the locations he needs?

What is the data source‘s data structure?(field name,data type,effect)(screenshots are better.)

What function do you need(in detial)?Filter by which field?What is this field‘s data type?

If you can't wait to explore on your own, you can refer to these following links:

Filter, Search, and LookUp functions in Power Apps:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-filter-lookup

Drop down control in Power Apps:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/controls/control-drop-down

Best Regards,

Bof

[Jeanlem]

Hi here are the following answers 

- Do you want to add a control for the user to choose the locations he needs? yes

- What function do you need(in detail)?Filter by which field?What is this field‘s data type?  I added a screen shot of my data and screen shot of my ios app 

So I would need all the formulas from the start the scan button and how many drop downs button do I need or any galleries features to add 

Thank you for your help so far !

Stay safe ! 

[Ambern1023]

I also am looking to create an inventory shet (very basic) for our company apparel.

We have 
Maroon Longsleeve, shortsleeve, pull over hoodies
HI VIS Shortsleeve, longsleeve, zip up hoodie, jacket, Vest
Gloves (various styles but lets call them Orange, Red, Waterproof, Nitrile)
Boots(just diff sizes)
thats the majority of our apparel

Id like to create a Barcode or QR code that is placed on the drawer our items are held in (they are for employee use & some are giveaways to customers)

So id like an easy ways to track the style of shirt (maybe with size) or just style is fine 
and keep an inventory count
So place  a barcode or QR code on the drawer and then scan it to remove ____ from inventory/add ___ into the inventory
it would be nice to have a column that states when removed who it went to / when added where it was purchased from(were still deciding our vendors based off cost)

we are a very small family business with 10 employees or so. but have lots of inventory (we dont sell products we are an industrial cleaning company) but we have "job supplies we bill customers for etc

whats my best method here? I want a barcode or QR and make tracking simple. but I dont know alot of excel functions/formulas