I have created a basic scanning app for my small business inventory management, the database is as follows:
Inventory - Row ID,SKU_ID,GI_Date,VendorCode,SP,OG_Qty,C_qty
Sales - RowID,SKU_ID,Post_Sale_Qty,Qty_Short?,ScanTime,SellingPrice,CustomerName,Contact,Remarks,User,Qty
Inbound - Product,SKU_ID,ProductID,InDate,LotID,Vendor,Qty,COstPrice,Moved to inventory?
The first step is creating the SKU_ID which happens by manual data entry in the Inbound sheet and concatenating the LOT ID, Vendor, ProductID, and some serial number.
Once I have the basic data in the Inbound sheet, I print QR codes for the SKUIDs and stick them on the items. Then starts the Inventory sheet filling, where an appsheet form is used and it takes SKU_ID by scanning QR code.
Q1. How do I change the ‘Moved to inventory’ flag(Y/N, default is N) in the Inbound sheet once I have made an entry for a particular SKU_ID in the inventory sheet?(this is basically to prevent and keep a track of what all from Inbound has been scanned and moved into inventory) i.e. I have stuck QR on product and successfully counted it under my inventory__ [for now I am using a countif in the inbound sheet referring the inventory google.sheet to change the flag to ‘Y’]
Once the items are in inventory, I may record a sale scanning the QR code
Q2. How do I change the ‘C_qty’(current quantity) in Inventory once a sale happens for a particular SKU_ID? I want the quantity in inventory to go down by 1, again I am currently using a countif in inventory referring to the sales sheet.
Please help, I have tried exploring a lot(weird formulas, bots, events), and I am yet to find how it’s done exactly