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

Hidros
Participant II

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

0 48 8,903
48 REPLIES 48

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

Please elaborate if you are looking for something else.

Brian_Morgan
Participant IV

@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

Action detail:

Events overview:

Event detail:

Bots:

Processes overview:

Process detail:

Log table results:
3X_8_c_8c38b2e6b514058855d93559dd13b4838b5cfbf1.jpeg

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! 

Brian_Morgan
Participant IV

Hidros, what do you think of the following:

3X_d_c_dca0cbdc549927742750072aea255c0dca482006.png

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

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.

3X_7_a_7ab5342aef6e3fa2f393ee3cec784a60f7c83b21.png

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_c_8_c88482281b05e3b15d37f8cc7035d4de4fe4f4aa.png

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,

3X_d_6_d6b693e99f64847c2fe91e7fef33fa57cc286761.png

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

Brian_Morgan
Participant IV

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

Hidros
Participant II

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.

Hidros
Participant II

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โ€.

Hidros
Participant II

3X_f_a_fa26382bdb267e7b14e7cb6b113520580fdb0edc.png

3X_0_e_0e8f925e1e21c6fb4646259352bc18de9dbdca1a.png

Brian_Morgan
Participant IV

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.

Brian_Morgan
Participant IV

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

Hidros
Participant II

Ready, its done

Next:

3X_3_1_31de3388e88b458de282c2b7ca5d179d241b5b8e.jpeg

Brian_Morgan
Participant IV

Brian_Morgan
Participant IV
  1. Click โ€œView Columnsโ€ for the GROUP table

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

  3. โ€œ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

  4. โ€œ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

  1. Click โ€œView Columnsโ€ for the TURNS table

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

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

  4. โ€œ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

  5. Click the pencil-icon for the GROUPID column:
    a. Select GROUP for the Source table.

  1. Click โ€œView Columnsโ€ for the TURNS table

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

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

  4. โ€œ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

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

Brian_Morgan
Participant IV

Hidros,

Here is a really important step that I forgot to mention:

  1. Go to the Data tab.
  2. Open the Group table
  3. View columns
  4. You should see the following. If you donโ€™t then click the โ€œRegenerate Structureโ€ button. That will create the two Virtual Columns: Related TURNS and Related GENERAL.

Brian_Morgan
Participant IV

Me too! Google is gonna kick us off soon

We are almost done.

Here is some more:

Click the UX tab

  1. 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โ€

  2. Select the TURNS view
    a. Set View type to Table
    b. Set Position to Left most
    c. No Sort
    d. No Column order

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

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

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

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

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

  1. Update this GROUP record

  2. Update this GENERAL record

  3. Update GROUP records associated with this TURN

  4. 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:

  1. On ADD record to TURNS table:

  2. On UPDATE to GROUP record:

ready

Top Labels in this Space