Use a reference action to delete all rows in another table

[maurice123]

Hi,

I am trying to truncate / delete all rows in a table using an action from another table. I.e., I have table A and once an action is triggered in table A all rows in table B should get deleted.

How would I implement this? I am struggling with the "Referenced rows" expression in particular as it is unclear to me what exactly the expression has to return (a list of row numbers, IDs, true/falses?).

Thank you!

[dbaum · Accepted solution]

---

@maurice123 wrote:

what exactly the expression has to return (a list of row numbers, IDs, true/falses?)

---

A list of values from the referenced table's key column.

[dbaum · Accepted solution]

---

@maurice123 wrote:

what exactly the expression has to return (a list of row numbers, IDs, true/falses?)

---

A list of values from the referenced table's key column.

[Sirfyaad · Accepted solution]

Certainly! Here's an example of using the REF_ROWS function with a condition in AppSheet behavior:

Let's consider two tables: "Projects" and "Tasks." Each project in the "Projects" table has multiple associated tasks in the "Tasks" table. You want to define a behavior rule that triggers when a new task is added to a project, but only if the project is in a specific status, such as "Active."

Here's how you can set it up:

1. In the "Projects" table, create a column called "Status" to track the status of each project. Set the column type to "Enum" and define the status values, including "Active."

2. In the "Tasks" table, create a behavior rule. Navigate to the "Behavior" tab in the table configuration.

3. Click on "Add a new behavior rule" and provide a suitable name for the rule.

4. Set the condition: In the "For this data change" section, select "Adds Only" to trigger the rule when a new task is added.

5. Specify the action: In the "Do this" section, choose "Data: Execute an action on a set of rows." Select the action you want to perform on the related project(s) when a new task is added.

6. Set the target rows: In the "Referenced Rows" section, choose "Referenced Rows (using REF_ROWS())" as the target rows.

7. Configure the REF_ROWS function: In the "Referenced Table" field, select the "Projects" table. In the "Referenced Rows Expression" field, use the following expression:

SELECT(Projects[ProjectID], [Status] = "Active")

This expression uses the SELECT function to retrieve the ProjectID values from the "Projects" table where the Status is "Active."

8. Save the behavior rule, and now, whenever a new task is added to a project, the action specified in the rule will only be triggered if the project is in the "Active" status.

Remember to adjust the column names and expressions based on your specific table structure and conditions.

By using the REF_ROWS function with a condition, you can define behavior rules that selectively trigger actions based on related row data in AppSheet.

[MultiTech]

ChatGPT???

[maurice123]

Thank you, this is super helpful!

[MultiTech]

Unfortunately (fortunately??? 😋) the robots have yet to learn about efficient AppSheet building, but I got this response back from Appster (an AppSheet Ai I'm working on) and thought I would share it - as it's actually pretty good.

---------------------------------------------------------------------------------------------------------------------------

To accomplish your goal of deleting all rows in table B when an action is triggered in table A, you can follow these steps:

1. First, create a slice for Table B called "All_Table_B_Rows" where the slice filter condition is just `TRUE`, indicating you want all rows from table B in this slice.

2. Next, create a grouped action in Table A that has multiple steps.
a. First step is a "Data: Execute an action on a set of rows" action.
- For the "Referenced Table", choose your "Table B".
- For the "Referenced Rows" expression, enter `All_Table_B_Rows[KeyColumn]`. Replace "KeyColumn" with the name of the key column in Table B (usually the first column, e.g. ID).
- For the "Referenced Action", choose the pre-defined "Delete" system action available for Table B.

3. Finally, attach the grouped action you created in Table A to a button, a Form Saved event, or any other event.

When the grouped action is triggered in Table A, it will execute the "Delete" action on all rows in Table B based on the defined slice.

Please keep in mind that deleting rows using actions might not be the most efficient way if you are dealing with a large number of rows. In that case, using server-side automation or a script with the AppSheet API to delete rows might be more efficient.

---------------------------------------------------------------------------------------------------------------------------

But are you really trying to delete EVERY record in the table, or just the related ones? 

• Because if it's just the related ones, if you've got references setup correctly it's simply the [Related Whatever] VC on the parent side of the ref.

Cheers!  Hope it helps.

[JP-AN]

Many thanks, this brins me some light! have a nice day/life!