Need Help Writing An Expression to Autopopulate Part Number Based on Barcode Scan Results

[vuworld]

Hello all, I need some assistance. I was originally under the impression that scanning a product barcode would automatically populate the part number into the part number field based on the barcode scan. This is not happening. I'm assuming I need to write an expression into the field to tell it to pull that information for me but I can't find how to do this online. Here is what I would like:

-an expression that would take the results of the barcode scan, look that data up in my "all products information" table, find the part number that barcode is associated with, and then return that part number automatically into the other two tables.

It doesn't make any sense for me to have the barcode scanning set up if I am then still going to have to select the part number in the drop down list I have set up, which is what's happening currently. I tried to make a lookup expression for this, however that's not working and I may have written it incorrectly. Any ideas?

[Joseph_Seddik]

https://help.appsheet.com/en/articles/1090811-dereference-expressions 

[vuworld]

Thank you. This is helpful, however I already have the box set up as a reference between sheets in order to have the inventory on hand calculate correctly based on increases and decreases to the part number. Here is (hopefully) a clearer, more detailed rundown of what I want to do:

- I want to have barcode scanning enabled

- I want to have a part number box underneath that that would populate the part number based on the barcode scan

- In the same part number box, I want to also have a dropdown arrow that would allow me to choose the part number from the list

In summary, I want the part number to always populate if there has been a barcode scan, however I want to have a secondary option in that same part number box to allow for a drop-down menu so that my warehouse team can EITHER scan OR choose the part number by hand. The reason for this is that it's going to take us a long time to get the barcodes into the system, and in the meantime I need them to do an inventory so it would be faster to choose the part number for now.

After playing around with the app, it seems that I can only set up a dereference, or have it set up as a drop down list, not both. Is there a way to do this?

[Joseph_Seddik]

You are probably using the dereference expression in the App formula field.

Instead, use the expression in the Initial value  field of your column, so that when a barcode is scanned the corresponding part number appears, and use Suggested values and Valid if fields to list all possible part numbers. 

[vuworld]

Ok, so I put the dereference expression in the initial value field and here's the expression I created in the suggested values field: 

SELECT(Product[Product Number], ([Product Barcode]=[_THISROW].[Product Barcode])).

I think this will work, however I'm having trouble figuring out the right expression to put in the Valid If field. Would you have any suggestions for this based on the expression I created? I'm looking through all the articles I can find and I may find the answer myself, but you might know based on looking at my expression.

[Joseph_Seddik]

In both fields, just put: Product[Product Number]

You want to list all possible Product Numbers, rather than selecting a particular one. 

[vuworld]

I'm not sure I'm understanding. I want it to suggest the part number based on the barcode scan if there is one. I want the option of choosing it in the case I don't do a scan, but I want it to suggest the part number if there is a scan. There is one part number for each product, with a total of 1500 individual products.

[vuworld]

Each of the 1500 individual products have a single part number and a single barcode associated with it. So wouldn't I want it to suggest the part number for me? That's what I'm attempting to do.

[Joseph_Seddik]

Are you having difficulties understanding the steps I've proposed? In that case, sorry, please tell me which step you don't understand and I'll do my best to explain it better. 

Are you trying to anticipate whether the solution would work? 🙂 In that case, please implement it first then tell me how the results differ from what you are trying to achieve. Thanks.

[vuworld]

My apologies, I'm new to coding-adjacent knowledge. No, I'm not having trouble understanding the steps. You laid everything out clearly and have been very helpful. I'm just concerned by your statement that "You want to list all possible Product Numbers, rather than selecting a particular one." I do want it to select a particular part number, based on the scan. I don't want it to list all possible part numbers once I do the scan. I want it to suggest it for me. But I'll go ahead and test out your steps and see if that works. Thank you!

[Joseph_Seddik]

Hey take it easy my friend 🙂I'll explain better..

Here how it goes:

• The initial value formula will compute the corresponding Part number and display its value. 
• If no scan was performed then the field will be empty.  
• Because the formula to compute the Part Number was put in Initial Value, not App Formula, users will be able to put other values in this field.   
• Suggested Values and Valid If fields will list all possible Part Numbers value, from your 1500  product list, if the user decides to click on field, whether or not a Part Number was found.  The user will also be prevented from putting arbitrary part numbers that do not exist in your list.   

If each of your products has its own distinct barcode, it can be a good idea that the barcode column is the key column of your Products table, and in your current table, "barcode" column should be of type Ref, pointing to Products table.

In this case you can use a simple dereference expression in the Initial Value field that is:

[barcode].[Product Number]

instead of SELECT().

For the Suggested values and Valid if fields, the list of all possible product numbers is:

Product[Product Number]

[vuworld]

Great, thank you very much for all your help! I'll try these steps and see if it does what I need it to do. You have been very helpful, much appreciated! 🙂