Create an Inventory Management App Using Google AppSheet Part — 2
Welcome!
In the last blog we learnt how to create the app , setup the product codes and make our first sale . In this blog I am going to show you how to calculate the total inventory using some formulas and slices. Then we will see how to display the items that need to be restocked.
So let’s get started.
Step 1 : Add the Third Sheet
This is what the third sheet looks like. The third sheet will contain the restock details like :
(1) Timestamp
(2) Product code
(3) Quantity of stock added.
In order to add the third sheet, follow these steps :
(1) Click on Data
(2) Next you have to click on the Tables button.
(3) Next you have to click on the plus sign that says New Table .
(4) After clicking you will see something like this .
(5) After selecting the sheet, click Add This Table .
(6) Now you can see that the sheet has been added to the app.
Even after adding the sheet you cannot see it in the app preview . In order to add Restock in the app view, follow these steps:
(1) Click on UX .
(2) When you click on UX it will show you the views in the app.
(3) In order to add a new view you have to click on the button New View .
(4) Here it will ask you to give a name to the view and then it will ask you to specify the name of the sheet. After this if you want you can make some more changes to it.
(5) After setting everything up, click on Save .
(6) After clicking Add Stock, you should see something like this .
Step 2 : Calculate the Current Inventory for each Product
We want to see the current inventory of each product in the app. To do that follow these steps:
(1) Go to data and click on Columns.
(2) Click on Add Virtual Column.
(3) Here you have to give a name to the Virtual Column. Then just go ahead and paste this formula in the App Formula tab .
SUM(
SELECT(
Purchases[Quantity],
[Product Barcode] = [_THISROW].[Product Barcode]
)) -
SUM(
SELECT(
Sales[Quantity],
[Product Barcode] = [_THISROW].[Product Barcode]
)) +
[Initial Stock]
This formula will automatically calculate the current stock .Now we have to display the current stock under each product. In order to do that follow these steps :
(1) Click on UX .
(2) When you click on UX, it will show you all the views in the App .
(3) Click on products and and scroll down and click on Secondary header .
And then click on Current Stock and then click save.
Now it’s time to check if the formulas have worked.We are going to add stock, and check whether the stock added gets added to the current stock. In order to add stock follow these steps:
(1) The initial stock of chicken is 76.
(2) Click on Add Stock .
You will see something like this .
(3)
Now go ahead and select the code for chicken that is 6000000, and then add some quantity. I have added 10 quantity for chicken.
(4) After adding everything click on Save .
Now you can see that the current quantity of chicken has been increased to 86.
This means that our formula has successfully worked .
Step 4 : Display Restock Needed for Products having Low Current Stock
The store manager needs to make sure that he has enough stock to sell . This step will display all the products that need restocking .
In the Products sheet we have a column that says restock level. This means that whenever the current the stock reaches the restock level, the app should display that the product needs restocking . To do this, follow these steps :
(1) Go to data and click on Slices .
(2) Click on the plus sign that say New Slice .
(3) Here I have named the slice as Restock Needed . Then just go ahead and paste the formula in the Row Filter condition .
[Current Stock] <= [Restock Level](4) After making the changes click on Save .
Now you have added the slice but cannot see it in the app preview . In order to do that follow these steps:
(1) Click on UX .
(2) Click on the plus sign that says New View .
(3) Here I have named the view Restock Needed . Then I have specified the slice that we just created as the source table .
(4) After doing this click on Save .
Now if you click on Restocked Needed , you will see something like this .
Congratulations !
Your Inventory Management App is ready. This is all for this blog . I hope you have understood how to create a simple Inventory Management App using Google AppSheet . You can send your feedback on aryairani123@gmail.com .
