Reference location and item number

[Blkfirephoenix]

Hello.

I have a problem with Ref tables. I store inventory of the same item in multiple locations.

So I am trying to create a "Picking" and "Put away" tabs and for my Inventory Management app where you can scan the item number and also the location to add or subtract a qty. However, when I tried to ref the item number AND location from the 2 tabs to the Current Qty tab, only the item number shows. when you click on Location to select a location, it shows item numbers. I want it to show locations for that item number.

Can anyone help with this?

[WillowMobileSys]

In a REF column, AppSheet stores ONLY a pointer to the row in the referenced table.  That pointer is simply the row key.  For DISPLAY purposes only, AppSheet uses the reference table column set as the "Label" to show a value where ever the REF is shown such as in a table view or in a drop down list.

So for example, let's say you have an Inventory row as:

• [ID] column as the row key with a value of "a1b2c3d4"
• [Item Name] column set as the "Label' column with value of "Some Product"

In some other table you have a REF column to the Inventory table named [Product] that shows a dropdown list of Inventory rows.  For the example row above, the dropdown list will display "Some Product" as the visible value.

BUT when you select this row from the dropdown and save, the actual value saved into the [Product] column is "a1b2c3d4" - the row key.

Now this [Product] columns acts as a pointer to allow you to select any other column from the Inventory table using dot notation like so:

[Product].[Location]
OR
[Product].[Quantity]

This is called dereferencing and you can then use these dereferenced values in calculations or use them to copy details from the referenced Inventory row into columns of the current table.  

Please refer to this article for more details:

References between Tables

[Blkfirephoenix]

Sorry for my ignorance. I am new to this app developing. 

Take this practice sheet for example:
https://docs.google.com/spreadsheets/d/1iNcgxQ1nO_eSVDe_pZw0CUuwfpvbHcFRrsbii7xx5sk/edit?usp=sharing

I want an employee to be able to put an item away in a location and scan it to that location with the qty amount. And for it to update in the Item Master of the total qty.

Same with Picking. If an employee picks an item, That employee would need to pick it from a certain location. So I would need them to scan the Location of where they are picking, then the item number that is being picked. 

Reason behind this, is that there will be multiple locations in the warehouse that a item is stored in. I want to be able to track how much inventory for a certain item is in a location.

I hope this makes sense

[WillowMobileSys]

---

@Blkfirephoenix wrote:

Sorry for my ignorance. I am new to this app developing. 

---

Not a problem.  That is why we have the Commmunity.  Please feel comfortable to post any questions you have.  But don't forget about the knowledge base.  There are many articles there that can help even more.

---

...put an item away in a location and scan it to that location with the qty amount.
...
... I would need them to scan the Location of where they are picking, then the item number that is being picked. 

---

I am gathering by these comments that you want to have Barcodes, QR codes, NFC tags or a combination.  Most mobile phones can scan for all of these.  Do you yet know precisely how you want users to enter these details?

It seems you either need to:

1)  Scan twice - one for Location and another for Product.

OR

2)  Generate codes that represent both Location and Product.  This can get cumbersome

[Blkfirephoenix]

Hello WillowMobileSys

Thank you again for your reply and help. A couple of points:

Below is a screenshot of the app on the right. When an associate clicks on the "Picking" menu, it will show the following: Current Timestamp, SprID (item number) which they could scan with their NEXUS Scanner or mobile phone (and also is showing a weird number in the field that is not my item numbers), Qty Picked, and Location (which they can scan. 

But the Location is not working. It only accepts the item numbers in the field "location" 

What I am trying to do is be able to select which location I am picking the item from. Because if an item, for example WKUK1069, is being stored in 3 locations (100 units in Location B130102 and 100 units in B250103 and 100 units in B350102) then If I am not able to select a location in the Picking Menu, then the qty picked would deduct from a random location. 

I hope I'm communicating this right. 

I have spent hours looking online for training videos for the type of app I am creating but all the Inventory Management videos using Appsheet only show the process WITHOUT locations. 

[WillowMobileSys]

Ok, I think I understand better now.  I can provide better analysis if I were to see the barcode columns and how they are defined.  Below is what I think is going on.

The reason for the weird number and the yellow warning Icon in the SprID column is a mismatch between the column REF value (SprID is defined as REF?) and the scanned value. 

You probably have a Valid_IF expression implemented to make sure the scanned values are valid.  BUT...REF columns require the row key and if that row key is NOT the barcode, then no match can be made.

Location seems to be suffering a similar issue.

How to fix...

I DO NOT recommend making barcodes row key columns.  Barcodes can change over time and if it is a row key the data structure depending on it will be affected and cause issues.

Instead, I recommend adding a Barcode scanning column as a lookup value - Scanned Barcode.  Define it as Text, make it scannable and if you prefer validating the scanned barcode, then introduce in the Valid_If an expression to provide the list of valid barcodes.  This will be visible only until a valid barcode is scanned.

Keep the SprID column as Ref with its Valid_If expression. Change the SprID column to NOT scannable and hide it until a valid barcode is scanned.  Use the Initial Value property to assign the matching SprID row with an expression like this (only an example, you will need to change it to suit your use case):

SELECT(Spr ID Table[SprID], [Barcode] = [_THISROW].[SCanned Barcode])

 How do you know in other columns that a valid barcode has been scanned?  ISNOTBLANK([Scanned Barcode])

Location...

Since it seems that Location is a separate scanned barcode value, you will need to implement the same logic as above for the SprID column.