Scanner, same barcode quantity update and other m

[Jorge78]

Hello, 

I work as a seller for a cosmetic brand, every week I am in a different multibrand Cosmetic store and everytime I make a sale I have to write it down on a notebook, because I do not do the check out and cannot have access to the computers to check everything, at the end of the week I have to report those sales.

Basically I  am creating an app to keep track of my sales and export it to a google sheets. I have it almost ready but I can't for the life of me, find the way for the scanner to let me scan the same item more than once. Is there a way to get it to add up or even just add a line?

Is there a way to add a new page t the spread sheet for every new week?

Lastly whenever I scan, on the column where the name of the product is supposed to show I i get the code again.

Any help and guide would be greatly appreciated.

Thank you in advance for your time. 

[WillowMobileSys · Accepted solution]

The [CODIGO] column is the barcode column? 

This column is set as the key value.  Key values must be unique.  With this barcode column set as the Key in the Productos and Ventas tables, you will only be able to ever have a single row with the barcode value in each of these tables.  this might work in the PRODUCTOS table but, as you said you want to record several sales on the same barcode in the VENTAS table.  You currently cannot do that with the barcode as the Key.

To fix this issue in the VENTAS table you need to:

1. add another column, maybe named Sales ID, and set it as the Key column.  You can use the UNIQUEID() function to populate the value.
2. Change the [CODIGO] column (in the VENATAS table) to the type of Ref with the table source as PRODUCTOS.

These two changes should make everything else work as expected.

[WillowMobileSys]

First, I strongly recommend that you use the Google sheet ONLY as a datasource into the app.  Trying to use that same sheet as a human readable report of the data leads you do things that make maintaining and even implementing in the app more difficult.  Instead, create automation to generate the reports you need as you need them.  Alternatively, create a separate human readable Google sheet that pulls in the data using an IMPORTRANGE() function.

---

@Jorge78 wrote:

I can't for the life of me, find the way for the scanner to let me scan the same item more than once. 

---

Can you explain the problem you are facing here?  I assume you are referring to scanning of barcodes.  There isn't any reason why you can't scan a barcode multiple times.  Maybe you are facing issue where the app itself is not allowing the scanned value?  If so, make sure your barcode is not set as the row key.   

---

@Jorge78 wrote:

Is there a way to get it to add up or even just add a line?

---

Yes!  There is a SUM() function into which you can supply a list of values from a SELECT() to be summed up.  The comment "just add a line" can refer to a few things but this is also possible and will depend a lot on what you have currently built.

---

@Jorge78 wrote:

Is there a way to add a new page t the spread sheet for every new week?

---

See my first comment at the top.

---

@Jorge78 wrote:

Lastly whenever I scan, on the column where the name of the product is supposed to show I i get the code again.

---

Scanning a barcode will always return the associated code.  Don't use it as a row key, especially if you need to scan it for additional rows.  In order to then return the associated Product with the barcode, you will want to have a another column for the Product and then perform a Lookup to assign the Product based on the scanned barcode.

[Jorge78]

Hi @WillowMobileSys ,

First of all, thank you so much for your reply, secondly please excuse my lack of information. I'll break it down more detailed:

I made a google sheet with three pages:

• Page 1 named Sales, where the scanned numbers are saved. (Here is where i have the 2 problems. 1 Barcode number is saved in the code and the product column. I can't figure out how to ref to Products page to check for that number and place the name of the product in that column and 2 I can't scan the same item twice. I don't care if it is adding up or adding a row either way works fine.
• Page 2 Named Products which is where the barcode numbers and names of the products are listed. No problem here I've made sure to name the columns and the products correctly.
• Page 3 named Total which I use as a fast check in the app to see how much I have sold so far. But surely I will do like you proposed with the other document with IMPORTRANGE() function. Thank you for the advice. 

As you can see in the screen captures my app data colums, I really don't know where the mistake is that i am getting barcode number where i should get product name.<(sorry i didn't explain my self correctly, yes I know when you scan a barcode you get a number but I can't get it to show me the name instead I get the code again on the product column.

And also how to tell it to let me scan an item more than once, since during the day or week I will sell it more than once for sure. Either add up or add a row.

Again thank you so much for your time and effort in helping me.

[WillowMobileSys · Accepted solution]

The [CODIGO] column is the barcode column? 

This column is set as the key value.  Key values must be unique.  With this barcode column set as the Key in the Productos and Ventas tables, you will only be able to ever have a single row with the barcode value in each of these tables.  this might work in the PRODUCTOS table but, as you said you want to record several sales on the same barcode in the VENTAS table.  You currently cannot do that with the barcode as the Key.

To fix this issue in the VENTAS table you need to:

1. add another column, maybe named Sales ID, and set it as the Key column.  You can use the UNIQUEID() function to populate the value.
2. Change the [CODIGO] column (in the VENATAS table) to the type of Ref with the table source as PRODUCTOS.

These two changes should make everything else work as expected.

[Jorge78]

I have done what you suggested, but now when I click the scan button I get this message. Where before I got the default 111223333

And if I click inside the code box I get a list of all the products.

Here is a screenshot of the end of both tables

[WillowMobileSys]

Yes.  Because the CODIGO column is a Ref column sourced from the Products table, the default scan value of "111223333" is not considered valid because it doesn't appear in the Products list.  You can add a Products row and assign it the barcode value of "111223333" to make it valid again as a test value for the emulator, if you wish.

CODIGO is being treated like any Ref column only it also has the capability of using the barcode scanner to select items.  Because it is a Ref column you will get the default dropdown list.   This does provide the ability of the user to select the value from the list rather than scanning - which is good if a barcode become unscannable for some reason.

[Jorge78]

Well, something is off. When trying the app on the phone  when I scan the barcode, in the box instead of getting the barcode number I get the name the of the product. Like this:

And in the google sheets in the column where the product name is supposed to go I get the barcode like so:

And Finally I have the columns in the tables like this:

I just can't figure out how to get barcode in the box and product name in the product column.

[WillowMobileSys · Accepted solution]

It is working as it is intended.  Ref columns always store as the data the row key. 

The reason you see the Product name is because of the "Label" setting on the Product table.  Label simply indicates which column represents the label for the row. Change it to the "CODIGO" and you will instead see, in the scanning box, the CODIGO barcode value.  Once you understand what Label is doing then you can change it to whatever you wish.

If in your Producto column in the VENTAS table, you only wish to store the Product name, then change the column to Text type and change the App Formula to:

[CODIGO].[Producto]

[Jorge78]

Dear @WillowMobileSys, 

I really can't thank you enough for your help and your patience. Thank you so much for your effort.