Same barcode but different suppliers

I have a simple inventory app with products, stock in and stock out. My products are grouped by supplier. For some of our products we will have multiple of the same thing with the same barcode but from different suppliers, I am unable to add the same product but allocate a different supplier as the barcode already exists. I would also like for the ‘stock in’ products to go to the supplier group that is entered in the form. How would I go about implementing this? Thanks in advance

If the barcode value is currently your key column value, you’ll have to change your key column to a different column that isn’t (only) based on the barcode value.

4 Likes

Thank you for the reply, however I would like the product name and supplier to both be keys, appsheet made a concatenate virtual column for me when i selected both as keys but I couldn’t get this to work when I replaced “Product code” in my formulas with the concatenate column. Also I thought I would need to change the Supplier column in my IN and OUT tables to Ref the Products table however when I do this it brings up the list of Product codes rather than Suppliers. I’m not sure how to get the formulas for stock in,out, and for current stock to rely on both product code and the supplier. These are my current formulas that just rely on the product code.
Related OUTs: REF_ROWS(“OUT”, “PRODUCT CODE”)
Related INs: REF_ROWS(“IN”, “PRODUCT CODE”)
Current stock: SUM(
SELECT(
In[Quantity],
[PRODUCT CODE] = [_THISROW].[PRODUCT CODE]
)) -
SUM(
SELECT(
Out[Quantity],
[PRODUCT CODE] = [_THISROW].[PRODUCT CODE]
)) +
[Initial Stock]

I am new to all of this so any help is appreciated. Thanks

Yep, that’s what you should do.

Yep, that’s what you need.

Why did you try?

Why?

1 Like

Adding a new product is working okay, I can’t add any new products with the existing product code and supplier which is what I want. With the formulas as they are when adding stock IN or OUT there is the same product code coming up for each of the suppliers, for example I have product code 08417 with 2 different suppliers and the code 08417 is showing up twice in the list, whereas I would like just 1 to appear and then when I enter the supplier it will allocate it to the correct one. I’m not sure if this is a formula issue or not. At the moment the current stock formula isn’t working as when I add stock IN or OUT the product code on my spreadsheet is showing as the concatenate rather than just the product code . I could change it so that I have to chose from the concatenate list when adding or removing stock but I still want to be able to use the barcode scanner which I wouldn’t be able to do as the scan would show just the code rather than the concatenate? This is why I tried changing the formulas to have the concatenate in them, like I said I am new to appsheet so I was just trying things I thought might work.

I thought I would need to do this so that when I add stock IN or OUT so I am choosing from the already existing suppliers, leaving it as text is okay it just means information could be entered incorrectly. Would I not want to Ref my suppliers column in my products table so that the supplier can’t be entered incorrectly?

Thanks

@Steve were you able to take a look at the above? Thanks