Updating Inventory row data in the inventory table once a new sale is recorded in the sales table

AppSheet AppSheet Q&A
,
1

Hello community, hope someone reads this and I get to find the right action formula.

I have been building my inventory and sales management app with appsheet, which is still in prototype.

Quick review of features: it adds stock and sales successfully. I want it to be able to run low cost alerts but haven’t reached there yet.

App problem: I had wanted the app bot to run an action to reduce the stock quantity in the inventory table once a new sale of that item is recorded in the sales table. However the formula I was using in the action on the quantity table would be successful on recording the first sale for example when the stock of books was 30, and a sale of 2 books was made. the stock quantity was reduced to 28. However on making another sale of any quantity of the similar product, the app would sum up all the sales of that item in the sales column including previous ones and subtract them off from the current inventory. For example if the second sale of five books is made. The app woul add the new sale of 5 to the 2, making the sum to be 7. And instead of subtracting 5 from 28 to update the new correct inventory to 23. The app is instead deducting the sum of all previous sales 7 from the current inventory quantity 28, giving a wrong updated stock quantity of 21.

I would like to be helped with the right expressions to use in my action and also for refrencing rows in the bot process. However my Sales table is well referenced to the Inventory table.

I will appreciate your feedback!

With regards,

James

2

Hello @mezan,

My guess is that you will need to clear the value of “items sold” so that it doesn’t consider the previous value.

You may also want to take a look at this Inventory Management Template for further ideas.

I hope this helps!

3

Thank you @Jose_Arteaga

I really appreciate your reply. Though for the template, it’s workflow is largely different from mine - which is composed of both sales and stock.

And about clearing the values I’m not sure what you meant

1 Like
4

Though I can add any well-wisher as a collaborator on the project and you help me on by taking a small look

5

Hello @mezan,

The solution is to ensure your Bot Action only references the single, newly added row in the Sales table, specifically the [Quantity Sold] from that one transaction.

The Correct Formula for Stock Reduction:
Assumptions:

  1. Your Inventory Table (Inventory) has columns:

    • [Product ID] (Key)

    • [Stock Quantity] (Number)

  2. Your Sales Table (Sales) has columns:

    • [Sale ID] (Key)

    • [Product Ref] (Ref type, pointing to Inventory)

    • [Quantity Sold] (Number)

1. The Bot Setup

Your Bot should be configured as follows:

Property Value
Event Data Change
Target Table Sales
Change Type Adds_Only (Since you only want to reduce stock when a new sale is recorded)

2. The Action (Process Step)

The Bot’s Process should execute a data action on the Inventory table, targeting the specific product that was just sold.

Action Name Reduce Stock (Data: execute an action on a set of rows)
Table to change Inventory
Rows to execute action on SELECT(Inventory[Product ID], [Product ID] = [_THISROW].[Product Ref])
Reference Formula Explanation: This formula finds the one row in the Inventory table where its [Product ID] (the key) matches the [Product Ref] column value from the current Sales row being processed ([_THISROW]).
Action to be executed Update Inventory Quantity (See below)

3. The Simple Data Action (Update Inventory Quantity)

This is the most critical part where you define the calculation. This action lives on the Inventory table.

Action Name Update Inventory Quantity (Data: set the values of some columns in this row)
Table to change Inventory
Set these columns [Stock Quantity]
Value [Stock Quantity] - [_THISROW_BEFORE].[Quantity Sold]

I hope it helps!