Auto update Inventory Master

[DME1]

I have created an app sheet to track inventory for entries coming in and out. There are three sheets in Google Sheets: Inventory Master, In, and Out. When I enter In inventory, the Inventory Master should automatically update the "Received" column with the new entry. However, in my case, it does not update automatically. Instead, I need to edit the Inventory Master form and click "Submit" to receive the entry details.

Example:
In the Inventory Master, the stock entry shows as 0. When I enter a value of 100, I expect the Inventory Master to automatically update from 0 to 100. However, I currently have to go into the Inventory Master form, click 'Edit', and then 'Save' for the new entry to appear.

[WillowMobileSys]

---

@DME1 wrote:

When I enter In inventory, the Inventory Master should automatically update the "Received" column with the new entry

---

Updates between tables do not occur automatically.  You must create actions or Bots to perform these updates.  Maybe you have done that?

A common problem when building these updates, is that the actions lose access to the original row in order to retrieve the entered value that needs to be sued to make the update.  AppSheet introduced the INPUT() function to helps solve this problem.  It allows you to pass values between actions.

You can find more details about the INPUT() function in this article:

Set input values dynamically in data-change actions 

I hope this helps!

[Denny774]

Hey,

you should have one Master and one Detail (Child) Table.
The SUM() of the Child-Records is the Stock-Amount in Master (IN +1, OUT -1 = 0 in Stock)

Cheers

[PauloExpert · Accepted solution]

Thanks for sharing the screenshots and context. You're on the right track, and this is a common AppSheet behavior—table values like “Received Stock” in the Raw Material table won’t update automatically unless you use App formulas or actions to recalculate them.

Here’s how to fix it so that your "Received Stock" column in Raw Material updates automatically when a new record is added to Raw Master In.

Goal:

When a new "In" record is added for a material (e.g., Apple), it updates the "Received Stock" in the Raw Material master table.

Step-by-step Fix

1. Make "Received Stock" a Virtual Column (or use an App Formula)

In your Raw Material table, go to the [Received Stock] column and set the formula:

SUM(

  SELECT(

    Raw Master In[Quantity],

    [Raw Material] = [_THISROW].[Raw Material]

  )

)

What this does:

It looks at the Raw Master In table

Filters for rows that match the current raw material

Sums the quantity values

> If you make this a Virtual Column, it will always stay updated. If you use a regular column with an App Formula, it updates only when the record is touched—but virtual is usually better for this kind of real-time summary.

2. Do the same for "Issues Stock" (optional)

In the Raw Material table, for the Issues Stock column, set:

SUM(

  SELECT(

    Raw Master Out[Quantity],

    [Raw Material] = [_THISROW].[Raw Material]

  )

)

3. Calculate Current Stock Automatically (optional)

Create a Virtual Column (or use an App Formula) for [Current Stock] in Raw Material:

[Received Stock] - [Issues Stock]

No More Manual Edits Needed

With this setup, your Inventory Master table (Raw Material) will update automatically whenever new "in" or "out" records are added—no manual edits or saving 

[DME1]

I'm not able to access virtual column data in Google Sheets. I want to perform analysis using Google Sheets. How can I do this?

[PauloExpert]

Check for a plus button

[dleblanc79]

I'm newish to appsheet and trying to implement this method in my app.  I've successfully  got the PO (Inventory In Sum) but I am struggling with the Order Side (Inventory Out Sum).  In my mind they should work exactly the same but its not.

This works

SUM(

SELECT(

[Related POs][Farmington],

[Product] = [_THISROW].[Product]

)

)

This doesn't

SUM(

SELECT(

[Related Orders][FarmingtonQty],

[Product] = [_THISROW].[Product]

)

)

[PauloExpert]

The issue you're experiencing with AppSheet could stem from how the column [Related Orders] is set up or how [FarmingtonQty] is structured within that related table. Here's a checklist and solution to debug and fix this:

1. Check the Column References

Ensure that [Related Orders] is a proper ref list column that references a related table, just like [Related POs]. If [Related Orders] is not properly set up, the SELECT function won't retrieve the desired values.

Confirm that [FarmingtonQty] exists in the related table and has the correct data type (e.g., a number).

2. Ensure Consistent Column Names

Verify that [Product] exists in both the current table and the related table being referenced by [Related Orders].

Check if there are any typos or mismatches in column names.

3. Debugging Your Formula

Here’s how you can systematically test and isolate the issue:

a. Simplify the SELECT Statement

Run this SELECT statement independently to see if it retrieves the expected list of values:

SELECT([Related Orders][FarmingtonQty], TRUE)

This should return all the [FarmingtonQty] values from [Related Orders]. If it doesn’t, there’s an issue with the [Related Orders] setup.

b. Test the Conditional Logic

If the above works, add your condition back in:

SELECT([Related Orders][FarmingtonQty], [Product] = [_THISROW].[Product])

Check if the condition properly filters the results.

4. Compare with the Working Formula

Look at [Related POs] and [Farmington]:

If [Related POs] works, it means [Farmington] and [Product] are correctly aligned. Compare this with [Related Orders] to ensure that [FarmingtonQty] and [Product] are similarly aligned.

5. Solution

If all else fails, you can debug further using Virtual Columns to see intermediate results:

Create a Virtual Column to calculate the SELECT statement output:

SELECT([Related Orders][FarmingtonQty], [Product] = [_THISROW].[Product])

Use this Virtual Column in the SUM function to simplify debugging.

Revised Formula Example

If everything is correctly set up, your formula should look like this:

SUM(
SELECT(
[Related Orders][FarmingtonQty],
[Product] = [_THISROW].[Product]
)
)

[DME1]

Thx i got it.

[dleblanc79]

I'm fairly new to appsheet and I am trying to implement this code into my app.  Ive successfully setup the PO side of adding inventory but I am struggling with the order side which should be the same with a different dataset-column.  

[PauloExpert]

It sounds like you're on the right track with your AppSheet implementation, and the concept for the "Order Side" should indeed be similar to the "PO Side." However, subtle differences in how the data or columns are structured could be causing the issue. Here's a systematic approach to help you troubleshoot and implement the "Order Side" logic:

1. Understand Your Dataset

Before diving into the formula, ensure that:

The "Orders" table (or dataset) is set up correctly and includes the columns [Product] and [FarmingtonQty].

[Related Orders] in your current table is a list of references pointing to the "Orders" table, similar to [Related POs] pointing to the "POs" table.

2. Compare the Working PO Formula

Your working formula for the PO side looks like this:

SUM(

  SELECT(

    [Related POs][Farmington],

    [Product] = [_THISROW].[Product]

  )

)

Key Observations:

[Related POs] is a list column referencing rows in the "POs" table.

[Farmington] is the numeric column you are summing.

[Product] is used to filter the rows where the product matches the current row.

3. Replicate for the Order Side

Now, adjust your formula to match the "Orders" dataset:

SUM(

  SELECT(

    [Related Orders][FarmingtonQty],

    [Product] = [_THISROW].[Product]

  )

)

Things to Verify:

1. [Related Orders]:

Is it a properly configured list of related rows? It should point to rows in the "Orders" table.

If not, confirm the reference setup in your column configuration.

2. [FarmingtonQty]:

Is this the correct numeric column in the "Orders" table that you want to sum? Double-check for typos or incorrect column names.

3. [Product]:

Is [Product] in the "Orders" table? Does it match the [Product] in the current table?

4. Debugging Tips

Test the SELECT Statement: Use the AppSheet expression tester to evaluate the SELECT statement independently.

SELECT([Related Orders][FarmingtonQty], [Product] = [_THISROW].[Product])

Ensure it returns a list of values from [FarmingtonQty] where [Product] matches the current row.

Inspect Relationships: Go to the "Data" section in AppSheet and review the table schema. Ensure the references between the current table and the "Orders" table are correctly configured.

5. Common Issues to Check

1. Incorrect Column Reference: Ensure that [FarmingtonQty] exists in the "Orders" table and is spelled exactly as it appears.

2. List Not Configured: [Related Orders] must be a valid reference list. If it's not working, you might need to create or fix the reference.