Table Reference

[lala_land]

Hi, I have some issues when trying to shape the function to replace the manual tasks that my staff have been performing. Firstly, let me share what I want to shape in my app and the issues that I encounter.

What I want to shape in my app:
During stocktake, the staff will need to scan the product barcode to count the items. However, each item is not only place at one location but multiple. For example, Product A will be placed near the entrance and near the open top counter in the retail outlet.

Currently, I have some sheets called “Item Master List” and “Stocktake”.

• Item Master List
  

  

• Stocktake
  

  
  
  I had created the stocktake view (form view type) connected to the data source called “Stocktake”. Once I scan the product barcode on the stocktake view, some information will lookup from data stored in the “Item Master List” sheet. Those information will be “Balanced Quantity” and “Item Description” will look up to the “Item Master List” sheet data based on the product barcode scanned.

The problem comes as the staff will be performing stocktake for different areas. My above solution will not work. It will not work as if the staff were to perform stocktake of same product from different location, meaning the number of rows will be created based on the number of location that the staff have performed the stocktake for the same product. Then, the counted quantity will forever do not tally with the balanced stock. As I am supposed to sum the “Counted Quantity” for the same product to make that work.

I had tried to create another table for the staff to add multiple entry for stocktake of the same product. I have made a table reference using the new table and the “Stocktake” table. However, it is not working as I still need to lookup back to the “Item Master List” for information.
I have take reference from the sample app “Add multiple photos”.

I wanted to do something similar to the “Add multiple photo” app which allows me to add multiple entries for the same product and I can do my summation of “Counted Quantity” from the multiple entries that is keyed in by the staff.

Please advise me on how I can solve this. Urgent help needed. Will really appreciate if you can render some help. Please let me know if you need further clarification.