Formula change

[billjohnson]

I am wanting to use the sample app
Inventory Management App v1.000132
The only thing I want to change is when I go to the “Sell” view and adjust the quantity… I want it to change the current stock number instead of subtracting it from the current stock number.
Ultimately, I want to use this for restaurant bar inventory. We do weekly inventory count. Where I want to scan bottle barcode + or - for the current inventory and then keep the add to inventory for when products come in from vendors.

[WillowMobileSys]

billjohnson:

I want it to change the current stock number instead of subtracting it from the current stock number.

What is the difference between “change” and “subtract”?

[billjohnson]

as it is… if I have current stock as 10 and I add 5 to stock, my new current stock is 15. this is good
If I go to “sell” view and input 8, then current stock shows 7. I want current stock to be 8.
I want what ever amount I input on the “sell” view to change/overwrite the current stock # despite what the previous amount was.
I will change the name of “sell” view to “count”

[billjohnson]

In Products Columns I have this formula for CURRENT STOCK=
SUM([Related Purchases][Quantity]) - SUM([Related Sales][Quantity]) + [Initial Stock]
I need it to be CURRENT STOCK=SUM([Related Purchases][Quantity])+ [Initial Stock]
until I do an inventory count
then I need it to be CURRENT STOCK=what ever quantity entered in Sell view
and be able to add purchases until next time I count inventory.
I tried to just have Sell view quantity replace initial stock… but the related purchases are still part on the math.
The reason subtracting does not work for me is it will require me doing the math my self.
i.e. I started with 40 bottles now I have 17, so I need to subtract 23 bottles in Sell View
This will double the time to do the task

[WillowMobileSys]

I would keep the Audit function separate - its own view and action, etc. This way you can later control WHO actually sees it.

So Sell function does the normal subtraction.

Audit function simply replaces the value.

[billjohnson]

Thank you for the advise.
Can you give me some insite on how to add an audit function?

[WillowMobileSys]

By Audit function I simply meant another View named “Audit”. But…somewhere in here I think you mentioned scanning labels? You can setup this Audit workflow to work like this:

Create a FORM against your Inventory table that has the UPC Code field, the Quantity On Hand field and any other identifying fields you want - Descrption, etc. I would include and “Update Date” that is automatically set for date. Make sure the UPC Code field is first.

Turn on Auto-Advance setting in the UX options. And turn on Auto-Save in the Form.

What ill happen when you launch the FORM is the Auto-Advance feature will automatically open the scan field to the scanner - ready to scan a UPC code. Once item is scanned, you’d be returned to the FORM and Auto-Advance to automatically advance “cursor” to the Quatity field ready for that input. Enter your amount and, if its the last field required for entry, Auto-Save should automatically save the entry AND re-open the FORM anew, advance to the scanner ready for the next item.

The resulting behavior for the Audit function is - Open, Scan, Enter -> Scan, Enter -> Scan,Enter.

A much more fluid and efficient way to update the Inventory counts!!

Let me know what you have questions on.

[billjohnson]

WillowMobileSystems:

Create a FORM against your Inventory table that has the UPC Code field

Not sure what “against” Means

[WillowMobileSys]

Just means you use the Inventory table as the source table in the FORM view.

[billjohnson]

Sorry, I am still having difficulty.I am using OneDrive. Do I need to create a separate sheet with audit info in OneDrive and create a new table in appsheet. I’ve tried adding a new table and chose products as source. Then added new view as suggested but when I input bar code it says “there is already a row with key 789”. So frustrated I can’t figure this out.
Inventory Management App v1.000132

[WillowMobileSys]

Ok, so I have realized that I have forgotten one important fact. In AppSheet, you cannot open an empty FORM, scan a barcode and then have that FORM switch to the row for that scanned item.

Instead, the FORM needs to be broken up into two - 1) performs the scanning and then navigates to a second with the identified row 2) The second that allows for row changes and Save.

Its been a while since I have created such a workflow so I am building an example of just this part and will post again once I have it completed to give you the details. Stay tuned!!

[billjohnson]

Thanks for your help again. I look forward to seeing the example. It is my hope that when the second form is opened it will have the current stock quantity and i can + or - from there.

[WillowMobileSys]

Here’s what you will want to do:

1. Create a new sheet to capture the scanned barcodes. I would use and ID key for cases where you might need to re-scan an item.
   

2. Add this sheet to AppSheet as a new table.

3. Set the ID column to not show
   

   

4. Turn on the “Advance Forms Automatically” setting. Note that this will affect ALL forms within the app but hey, you might just like it!!
   

   

5. Create the Audit FORM using the new Audit table as the datasource. Have it placed where ever you’d like in the app.
   

   

6. In the Audit view, Turn on the “Auto-Save” and “Auto-Reopen” settings.
   

7. Test that the when selecting Audit, it automatically opens the scanner. Scan an item. It should automatically save and then re-open the form allowing you to scan again.

8. Check the sheet that your scans are being saved.

Build this part and let me know how it goes and if you have any issues. In the meantime, I’ll put together a post on navigating to the second form to update the product count.

[WillowMobileSys]

Here is what to do to create the second FORM and then connect the two together to create the most efficient way of scanning and editing the inventory counts.

1. Create a Slice for the Products table with only the columns needed for editing the counts. I am including description and image as a way of confirming the intended object was scanned. But you can do this however you wish.
   

   

2. Create a Form for the Adjust view using the new Slice as the datasource.
   

   

3. In this Adjust view, turn on the “Auto-Save” feature and set the “Finish view” to the first view you created. This resets for your next scan.
   

   

4. In the Products table, set all of the informational columns on your Adjust view as view-only. I.e. set the editable when NOT the Adjust view.
   

   

5. Create an action that navigates to the second view using the LINKTOROW() function.
   

   

6. Go back to the first view, Audit view, and update the “Form Saved” property in the Behavior section setting it to the action you just created.
   

   

7. Test the Audit function again. This time when you scan an item it should go to the second form ready for you to enter/adjust the Count field. Note that if you tap on the Count field and edit the number and then tap Done, the Auto-Save kicks in, saves your changes and then will automatically navigate back to the first FORM view. HOWEVER, if you simply use the +/- buttons you will need to tap Save at the bottom.

8. Check the Products rows to confirm your updates are applied.

Give it a try and let me know how it goes!!!

Final note. This Audit function is basically to “adjust” the Initial Count field. But if you plan to use the Add and Sell functions that were originally in the sample app, you will need to change the Current Stock calculation to only grab the Add and Sell rows AFTER the Initial Count was adjusted. This will require capturing the Date of adjustment AND changing the calculation to pull the Add and Sell rows entered AFTER the Date of adjustment. If you intend to keep these features in your final app and need help with these changes, just ask.