When a change occurs to a record, I need a related record to change also

[Patrick_Paul]

I have two tables in my app, toolboxes and trainees. Toolboxes are assigned to trainees via a ref (in the toolbox record, you choose which trainee is associated with that box). I need a way so that when a trainee leaves the company (their “status” field changes) it automatically causes the status field of their associated toolbox to change as well (to “needs pickup”… we pick up and re-assign the boxes). Right now, users have to remember to edit the toolbox record if a trainee leaves, but everybody forgets to do it. Until I get “better” co-workers (ha ha!), does Appsheet have a way to automate this? Thanks!

[Marc_Dillon]

Check out Automation and/or the Action type “execute an action on a set of rows” (also called “reference action”).

help.appsheet.com

AppSheet Automation: The Essentials

https://www.google.com/search?q=appsheet+reference+actio

[Patrick_Paul]

Thanks for the suggestions, however… Creating an action button that they would have to click still leaves room for them to forget to do it. Also, it’s a bit counterintuitive to the process, as there are many possible statuses, and this would only apply to one of them. I had already looked at Automation, but at this time it seems the tasks are limited to sending messages and emails, reports and creating files… I don’t see a task that can modify a record available yet?

[Marc_Dillon]

Patrick_Paul:

I had already looked at Automation, but at this time it seems the tasks are limited to sending messages and emails, reports and creating files… I don’t see a task that can modify a record available yet?

There certainly is.

[Patrick_Paul]

VERY spiffy… it’s not in any of the documentation (yet). I’ll go check that out.

[Patrick_Paul]

I see another fly in the ointment. For an event based on “data change”, it seems that my condition (in this case, trainee status is “terminated”) is checked every time there’s an update, and will fire the task. This is undesirable. I only want to fire when the status first changes to “terminated”, and any subsequent edits would not fire the task, even if the status were to remain “terminated”.

[Marc_Dillon · Accepted solution]

help.appsheet.com

Workflow

Send Email, SMS, or Notifications on Add, Update, or Delete.

[Patrick_Paul]

To be clear to anyone else who may look at this, the dereferencing “how to” for updates to a status found way down the above document contains the solution. It’s not super obvious.

[Patrick_Paul]

Argh. I’m just now getting time to circle back to this and try to work it out. I still don’t seem to be able to create an action that will act on a related row in another table. When I choose “run a data action” and then “set row values” it only allows me to choose fields within the current table, or run actions from the current table…

[Marc_Dillon]

Marc_Dillon:

Action type “execute an action on a set of rows” (also called “reference action”).

To use reference Actions, you set up the actual Action on the actual Table you want it to run, then you set up a 2nd Action (“execute on a set of rows”) that sits on the Table that the Bot is triggering from, and points to the 1st Action.

If you need values from the originating record, check out the new INPUT() expression, particularly this post:

Using INPUT() to update another table using a value from this row Tips & Tricks 💡

This “Tip” is a little report, with a sample app, on my own attempt to familiarize myself with the new INPUT() function. As @praveen has indicated, the advent of this function gives us the power of an “UPDATE EXISTING ROW(s) using values from this row” action: Here’s a sample app I made to test this out: https://www.appsheet.com/samples?appGuidString=502cc394-2717-4a69-8246-5fc26a1aa9cf This extremely simple app has two tables: one with multiple records and one with only one record. The i…

[Patrick_Paul]

Would there be a way to prevent the action you create on the actual table from being available any other way? I don’t want a clickable icon to appear for it or anything in the detail view. It needs to only happen during automation… The second method you list is more than my puny brain can wrap itself around. Which is why I never did any Object Oriented Programming.

[Marc_Dillon]

Patrick_Paul:

I don’t want a clickable icon to appear for it