How can I execute an action on a set of rows in a table other than the one where the event is generated?
In general it sounds that you are looking for something like “reference actions”
Please elaborate if you are looking for something else.
@Suvrutt_Gurjar is correct but the event table must be related to the referenced table. For example, if you have a Parent and Child table, then the Child will have a reference to the Parent. You can set up a Bot to respond to changes in the Parent. The Bot can then modify any associated Child records.
However, you may want to make changes to an unrelated table, for example a Log table. To illustrate, I selected and loaded the “Field Delivery” sample app into my workspace. The data structure is simple: a Driver table and a Job table. Drivers are assigned Jobs.
Let’s say I want to Log all changes made to the tables. Therefore, I created a third table called “Log”. Here are the Bots, Events, and Actions that I created to make it work:
Log table results:
Note that the “Driver” and “Job” columns are TEXT fields not REFS. There is no connection between those tables and the Log. They are there just for additional information.
I think in the image below might be the Action Type you are looking for.
I call this a Bridge or Transition action. it allows you to transition from operating on a single row in one table to operating over a set of 1 or more rows in another table (can be the same table).
Referenced Rows MUST be a LIST of rows keys in the Referenced Table. You can get this list from a “Related” virtual column, e.g [Related Order Details]. Or you can use a FILTER() or SELECT() function to retrieve the set of row keys. They do NOT need to be related to the original row in any way though typically the need for this action means they are.
I hope this helps!
Hello, thank you for your contribution.
It is really about changing the value in a group of rows in table 2 when adding a new record to table 1; not adding rows.
TABLE1 - TURNS
TABLE 2 - GENERAL
When adding a new record in table 1, you must change the [STATUS] in all the rows of TABLE 2, where the field [GROUP]=1 .
Thanks a lot of
Hidros, what do you think of the following:
I invented a new table called GROUP. Each record in the GENERAL table has a reference to a corresponding GROUP record. AppSheet will create a Virtual Column in the GROUP table with a list of associated GENERAL records. So for example. GROUP ID: 1 will point to GENERAL ID: 1 to 4 and GROUP ID: 2 will point to GENERAL ID: 5 to 10. This greatly improves the performance.
I know from experience that you can create a Bot that reacts to changes to a GROUP record. For example, if you update GROUP ID: 1 and set LAST=“2:05:15AM”, then the Bot will perform an action on the four associated GENERAL records.
Now, let’s introduce the TURNS table:
The following is pure speculation. You need test it. But you might be able to create a Bot that reacts to changes in the TURNS table. So for example, if you were to add a new TURNS record having GROUPID=1 then the Bot can run an Action to update that GROUP record. That will then trigger the other Bot to update the associated GENERAL records.
Give it a try. It might work.
What you describe is now exactly the case I have. About the solution method it sounds like it works but I need to read something more to apply it.
My problem is about reference, i guess.
**with a list of associated GENERAL records.**
Thank you very much, I will try it and let you know.
Regarding with a list of associated GENERAL records.
In Google Sheets, create a workbook called “Test App” with three worksheets: TURNS, GROUP, and GENERAL. Add the column names and mark them in Bold. Add some rows of data. Note that in my example I used numbers for keys. AppSheet uses 8-character alphanumeric for keys. So beware that AppSheet might have trouble creating an app using the simple numeric keys.
Next, go to appsheet.com and click “My account” then “My Apps”. There will always be a card with a “plus sign” that says “Make a new app”. But if you are lucky there should be an additional card that says something like “Make Test App”. If you click “Make a new app” you will have to tell it which data source to use. It should see the three worksheets: “TURNS”, “GROUPS”, and “GENERAL”. Click buttons to import those tables.
Next, select a table and click “View Columns”. Make sure that the GROUPID column type is REF. Change it if it is not. AppSheet should automatically create two virtual columns in the GROUP table of type LIST. One should be called something like “Related TURNS” and the other "Related GENERAL"
My best advice for success with AppSheets is to stop thinking like a programmer if that is your background. Think more like a Data Architect. Our job is to tell AppSheet how tables are related to one another. The looping and iterating through lists are handled entirely behind the scenes by AppSheets by Bots and Views.
I got it working. How is your implementation coming along?
Hi, thank you very much for your interest and patience.
I am very frustrated, I have tried several ways and I can’t get it to work.
When i cread a new record in Table 1, it take GROUPID (user select) and STATUS (user select) and update TABLE 2 (GENERAL). I mean all records on Table 2 (GENERAL) with GROUPID “2” change to CLEAN
Your Actions look good, but Actions alone won’t make it work. You need a Bot that looks for ADDs to the TURNS table. Did you define a Bot and an Event?
You need the Bot to respond to ADDs to the TURNS table. It should respond by calling “New Action”.
How are you adding a new record to the TURNS table? Are you using the TURNS_Form view? Or are you adding a record directly into Google Sheets?
I ask because the ID fields in your spreadsheet are numeric. AppSheet creates alphanumeric keys. So, this tells me that you are adding TURNS records directly into the spreadsheet. To the best of my knowledge, the Bot will not be invoked if you do that.
from a Form_View
I have already tried with an ID (UNIQUEID) and then with an alphanumeric (entered by the user). Very strange results.
All right, time for debugging. Add a Task to the Process to send you an email. That should tell you if the Bot was triggered.
its fine. email sent.
Add to application ‘DearBrian’ table ‘TURNS’ by ‘email@example.com’ at 30/07/2021 13:45:17 [App version 1.000033 is not deployed. All emails are therefore being sent to the app creator. This email should have gone To ‘firstname.lastname@example.org’ CC’ed to ‘’ and BCC’ed to ‘’]
All right, add to the email body the value(s) of GENERAL[GROUPID].
Also, try a simpler expression in “New Action 2”. You’ve got a switch statement. Try something simple like setting the column to “FOO”.
Hmm, I expected GENERAL[GROUPID] to be a list of “1,2,3,4”
I think you need to do the following in “New Action”:
Replace GENERAL[GROUPID] with SELECT(GENERAL[ID]. (GROUPID=[_THISROW].[GROUPID]))