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 |
---|---|
41 | |
31 | |
29 | |
16 | |
14 |