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
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โ.
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.
I believe your current setup WILL work with just a couple adjustments. You havenโt shown the actual AppSheet tables so Iโll assume that Barcode is the Key Column for the Item Master List table.
First set the StockTake table as a Child table of the Item Master List. You do this by making sure that the Barcode column in your Stocktake table is a REF column to the Master List Table AND turning on the โIsPartOfโ property setting in the Barcode column of the Stocktake table.
Itโs not clear which column is expected to be the SUMMED amounts from the Stocktake table in your Item Master List table. Whichever column it is, make it a Virtual Column - if not present then add it - then set its expression to be the SUM of rows from the Stocktake table like this:
SUM(SELECT(Stocktake[Counted Quantity], [Barcode] = [_THISROW].[Barcode]))
As you add Stocktake rows with the same Barcode, the summed amount in the Item Master List table will automatically update upon each Sync.
Thank you for the reply. Regarding point 1 that you have advised me, I think there is some misunderstanding based on my previous post. Sorry about that.
The โItem Master Listโ table should be read only. No entries will be added or updated. It is a table for lookup purpose.
You donโt need to add new product items to this table?
Ok. I donโt know your system and why you would need the extra table. Simply apply what I mentioned to the Stocktake as the Parent table and the Another Stocktake as the child.
I do not need to add new product items into the โItem Master Listโ.
Hi, I have applied what you mentioned by connect the Stocktake as the Parent table and the Another Stocktake as the child. I am able to get what I want. Thank you.
However, I do have some questions.
This is the stocktake form connected to the Stocktake data source.
Use the Parent ID to populate those other fields (thatโs why itโs a REF column):
Stock ID => [Parent ID].[Stock ID]
If you want these other fields only INITIALIZED to the Parents value then place the above expression in the Initial Value property.
If you want it copied and not able to be changed, then place the expression in the App Formula property.
Thank you so much. Itโs working.
Sorry to trouble you again. I realised that in the child table the PLU ID is reflecting as what the parent table is showing on the app (refer to the picture sent previously). However, when I have submitted the form but the entry keyed in the child table sheet for โPLU IDโ is not reflecting correctly.
It actually is correct for a Parent/Child that is how REF columns work, they save the value of the Key of the row but will display the column set as the Label in that referenced table.
If you want the barcode to physically exist in the Another Stocktake table, then separate iot from the Parent reference. Add another column for the PLU ID and a de-reference expression to copy in the barcode value.
Next change the name of your Parent Row column, maybe just call it โStocktakeโ as an indication itโs a pointer to the Stocktake table row. Then HIDE this [Stocktake] column if you do not wish it to show.
thank you very much.
I have successfully achieved the function that I want.
User | Count |
---|---|
36 | |
33 | |
27 | |
23 | |
18 |