Execute an action on a set of rows from a different table

AppSheet AppSheet Q&A
,
1

How can I execute an action on a set of rows in a table other than the one where the event is generated?

2

In general it sounds that you are looking for something like “reference actions”

https://www.appsheet.com/samples/This-app-shows-how-to-use-reference-actions?appGuidString=e76d2e73-3d26-475c-a8f8-9911f5015920

Please elaborate if you are looking for something else.

2 Likes
3

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

Actions overview:

3X_3_4_34c52bc80d63fcdb2521dc7cf945574d7e618048.jpeg
3X_3_4_34c52bc80d63fcdb2521dc7cf945574d7e618048.jpeg356×404 29.7 KB

Action detail:

3X_3_e_3e1343c36516551d7a7fe36060e5a8aeb4f0d234.jpeg
3X_3_e_3e1343c36516551d7a7fe36060e5a8aeb4f0d234.jpeg820×599 75.2 KB

Events overview:

3X_a_a_aa233759d33623074ee2be08438d8cfd71fdce38.png
3X_a_a_aa233759d33623074ee2be08438d8cfd71fdce38.png385×604 75 KB

Event detail:

3X_d_8_d80d6728e4868f0800807abd86d661a1ff3e0853.jpeg
3X_d_8_d80d6728e4868f0800807abd86d661a1ff3e0853.jpeg471×631 48.2 KB

Bots:

3X_9_a_9a73f5c8d8a024eb257d185fb2ff3c71755339d6.jpeg
3X_9_a_9a73f5c8d8a024eb257d185fb2ff3c71755339d6.jpeg350×630 37 KB

Processes overview:

3X_4_6_4624cd7cc05e392c94981fc935a20e3b97368609.jpeg
3X_4_6_4624cd7cc05e392c94981fc935a20e3b97368609.jpeg366×623 36.2 KB

Process detail:

3X_6_1_61a9816b03363cf376e856f7c851911e2612ba0d.jpeg
3X_6_1_61a9816b03363cf376e856f7c851911e2612ba0d.jpeg330×719 41.8 KB

Log table results:

3X_8_c_8c38b2e6b514058855d93559dd13b4838b5cfbf1.jpeg
3X_8_c_8c38b2e6b514058855d93559dd13b4838b5cfbf1.jpeg625×147 37.7 KB

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.

Brian

5 Likes
4

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.

3X_5_1_517ef707748128230b303477dd2d23a732aee653.png
3X_5_1_517ef707748128230b303477dd2d23a732aee653.png1942×1212 183 KB

I hope this helps!

1 Like
5

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

1 Like
6

Hidros, what do you think of the following:

3X_d_c_dca0cbdc549927742750072aea255c0dca482006.png
3X_d_c_dca0cbdc549927742750072aea255c0dca482006.png399×241 2.47 KB

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:

3X_a_7_a7859c7776ad56d3ab3a9486c19d61f621ee01e7.png
3X_a_7_a7859c7776ad56d3ab3a9486c19d61f621ee01e7.png590×241 3.16 KB

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.

Brian

7

Thanks Brian!
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.

8

Hi Hidros,

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.

Brian

1 Like
9

Hi Hidros,

I got it working. How is your implementation coming along?

Brian

10

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.

3X_7_a_7ab5342aef6e3fa2f393ee3cec784a60f7c83b21.png
3X_7_a_7ab5342aef6e3fa2f393ee3cec784a60f7c83b21.png585×208 41.9 KB

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

3X_a_f_af64bd9a77afbcfcd346f8feba01b80cb4fd1468.png
3X_a_f_af64bd9a77afbcfcd346f8feba01b80cb4fd1468.png577×394 38.4 KB

3X_c_8_c88482281b05e3b15d37f8cc7035d4de4fe4f4aa.png
3X_c_8_c88482281b05e3b15d37f8cc7035d4de4fe4f4aa.png583×341 37.5 KB

11

Hidros,

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

Brian

12

Yes sir,

3X_d_6_d6b693e99f64847c2fe91e7fef33fa57cc286761.png
3X_d_6_d6b693e99f64847c2fe91e7fef33fa57cc286761.png589×272 26.3 KB

13

OK good.

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.

14

from a Form_View

15

I have already tried with an ID (UNIQUEID) and then with an alphanumeric (entered by the user). Very strange results.

16

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.

17

its fine. email sent.

Add to application ‘DearBrian’ table ‘TURNS’ by ‘xxxxx@xxxxxxx.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 ‘xxxxxxxx@xxxxxx.com’ CC’ed to ‘’ and BCC’ed to ‘’]
ID: 4

GROUPID: 1
STATUS: 3

18

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

19

3X_f_a_fa26382bdb267e7b14e7cb6b113520580fdb0edc.png
3X_f_a_fa26382bdb267e7b14e7cb6b113520580fdb0edc.png592×214 35.8 KB

3X_0_e_0e8f925e1e21c6fb4646259352bc18de9dbdca1a.png
3X_0_e_0e8f925e1e21c6fb4646259352bc18de9dbdca1a.png575×383 56.4 KB

20

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]))