Help on Inventory App with stock release mechanics

[Prismatique]

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]

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:

1. On the table to set the value (Inventory)
2. 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:

@Prismatique

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

• 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]

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]

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]

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

---

@Prismatique

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 immediately updated…

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]

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]

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.

[Prismatique]

Hello Matt !

I think I’m almost where I want to be with this workflow…

In My [Release Stocks] table, I created a virtual column that SUM all the Rows with the same [Item Code] and where the Boolean column [Stock Released]. here is the Formula

SUM(SELECT(Release Stocks[Quantity],AND(([Item Code] = [_THISROW].[Item Code]),([Stock Released]= FALSE))))

This virtual Column [Total Released Qty] seems to work fine. Each row report its total for same items correctly.

I then Created a Bot, Which on Daily scheduled event @ midnight launch the following tasks

1. the same “Ref input action” as you described earlier, instead that my [_INPUT] value, is now based on the VC [Total Released QTY], rather than the earlier [Quantity]

2. Set to “True” the Boolean column [Stock Released], so that they are not counted in future SUMS…

UInfortunatelly , with this approach, the “Update Stock” automation on my Stocks Inventory table, is registered 1 time, for each row with same Item. Like with the value in my first image :

• RJ45 is registered 2x times (2 rows) for 14 units (so 28)
• ACL4T is registered 3x times (3 rows) for 12 units (so 36)

Where do you think I can Improve this workflow, so that only 1 of the “Total Released QTY” per items is registered in the Update ?

I deeply thank you for your answer…
Best regards !

[MultiTech_Visions]

@Prismatique

SUM(SELECT(Release Stocks[Quantity],AND(([Item Code] = [_THISROW].[Item Code]),([Stock Released]= FALSE))))

I would hope that the Release Stocks are ref connected to the item they belong too… which means under the item you have a reverse reference of these records, yes?

Try this instead:

• SUM([Related Release Stocks][Quantity])

You might need to create a data subset of the released stocks (I see you’ve got a flag in there for those that haven’t been released yet.

• Make a slice with a simple formula to hold all of those records
• Then create a copy of your reverse reference column [Related Release Stocks], but use the slice instead of the main table
  • This will create a subset of the Release Stocks records that are in the status you specify
  • Then from this you can do your list dereference sum(....

---

But the whole point is to NOT have that virtual column.

• Either have the VC and use that, which dynamically calculates the values
• Or use automation

It makes no sense to create a VC that you then simply port into a PC by a bot; then you’ve got the app computation overhead of the VC, which is what the bot is supposed to be accounting for. At that point, use the VC and remove all the bot infra you’ve had to make.

Or remove the VC, and use the infra you’ve made to set the value, and take the formula you’re using and use that for the input value, the SUM()