Welcome back to my How to Make + Function + App series! I received so many comments on the Project Management and Property Management posts that I decided to continue with other use cases in this series.
Determining which Functions to focus on can be tricky so I took a look at AppSheet’s Sample App page and Support page. By the way, AppSheet’s Support Page is the perfect place to search for AppSheet documentation, how-to apps and our responses to all kinds of app-making questions. Here is what I found:
So I made an inventory app that (I hope) addresses some of your most frequently asked questions around using bar codes and how to create and apply restocking levels. Feel free to copy this app, customize it and make it your own. And don’t forget to leave your comments below—either for this post or the post series!
Use case and target users
This app was made for operation managers who want to keep track of inventory with barcode scanners to ensure they have enough stock for customers.
The app enables operation managers to do the following:
Without further ado, here is how I built the app.
Step 1: Organize your data and make an app
First, make sure that your data is organized in a meaningful way so that your app (next step) can understand how to present that data. For example, in Google Sheets (check out other data sources AppSheet supports here), I have three sheets that organize different sets of data:
Next, convert your data in Google Sheets to an AppSheet mobile app (see video 1 in the property management app). Now, the above three sheets become three views of data in your app. You can change view names to whatever makes more sense to you. I call my views Product List, Sell, and Add Stock.
Step 2: Record stock in and stock out with a barcode scanner—the camera on your phone
AppSheet can use the camera on your mobile device to capture barcoded data. To do this: Mark Product Barcode column (Data --> Columns in App Editor) in both the Purchases and Sales sheets as searchable and scannable. Your app is now ready to record any inventory movement, whether it is a stock in or stock out. All you have to do is to tap on the barcode scanner button (under Add Stock or Sell view) and scan the item!
Keep in mind that if an item is new and doesn’t have a product barcode already stored in your Product sheet, you will have to capture its barcode first so that in can then be stored on the Product sheet. Instead of simply scanning it, you will need to follow these three steps:
Step 3: Calculate the real time inventory level
The calculation formula is pretty simple. But while the Initial Stock field is static, Stock In and Stock Out data can be a nightmare, especially if you want to monitor your stock multiple times a day.
This is the formula you want to use:
Current Stock Level= Initial Stock + Stock In – Stock Out
To configure your app to automatically record real-time inventory levels, do the following:
First, connect your Product Barcode columns in the Sales and Purchases sheets with the Product Barcode column in Product sheet. To do this: Go to Data --> Columns --> Sales, and click on Product Barcode and edit the column definition by following the three steps below:
Now you have pointed all data (needed to do the calculation) to one place, in this case, your Product sheet.
Next up: Tell your app how to calculate! Create a virtual column Current Stock in Product sheet and add your App Formula:
COUNT([Related Purchases]) - COUNT([Related Sales]) + [Initial Stock].
And that’s it. If you go to:
UX --> Product view --> Column Order and select Current Stock,
you will see every product’s Current Stock level.
Step 4: Display “Restock Needed” for low inventory products
Inventory managers need to make sure there is enough inventory to sell and that shelves are full. For example, if you keep running out of stock, there is no reason for customers to choose you over your competitors. In this section, I will show you how to let your app tell you: “You need to purchase the following products ASAP.”
To set restock alerts for low inventory products, follow these steps:
The following screenshot shows the view you created.
Step 5: Send email notifications on “Restock Needed” products
Let’s say that you don’t want to check your app to know what products need to be replenished. Instead, you want your app to send SMS or email notifications to you or to the purchasing department.
To do so, create a report in the Behavior section of the Editor and tell your app to notify you or purchasing managers when the Restock Needed view has at least one product whose current stock is lower or equal to the restock level. Follow the instructions in the image below and messages will be sent out everyday at 7 PM PST.
Here is what you will see in your email:
Okay—that’s it for now! Hopefully my mini-tutorial helped to fill some of your inventory management gaps. Questions, comments or even requests for my next app are always appreciated!