Help on Inventory App with stock release mechanics

Prismatique · May 8, 2025, 7:41pm

Hello to all !

Starting my Appsheet learning path. I’m seeking some inputs, on learning ressources, to resolve an issue..

I am trying to build a simple Inventory App, with 2 tables : “Inventory” and “Stock releases”, mostly in the way of this youtube tutorial : Joe Tayactac - AppSheet Inventory Management Part 1 of 2.

I just want to track the [Available Stocks] in my “Inventory” Table, trough each row created in “Releases Stocks” table. Which include a Ref to “inventory” and data pulled with standard “Dereferencing” (and not ANY(SELECT() formulas as mostly used in the video)…

Btw Actually my Data tables comes from linked 3rd party “Smartsheet”…

What would be the best way, for the [New Available Stocks] value to replace the [Available Stocks] in “Inventory”, for each Row submission inside “Release Stocks” table ?

The tutorial achieves this by using a “Bot automation” and a “Task Webhook” which include the following API call (on “Release Stocks” Add row) :

*> {*
*> "Action": "Edit",
*>  "Properties": {
*>     "Locale": "en-US",
*>     "Location": "47.623098, -122.330184",
*>     "Timezone": "Pacific Standard Time"
*>   },
*>  "Rows": [
*> {
*> "Item Code": "<<Any(Select(Release Stocks[Item Code],[_thisrow].[Item Code]=[Item Code]))>>",
*> "Available Stocks": "<<Any(Select(Release Stocks[New Available Stocks],[ID]=MAXROW("Release Stocks","_Rownumber")))>>"
*> }
*> ]
*> }

This is where I am getting lost. I don’t fully understand yet, what is happening here and in these 2 formulas. And even by using the exact same column naming this don’t work on my side.

Could you please guide me to the correct ressource, subject on which to learn, to be able to achieve this kind of workflow ?

Could using “Smartsheet” as table source, rather than plain google sheets or integrated tables be an issue ?

Many thanks !
Best Regards,

MultiTech_Visions · May 9, 2025, 5:16pm

Select() statements, using a webhook for simple data changes… it’s sad when people are spreading bad advice.

And wow… looking at that API call, with all the brute force… just… SMH

No wonder people can’t ever actually get anything they’re trying to do done; if you followed this type of advice, you’d basically paint yourself into a very difficult corner to get out of.

Thankfully that’s why you came here! Welcome @Prismatique

High level idea

The basic idea here is the following:

You have a table that records when stock changes
- I’m assuming the “Releases Stocks” is capable of both adds and removes, because you’ll need some way to add stock into the mix as well as remove.
When a new record is made, an automation needs to run on the item that the new record is ref connected to that adjusts the quantity by the new record’s value.
- You can easily accomplish this using the INPUT() feature

How to setup the Ref INPUT

This requires two actions:

On the table to set the value (Inventory)
On the table where you’re pushing a value (Releases Stocks)

#1 Start by creating the action to set the [Available Stocks] value

This needs to be an action on the parent table (Inventory)
It’s the type that sets the values of the row
The column is “Available Stocks”, the value needs to be an INPUT() formula
- [Available Stocks] + [_input].[stock_adjustment]

#2 Next create the Ref Input action

This needs to be an action on the child table (Releases Stocks)
The type is “Execute an action on a set of row”
The table is the parent table
The rows needs to be the parent ref column wrapped in LIST()
- LIST([Item Code])
The action needs to be the Input action
For the input you need to provide the adjustment: [Quantity]

Next create the automation

This needs to fire off from the “Add” event for the “Releases Stocks” table
It should run the data action created in #2 (above).

========================================================

Now when you create a record in the Releases Stocks table, it automatically updates the available stocks in the Inventory table.

========================================================

PS:

Not likely
There are some fiddly things with using Smartsheet that can get in the way in certain instances, but I don’t think you’re running into those here.

Prismatique · June 1, 2025, 10:00pm

Hello Matt .

Sorry for the delayed response… Just wanted to say : I love U !

I did completely understood what you detailled, And I successfully implemented it into my app…!!

Thanks a lot about that !

I need to dwelve in this " (_INPUT) " variable definition to better understand it. and expand its use…

Prismatique · June 1, 2025, 11:10pm

Testing this workflow for an “inventory app” makes me immediatly realizes a major flaw…

As the “Update Available Stock” action is launched at every new “row add” in “Release stock” table, this doesn’t allow a potential “user error” of creating then deleting a “release”, as the available stock is immediatly updated…

To counter this, I was thnking of adding a “first step” in the automation, to wait for “08:00:00” duration, so the “Available stock update” only happen by night, outside Workday…

But this workflow will be triggered even if the Row is deleted bfore countdown right ? so maybe wrong path ?

Many thanks for your feedback !

MultiTech_Visions · June 3, 2025, 7:35pm

You’ve definitely landed on something to watch out for.

One of the largest down falls in AppSheet has always been that there isn’t an UNDO.
- We can build something in, but think about the delete action… to undo that, we’d need to re-engineer the whole delete thing in the app
- Our best option is to install a “soft delete” system, where we flag records for deletion (filtering them out of the app) and then deleting them with a scheduled thing later on

This is why I usually advise trying to build a dynamic current stock system.

Instead of physically changing the number in the product record…
We instead use a virtual column to do the calculation of all the adjustment records on-the-fly - giving us an always up-to-date value.
- But as you can guess, there is more long-term impacts for this setup
- To accommodate for the build up of records over time, which will cause computational slow down, I typically implement an Audit system
  - The idea being we can say, “Starting 6/3/25 the inventory for each product is X” - and then clear out the adjustment records (moving them to an archive).
  - Then from there on we use this [Product_Starting_Inventory] to start with when calculating the available inventory.
    - This starting number + all adjustments = current stock
  - Then when we want to, we archive things again - resetting the number - and repeat forever, allowing for a long-term system.

I’m glad you got things working!

Prismatique · June 3, 2025, 8:21pm

Hello Matt .

Many thanks for your feedback…

Can you confirm me if i did understood correctly your last advice ?

You mean , for example, tracking all the “add” and “releases” of a dedicated item, trough a “sum” function (for rows in the release table) in a virtual column ?

Then only trigger the “update available stock value” workflow later on (end of workday for exemple) based on that last virtual column count , then move those rows to an “archive” table ?

This should account also for the rows deleted ?

Many thanks & best regards !

MultiTech_Visions · June 3, 2025, 9:11pm

Yeah that’s the idea, but maybe not as frequent as that.

I was thinking maybe on a quarterly frequency

But now that I think about it… since you’re wanting to implement an authorization system (where things can be submitted, but only until they’re marked as “okay” will the actual numbers change)… this would be easier to maintain long-term using physical changes.

You might be best served adopting a hybrid approach

Use actions/automation to make changes when you know things are 100% certain
Implement a mechanism that you can turn on for records that you’re not 100% about, these then need the approval.

But you’ll want some way to “just make changes” when you know things are working - like from Automation or an Ai or something.