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:
Actions overview:
Action detail:
Events overview:
Event detail:
Bots:
Processes overview:
Process detail:
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.
Brian
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
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!
Thank you. Sounds Interesting
I have almost the same problem As @Hidros
But what should be next step after filtering data
I thought I should create Referenced Action as an action on selected rows
But the only available options are Delete, Add or Edit
But I need to modify some columns in selected rows
Sorry
I found a solution
I should create Referenced Action before!
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.
Brian
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.
Hi Hidros,
I got it working. How is your implementation coming along?
Brian
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
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
Yes sir,
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.
from a Form_View
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
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 ‘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
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]))
Hidros,
After you get this working, you should consider the advantages of creating a third table called GROUP. Not only will it scale better, but you won’t have to do a SELECT each time a TURNS record is created. The disadvantage is that you will need two Bots instead of one. That is how I implemented it.
Brian
A third Table would be no problem, if it’s a better way and it work for you, I can implement it, it doesn’t affect anything. I just didn’t understand the relationship in your example, and that’s why I sent even what I understood. Go ahead with the third Table if you help me.
Hidros,
Are you still at work? Are you heading home soon? I ask because it could take me an hour to take screenshots and write it up.
Brian
im online , no problem
OK, I’ll work quickly to get it out very soon.
using:
SELECT(GENERAL[ID],(GROUPID=[_THISROW].[GROUPID]))
Nothing yet.
Hidros,
You can get started on this right away. Make sure to read the instructions especially step #4. You don’t need to add records since wel will create the App from scratch.
Brian
Ready, its done
Next:
Click “View Columns” for the GROUP table
ID column, ensure the following:
a. TYPE: Text
b. KEY: checked
c. LABEL: unchecked
d. FORMULA: none
e. SHOW: unchecked
f. EDITABLE: checked
g. REQUIRE: checked
h. INITIAL VALUE: =UNIQUEID()
i. DISPLAY NAME: none
j. DESCRIPTION: none
k. SEARCH: unchecked
l. SCAN: unchecked
m. NFC: unchecked
n. PII: unchecked
“Group name” column, ensure the following:
a. TYPE: Text
b. KEY: unchecked
c. LABEL: checked
d. FORMULA: none
e. SHOW: checked
f. EDITABLE: checked
g. REQUIRE: unchecked
h. INITIAL VALUE: none
i. DISPLAY NAME: none
j. DESCRIPTION: none
k. SEARCH: checked
l. SCAN: unchecked
m. NFC: unchecked
n. PII: unchecked
“Time” column, ensure the following:
a. TYPE: DateTime
b. KEY: unchecked
c. LABEL: unchecked
d. FORMULA: none
e. SHOW: checked
f. EDITABLE: checked
g. REQUIRE: unchecked
h. INITIAL VALUE: =NOW()
i. DISPLAY NAME: none
j. DESCRIPTION: none
k. SEARCH: unchecked
l. SCAN: unchecked
m. NFC: unchecked
n. PII: unchecked
Click “View Columns” for the TURNS table
ID column, ensure the following:
a. TYPE: Text
b. KEY: checked
c. LABEL: unchecked
d. FORMULA: none
e. SHOW: unchecked
f. EDITABLE: checked
g. REQUIRE: checked
h. INITIAL VALUE: =UNIQUEID()
i. DISPLAY NAME: none
j. DESCRIPTION: none
k. SEARCH: unchecked
l. SCAN: unchecked
m. NFC: unchecked
n. PII: unchecked
GROUPID column, ensure the following:
a. TYPE: Ref
b. KEY: unchecked
c. LABEL: checked
d. FORMULA: none
e. SHOW: checked
f. EDITABLE: checked
g. REQUIRE: checked
h. INITIAL VALUE: none
i. DISPLAY NAME: GROUP
j. DESCRIPTION: none
k. SEARCH: checked
l. SCAN: unchecked
m. NFC: unchecked
n. PII: unchecked
“Time” column, ensure the following:
a. TYPE: DateTime
b. KEY: unchecked
c. LABEL: unchecked
d. FORMULA: none
e. SHOW: checked
f. EDITABLE: checked
g. REQUIRE: unchecked
h. INITIAL VALUE: =NOW()
i. DISPLAY NAME: none
j. DESCRIPTION: none
k. SEARCH: unchecked
l. SCAN: unchecked
m. NFC: unchecked
n. PII: unchecked
Click the pencil-icon for the GROUPID column:
a. Select GROUP for the Source table.
Click “View Columns” for the TURNS table
ID column, ensure the following:
a. TYPE: Text
b. KEY: checked
c. LABEL: unchecked
d. FORMULA: none
e. SHOW: unchecked
f. EDITABLE: checked
g. REQUIRE: checked
h. INITIAL VALUE: =UNIQUEID()
i. DISPLAY NAME: none
j. DESCRIPTION: none
k. SEARCH: unchecked
l. SCAN: unchecked
m. NFC: unchecked
n. PII: unchecked
GROUPID column, ensure the following:
a. TYPE: Ref
b. KEY: unchecked
c. LABEL: checked
d. FORMULA: none
e. SHOW: checked
f. EDITABLE: checked
g. REQUIRE: checked
h. INITIAL VALUE: none
i. DISPLAY NAME: GROUP
j. DESCRIPTION: none
k. SEARCH: checked
l. SCAN: unchecked
m. NFC: unchecked
n. PII: unchecked
“Time” column, ensure the following:
a. TYPE: DateTime
b. KEY: unchecked
c. LABEL: unchecked
d. FORMULA: none
e. SHOW: checked
f. EDITABLE: checked
g. REQUIRE: unchecked
h. INITIAL VALUE: =NOW()
i. DISPLAY NAME: none
j. DESCRIPTION: none
k. SEARCH: unchecked
l. SCAN: unchecked
m. NFC: unchecked
n. PII: unchecked
Click the pencil-icon for the GROUPID column:
a. Select GROUP for the Source table.
Ignore what I am saying here. The system is giving headaches with too many posts.
Hidros,
Here is a really important step that I forgot to mention:
Me too! Google is gonna kick us off soon
We are almost done.
Here is some more:
Click the UX tab
Show the view name in the header
a. Click “Brand” on the horizontal menu bar
b. Scroll down to Header & Footer section
c. Check “Show new name in header”
Select the TURNS view
a. Set View type to Table
b. Set Position to Left most
c. No Sort
d. No Column order
Select the GROUP view
a. Set View type to Table
b. Set Position to Right most
c. Sort by Group name
d. Column order: Group name, Time
Create the GENERAL view (if it isn’t there)
a. Click “New View”
b. View name: GENERAL
c. For this data: GENERAL
d. View type: Table
e. Position: Right most
f. Sort by: GROUPID
g. No column order
Create some GROUP records
a. Click the “SAVE” button in the upper-right hand side of the page (hopefully no errors)
b. Click the GROUP menu item of App
c. Click “+” to add a record
d. Enter “GROUP 1” for the name
e. Leave the time alone
f. Click Save.
Everything should look OK
Create some GENERAL records
a. Clicj the GENERAL menu item of App
b. Click “+” to add a record
c. Select “GROUP 1” from the dropdown
d. Leave the time alone
e. Click Save.
f. Create two more “GROUP 1” records
Everything should look OK
Create a TURNS record
a. Click the TURNS meny item of App
b. Click “+” to add a record
c. Select “GROUP 1” from the dropdown
d. Leave the time alone
e. Click Save.
Everything should look OK
The final step is to create Bots, Processes and Events! Coming up soon.
Let’s create Actions first. There are 4 actions. Create them in this order:
Update this GROUP record
Update this GENERAL record
Update GROUP records associated with this TURN
Update GENERAL records associated with this GROUP
Hidros,
We need to create two Events. But first you need to set one property on each of those four Actions you created. Under the Appearance section, click the “Do not display” button.
Now for the Events:
On ADD record to TURNS table:
On UPDATE to GROUP record:
ready
User | Count |
---|---|
43 | |
29 | |
25 | |
23 | |
13 |