Help on Inventory App with stock release mechanics

General
1

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”…

Appsheet1
Appsheet1598×853 95.4 KB

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,

1 Like
2

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

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

  • 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. :wink: