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.

[WillowMobileSys]

lala_land:

Please advise me on how I can solve this.

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.

1. 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.

2. 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.

[lala_land]

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.

In the above picture, this is what I want to achieve. The part where I mentioned that I would like perform something similar to the “Add Multiple Photo” sample app. The “Another Stocktake (store all the items)” table will be the child table and reference to “Stocktake” table. However, when I do that, the stocktake form view type connected to “Stocktake” Table will need reflect the “Barcode” and “In House Barcode” columns.

The “Item Master List” table should be read only. No entries will be added or updated. It is a table for lookup purpose.

[WillowMobileSys]

lala_land:

The “Item Master List” table should be read only.

You don’t need to add new product items to this table?

lala_land:

In the above picture, this is what I want to achieve.

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.

[lala_land]

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.

This is the another stocktake as the child table from the Another Stocktake data source.

How can I allow pre filled like the Stock ID to the Stock ID shown on the child table?

[WillowMobileSys]

lala_land:

How can I allow pre filled like the Stock ID to the Stock ID shown on the child table?

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.

[lala_land]

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.

• Stocktake
  
  
• Another Stocktake (Child Table)
  
  
  
  The PLU ID is reflecting the UniqueID from the Stocktake Parent Table.

[WillowMobileSys]

lala_land:

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.

[lala_land]

thank you very much.
I have successfully achieved the function that I want.