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

0 5 285
5 REPLIES 5

Steve
Participant V

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.

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?

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

Top Labels in this Space