Create Prefilled fields with Checkbox and Barcode Scanner

[dhaval]

I'm trying to create an Appsheet Form/App for recording sales orders. 
1. The app user will enter an order ID from an invoice.

2. Post entering the order ID I want the app to display Items that are being sold under that particular order ID. 

3. The Items field would be prefilled and should have a barcode scanning option and a checkbox. So, when a user scans the particular item's barcode the checkbox would be marked ✅

For instance, Let's say Order No. 1111 has Item A, Item B, and Item C. So, When a user enters Order No. 1111, all prefilled values pop up i.e Item A, Item B, and Item C. Now, the user will scan those item's barcode and post scanning the items would marked ✅

Can anyone suggest a workaround for this?

[Steve]

Define App formulas and Initial values - AppSheet Help

[dhaval]

@Steve I already have predefined values in Google Sheets. Let me explain what I'm trying to accomplish. 

The main idea behind the app is to make sure the warehouse executive is packing the right items by scanning the barcode. The google sheet would already be populated with all the data that is Order ID and Order details. Once the user scans the order ID, he'll see the list of items that he has to pack. For instance, if the user scans/enters Order no. 1, he'll see the list of items that are being sold under order no.1. Let's say Item A, item B, and item C are being sold under Order no.1. Now, I want these items field to be barcode scannable and post scanning the item, that specific should be marked ✅. This way the user can ensure that he is packing the correct item. I'm attaching the screenshots of google sheets and the app I'm trying to create. 
Using Sheet - AppSheet_Order_ID, I have created the first view (refer to the image)

the other sheet - AppSheet_Order_Details has all the data of items and barcodes that are sold under a specific order ID. 

I know I can create an inline view by setting order_Id as a key column but this will only display the order items. And Since the key columns have duplicate values I'm unable to expand each item individually from the inline table view. And setting any other column such as Identifier as Key Column won't display all the items in an inline view. 

[dhaval]

@Steve @WillowMobileSys this is what I'm trying to accomplish. 

[WillowMobileSys]

---

@dhaval wrote:

I know I can create an inline view by setting order_Id as a key column

---

Just to reiterate from the other post, this statement above is incorrect.  The order_Id from the Parent table is only referenced in the child.  It is not, cannot, be set as the key in the Child table.

Once you fix the Child table key column, as noted in your other post, the scanning part should be relatively straight-forward.

I assume you are scanning and marking the row for order fulfillment?

It would work like this:

1. Tap button to scan and open scanner.
2. After barcode is scanned, perform validation by looking up the barcode in a Products table and verify the barcode matches the Product on the order.
3. If invalid then present message informing user of wrong barcode.
4. Mark column as matched. There are no checkboxes in AppSheet but rather Yes/No columns.

NOTE: The above assumes you have a Products table with the various products and a Barcode column you can match the scanned barcode to.

[dhaval]

@WillowMobileSys Thanks! Changing the key column of the child table has resolved the issue of row selection. However, for the other part that is in Order Details, I have the following prefilled values,

Order_ID,
Identifier,
Barcode_Product (This column shows the products on that Order_ID), - Check the

image below,



These prefilled values are coming from Google Sheet already populated with order details, ⬇️





But, I was thinking if it's possible to apply barcode scanning to the prefilled field that is "Barcode_Product". Once the user scans the barcode as per prefilled value, it should change its format maybe it will turn to bold from greyed out or something like that

[WillowMobileSys]

First, in your Form image,  all of the fields are showing as non-editable.  Some of them, such as Identifier, should be non-editable.  Depending on your editing design, others maybe should be editable and you will want to figure out why they are non-editable.

Barcode scanning works differently than you seem to suggest.  If you enable scanning, and scan a new code, that field will be updated with the new value.  And you may be able to use the properties [_THISROW_BEFORE] and [_THISROW_AFTER] to compare the values to determine if they match.  BUT, if a user was to cancel the scanning and return to the Form, the original value remains and the app has no way to determine if that remaining value was the original or was the newly scanned value.  So comparing before/after doesn't really work.  The best way is to add a new column that allows for scanning and then compare the columns to determine a match or not.

Before, going further with suggestions, it would be helpful to know the goal/purpose of scanning.  I suggested it might be for order fulfillment but you haven't confirmed that yet.  I see references to Shopify and Amazon so maybe it's to confirm correctness of Product from those services?  Please clarify.

[dhaval]

So, let me start with the reason for creating the app. 
There are multiple SKUs of the same category that we sell online on e-commerce websites. So, post receiving orders, while packing chances are, the warehouse executive might pack the incorrect item/items in oversight. To overcome this slip-up, I want to create an app that helps the packer to ensure that he's packing the right items. So, at the start of the day, the google sheet linked to the app would be populated with order-Id and the order details. The order details sheet - Child Table (as shown previously) would contain the following details,
Order_Id,
Marketplace,
Product_Barcode,
Product_Name,
Scan_Barcode (editable/scannable field)
Status 

Parent table - Order_Id would just contain Order ID and date_time field. 

the user will first scan/enter the order ID in the parent table/form and the order details would pop up from the child table. 


Now, as per the products shown in the order details table/view, he'll pick up the product and scan the barcode. If the scans the correct product barcode, the "Status" field will show "✅". This way he can ensure that he is picked up the correct product to be packed. 

Now, I understand he can get all the information from the invoice i.e products and qty but like I said before he could pack the wrong products in oversight. So, introducing the app for recording the packing items as per order ID will help him to ensure the right items are dispatched. 

As per your suggestions, I have added a separate barcode scannable field and status field. The reason why the other fields are not editable or read-only is that I'm using formulas in the spreadsheet. So, AppSheet has marked these fields as read-only. To get more clarity check the image below, 

Below are the images of the spreadsheets used in creating this App,

The above explanation is something that I have actually created for now. 

But, I want to create something like this, more seamless and with less flab,⬇️

I know it's not feasible to create exactly like this but if you suggest a workaround to create something similar. 

[WillowMobileSys]

I think adding the scannable column, which you indicated you've already done, and the status Yes/No column is really the best solution.

I would implement it such that once scanned and if match is successful then hide the scannable column and set the status column to the appropriate "matched" value.

If they don't match, then you can present a message to the user to check the  order.

You can also setup the process so that when a Product row is selected, the associated Form goes straight to the barcode scanner eliminating additional taps.  This is done by turning on the auto-advance feature.  If the scannable column is placed at the top AND an appropriate "Reset on edit" is applied, the barcode scanner will open automatically when the Product row is selected.  IF implemented well, the user will never see the scannable column on successful Product matches. The user process would be - 1) Select Product Row,  2)Scan item barcode and then 3) tap Save.  Quick and easy.

Beware that the auto-advance setting affects the entire app so all Forms could possibly auto-advance depending on how they are implemented.  

[dhaval]

Thanks! @WillowMobileSys that's something I'd like to implement. But, I'm stuck because of two things. 
1. I have toggled the "Auto Advance" on and moved the scannable barcode column to the top. But, the barcode scanner still won't open automatically. Also, I have toggled "Reset on Edit" in the barcode field. 

2. As shown in the image below, If the order ID has multiple rows because of multiple items, I want the submit button in the inline view to work only if the status of all the rows is ✅. If this is not enabled the user might scan the first two or any one of the items and submit the form and he'd think that he has scanned all the products.