Referencing and setting single-cell values in one table from a separate table without creating a new row

[mavv]

Hi there. I’m working on a simplified ERP in Appsheet and struggling with a few of the automations. All problem automations have to do with setting a value in an existing row in Table A from a value in/form related to Table B. I saw this post, but the poster didn’t provide enough information for someone to suggest a solution. I’m hoping now there might be a solution to this, perhaps?

Setup:

1. User creates a “job” in Appsheet by filling out a Create Job form. In that form, the user enters a numeric Estimated Material Use value (like “10,” as in pounds). If needed, the user can fill out a Cancel form afterward to represent “cancelling” the job in the real world. The Cancel form in Appsheet includes fields where the user can enter a Completion % value (as a whole number, like “50”) and Actual Used Material value (also a whole number like “20”).
2. There are two tables: one for Jobs and one for Material Tracking. The Jobs table shows all jobs and their associated information, including values like Estimated and Actual Material Use. The Material Tracking table contains a list of all possible materials, all of which have unique IDs set by the app, and the total amount in stock for each material. The “Total In Stock” value for each material is the same format as the Estimated Material Use and Actual Material Used fields in the Jobs table (i.e., numeric value representing pounds).

Goal:

• When a user fills out the Cancel form to “cancel” a job, the system should calculate an updated value for the Total In Stock field in the Material Tracking table like this:

Total In Stock (new value) = Total In Stock (existing value) -

IF(

ISNOTBLANK(Jobs[Actual Material Used]),

Jobs[Actual Material Used],

Jobs[Estimate Material Use]

*

IF(

ISNOTBLANK(Jobs\[Completion %\]),

Jobs\[Completion %\] / 100,

1

)

)

The idea of this formula is: if there is a value in Actual Material Used (in the Jobs table), subtract that from the Total In Stock value in the Material Tracking table. If not, multiply the Estimate Material Use value by the Completion % value / 100 (if Completion % has a value in it; else multiply by 1) and subtract that from the Total In Stock value in the Material Tracking table instead. This calculation should not create a new row - it needs to edit the existing row for the material based on the unique material ID. New rows in the Material Tracking table are added manually via a different form in the app; it’s important for this decrementing calculation to edit the correct existing row for the material used in the job.

I’ve hit a lot of reference-related errors with functions like [_THISROW], [_THISROW-1], and wide-reaching functions like ANY() and SELECT(). Typically, the errors call out that the row I’m trying to set or reference is not in the table I’m currently editing.

Any and all suggestions appreciated. Just trying to get some accurate material quantity tracking going on over here.

[Shourya_Tomar]

Hello @mavv, Welcome to the community!

I did some preliminary research and have identified several support articles and previous community discussion threads that might assist you in resolving your inquiry.

You can use the INPUT() function to Set input values dynamically in data-change actions.

Set input values dynamically in data-change actions - AppSheet Help

The below discussions on the community might also be helpful in your case.

Passing the selected row to another action

In view MY GROUP (filtered to show a single, user specific row), there is an inline table of RELATED SECTIONS. In the same MY GROUP view is an inline table of RELATED MEMBERS, filtered to show only rows for a selected SECTION (this selection is currently stored in a separate USER table). My objective is to remove the need for the user to manually update the selected SECTION on the USER table and instead have the field updated when the user clicks a row in the RELATED SECTIONS inline table, I …

Using INPUT() for "just in time" calculation and display

This post is a follow-up to “Using INPUT() to update another table using a value from this row”: https://www.googlecloudcommunity.com/gc/Tips-Tricks/Using-INPUT-to-update-another-table-using-a-value-from-this-row/m-p/328992 In that tip, I provide a sample app that shows, as the title indicates how to take a value from a row and update a cell on another table. What I did not do was try to explain why that can be very useful. In this post I’ll attempt to do that. Here are some screenshots from …

Thank you so much! I hope it helps.

[Steve]

There are several possible approaches:

1. Make the Total In Stock column a virtual column. It will then update automatically. This will contribute to slow sync times, so best avoided.
2. Use a Form Saved event action to reach out to the related row in the Material Tracking table to update the Total In Stock value. This updates the value immediately, allowing the user to see it immediately.
3. Use a bot to reach out to the related row in the Material Tracking table to update the Total In Stock value. The user won’t see the updated value until the next sync. If the computed value requires values from data that would not have been on the user’s device, this is the approach to use.