Inventory Management

AppSheet AppSheet Q&A
, , ,
1

Hello,

I am trying to teach myself how to use this program, and have run into a few speedbumps as I learn. I am currently working on an inventory management system that includes a pick feature. There is a tab that shows visibility of all materials, a tab meant to place a “pick” request for my team to retrieve the materials, and a tab that has a list of open orders for my team to reference as they pull the material.

Process Flow:

  1. Ordering
    1. Select “Place Order” tab
    2. “Item Requested” field has a dropdown referencing “Inventory” table
    3. “Quantity Requested” field identifies how many of the items to pull down
    4. “Save” button generates a row in the “Open Orders” tab
  2. Pulling Orders
    1. Select “Open Orders” tab
    2. Select the row’s “edit” button
    3. Enter a number into the “Quantity Filled” field that is visible only to a specific user role (to avoid confusion for the ordering team)
    4. Change the order status from “Open” to “Closed” to signify the order has been pulled and is ready for use

Here is where I run into my problem. I would like the action of marking the order as “Closed” and saving the form to update the inventory quantity for that row. I have not found a method that works online, as some of the processes that I am using are more specific. Any help would be greatly appreciated.

2

No doubt, the issue you are running into, since you didn’t explicitly state the problem, is that when you get to the action to update the Inventory row, you no longer have access to the “Quantity Filled” field to grab the value with which to update the Inventory row.

There are a couple of solutions.

  • Use the INPUT() function. I hear that many are using it as a production ready function but in AppSheet’s notices it still regarded as a Beta function. I have also run into a couple apps where INPUT() is just simply not available - I think due to the nature of the accounts I am working under. However, INPUT() is relatively easy to use and was created to fill the gap in updating rows.

  • If INPUT() is not available, you will need to somehow “remember” the value, or the Order Detail row, in a temporary table location and retrieve it when in the action for updating the Inventory row. You will need to have the ability to definitely identify which temporary table row to use in your update if there is a possibility of multiple people performing the same updates. I typically will use a small table and record the user ID along with a way to get to the desired value - e.g. Order Detail row. Then in the Inventory row update action, when applying the update to the Quantity column, I would use a SELECT() to retrieve the temporary table row by current user AND Product since I WILL know which Inventory Product I am updating. You would then want to delete the temporary table row to prevent additional updates.

3

Hi there, perhaps u may share your table setting too. So it may be helpful for giving good advice.

Not sure does this help: You may check the Even Action in the form (in the Behavior section of the form), and you may utilize this to update the order status once the form is saved.

Or you may check on my channel, I do share some Inventory Management App & Van Sales App, hope it may give you some clue.

Inventory Management

Van Sales

Cheer!

4

This is a little tricky to do in Appsheet. Since I recently had to implement something similar I will try to lay out a method based on your initial post.

The overall process is this: you have an “Automation” that runs when order status is changed to “Closed”. This automation will then update the inventory table when users change the status to “Closed”.

1. Add an Inventory Updated column to “Open Orders”

You will need to add a column to the “Open Orders” table that the system can use to track which closed orders have been processed. A “Yes/No” column called “Inventory Updated” would do the trick. This column should default to “No”, and since you don’t want users messing with it you should uncheck the “Show” attribute. It would also be good to set an expression on the “Editable” attribute under update behavior to “false”. Note that this is not the same as unchecking “Editable”… the documentation expands on how to set “Editable if” expressions. This will allow the system to update the field, but not users.

2. Add a Behavior to “Update Inventory for Closed Orders”

You will also need to add a “Behavior” for your inventory table. Lets call it “Update Inventory for Closed Orders”. For the “Do This” field choose “Data: set the values of some columns of this row”. You then choose your quantity column in the “Set these column” field. For the value you need to craft an expression that will decrement the quantity by the picked quantity. This would look something like this:

[Quantity] - INDEX(SELECT(Open Orders[Quantity], AND([Item ID] = [_THISROW].[Item ID], [Order Status] = “Closed”, [Inventory Updated] = “No”)), 1)

Note that the “INDEX” function is necessary because the “SELECT” function returns a list and you cannot do arithmetic with a list. INDEX just returns the first item in the result.

3. Create an “Automation” to do the work

You then create an Automation on the Open Orders table that triggers on [Order Status] = “Closed”. To do this you go to the Automations section and add an Event that triggers on updates to the “Open Orders” table. The condition will be an expression that looks something like this:

AND([Order Status] = “Closed”, [Inventory Updated] = “No”)

You then create a “Bot” that uses the newly created event. You will then create a process for the bot with two steps:

The first step will “Run Action on Rows” of your inventory table. The referenced rows field should be an expression that selects the appropriate inventory record. This would look something like this:

SELECT(Inventory[Item ID], [Item ID] = [_THISROW].[Item ID])

The “Referenced Action” should be the behavior you created above: “Update Inventory for Closed Orders”

Then create a second step in the process. This process step will “Set Row Values”. It should set [Inventory Updated] = “Yes”.

After that you should have the behavior you desire. I wrote this from memory so there may be mistakes in here. If you spot any mistakes or any of the steps are unclear, you can DM me and I’ll update this post.