Bulk Action - How to Delete Rows on Multiple Tables?

[wheaties]

I am wanting to delete rows in multiple tables that's triggered by one bulk action.  After researching it seems this is not possible in a "conventional" way since the available options make it so you have to navigate to another table first before deleting rows. The problem is the app navigates to the table, it stops the other actions in the grouped action.

Does anyone know a work around for this?

Side Note to Devs:  We are given the action option of "add rows in another table" (this in essence allows you to bulk create rows in multiple tables no problem), however there is not a action for "delete rows in another table" (which would accomplish what I am after) - how come?

[MultiTech · Accepted solution]

You could be facing these problems due to the fact that you're doing things non-standard.  Most systems would simply expose the ref column and allow people to interact with that, instead of kicking off an automation to connecting the after the fact.

One of the beauties of app sheet is that you can do anything, just about any way that you want.  However, when you stray away from the yellow brick road you encounter problems.

Generally speaking though... if things are connected together properly using refs, you should be able to simple call the delete action on the child records to accomplish the delete.

It's all about having those [Related Whatever] VCs on the parent; it's that list that's giving you all the child record values

[scott192]

There are two things I would suggest for this problem.

The first and easiest can be done if your data structure is related to your other tables with a Ref, following the 'normal' Master->Child->Child->Grandchild etc system where each other table further down the chain is related to the previous table.  If this is the case, then ticking the 'Is a part of' option when setting the Ref will cascade all the deletes to the other tables once the Master record is deleted.  This is the preferred way.

The second, harder way is to investigate  the Appsheet API

[JPAlpano]

Or an automation bot that watches delete updates on the main table, and calls an action to delete the same Keys on the other table/s.

[wheaties]

@JPAlpano - The only automation process I can see working is running a Script.  I created a script, however I still can't get the related rows in the child tables (Accessories, Wall Art, and Rugs) to delete.  It's set to run when there is a deleted row in the parent table.  Here is my script - feel free to take a look and see if I went wrong somewhere.  

[wheaties]

@scott192 - Thank you for pointing this out, I was hoping this would solve my issues, but alas I still have the same problem.  I have three child tables that are referencing the parent table.  I set the child table key columns as Ref and checked the "Is a part of?"  box as you instructed, however when when the parent row is deleted, the related rows in only one of the referenced child tables is deleted, not the other two tables.

[scott192]

I know this might seem like an obvious thing to check, but are the table permissions of your other child tables set to allow deletes?

[wheaties]

Child tables are set to allow Updates, Adds, and Delete.  That was a great thought to check though!

Child Table 1 ("Accessories")

Child Table 2 ("Rugs")

Child Table 3 ("Wall Art")

[scott192]

I'm a but stumped by this one.

If each of those child tables has a field that is a Ref to your Master table and each Ref is set to be 'Is A Part Of', then I cannot understand why it won't delete those child table records on deletion of the master record.

[wheaties]

I'm in the same place, I've triple checked to make sure it's all setup this way and in my mind your solutions makes the most sense and "should" work.  

@JPAlpano recommended I try an automation.  I am in the process of coding a script to see if it will work.  Will keep you posted.

[scott192]

Only other place I can think of to look is in the Audit History and see if there are any red messages?

[wheaties]

Audit Log shows no errors related to this 😔. 

[scott192]

Just another follow up, there is a similar problem with a solution here ... might be worth a look?

[wheaties]

Well I've made progress.  I removed the child table that was deleting properly to test out the other two that were not...the other two child tables are still not deleting properly.  This tells me it's something with those two child tables specifically.  I tried deleting and recreating them (based off the recommended post you provided), but they still don't want to delete properly.  I will keep trudging!

[Steve]

Are your Ref values in the parent correct?

[wheaties]

@Steve - Pardon my ignorance, how should they be setup?

The parent table(Locations) has a column [Project Name] set as a Text type - (see image below).  The child tables all have a column named [Project Name] that is set as a Ref type to the parent table.

[scott192]

You are using a value that somebody types in to set the unique key of your master record?

How does this value get put into the child records?  Do they type it in there as well?

[wheaties]

For the parent table, the user opens a form and manually types in the project name.  After they save the form, a group action triggers that adds a row to each of the child tables and automatically sets the project name as the one the user entered in the parent table.

[scott192]

And, how is the Key field of each of your child tables set?

[wheaties]

@scott192 - Here are the screenshot of the actions to create rows in child tables.  The key column in each child table is Project Name.  The formula I am using to set Project Name is [_THISROW].[Project Name].  All these create actions work perfectly.  Upon testing, only the Wall Art child row is deleting properly with the parent.  The rows from Accessories and Rugs will not delete even if the other sheets are removed.

Action 1:  Group (triggered when form is saved from parent table)

Action 2:  Create Accessory Page

Action 3:  Create Wall Art Page

Action 2:  Create Rug Page

[scott192]

---

@wheaties wrote:

The key column in each child table is Project Name.

---

So you don't have a separate key on each child table then?  You are using the Ref back to the master as a Key?

Hmmm....I've never heard of doing data like this.  I would advise you to add a new column to each of your child tables and name them something like ChildID.  Regenerate the structure and use uniqueid() as the Initial Value in your newly added column.  Make this the Key of your child table and set the Label to be the Project Name field.  Keep the project name field as it is with a Ref back to the master.

See if that works now!

[wheaties]

This was a great idea, but same result.  Only the child row from Wall Art is deleted while the Accessories and Rugs remain.  I added a whole new "Test" table and slowly added identical virtual columns to see if that was tripping it up, but it was not - the rows of the new Test table would delete fine.  I then deleted the initial table (Accessories) and renamed the Test table and source sheet to Accessories, and then poof the child rows stopped deleting again...it seems like the name of the Accessories and Rugs tables are causing issues, but I can't pinpoint where.

[MultiTech · Accepted solution]

You could be facing these problems due to the fact that you're doing things non-standard.  Most systems would simply expose the ref column and allow people to interact with that, instead of kicking off an automation to connecting the after the fact.

One of the beauties of app sheet is that you can do anything, just about any way that you want.  However, when you stray away from the yellow brick road you encounter problems.

Generally speaking though... if things are connected together properly using refs, you should be able to simple call the delete action on the child records to accomplish the delete.

It's all about having those [Related Whatever] VCs on the parent; it's that list that's giving you all the child record values

[wheaties]

Hi Multitech, I love Appsheet because there are so many work arounds in case you go off the straight path.  However, in this case, I don't feel like I have strayed too far off since I am using a standard action to create rows in child tables and there is standard function for those child rows to be deleted through the Ref type...I just think there is a problem with my Accessories and Rug child tables that's not allowing the rows to be deleted with the parent.  The Wall Art child rows delete fine when the parent is deleted.

[wheaties · Accepted solution]

SOLVED!!

@MultiTech you beautiful beast!  You prompted me to look at the [Related Whatever] columns in the parent table.  I deleted them for each respective child table, then saved and let the system recreate them automatically and now all the child rows delete properly.

Seems like the link between the Related VC and the child table got messed up internally even though they were showing correctly.  Letting the system recreate them restored the correct relation.

THANK YOU everyone for your time and patience!

[Steve]

Action 1

• For a record of this table: (child table)
• Do this: Data: delete this row
• Needs confirmation?: OFF

Action 2

• For a record of this table: (parent table)
• Do this: Data: execute an action on a set of rows
• Referenced Table: (child table)
• Referenced Rows: LIST(child-row)
• Referenced Action: (action 1)

Action 3

• For a record of this table: (parent table)
• Do this: Data: delete this row
• Needs confirmation?: OFF

Action 4

• For a record of this table: (parent table)
• Do this: Grouped: execute a sequence of actions
• Actions:
  • (action 2)
  • (action 3)

Action 4 is the action you present to the user; the others should be hidden.

[MultiTech]

#BossMode

[wheaties]

@Steve - Thanks for the reply.  This is actually the same structure of what I currently have, however I have two additional actions in the the grouped action that are identical to (action 2) that point to other sheets.

In essence, I want the group action to delete the current row in the active sheet, then delete the related rows in three other sheets.  The structure you provide works if I am only deleting rows from one other sheet.  The group action stops after that and wont delete the related rows in the other two related sheets.  Hopefully that makes sense?

[Peter_Kalu]

Do you know why this solution only seems to work on records created by individual/account initiating the action? That is, if I click on the action, it only deletes record(s) that I created, but ignores ones created by others - even though the list expression is supposed to filter all matching records.

[MultiTech]

Th

---

@wheaties wrote:

I want the group action to delete the current row in the active sheet, then delete the related rows in three other sheets

---

This is an order of operations problem; you CAN'T do things in that order.

You have to delete the children first, then delete the parent.

[MultiTech]

---

@wheaties wrote:

Upon testing, only the Wall Art child row is deleting properly with the parent.  The rows from Accessories and Rugs will not delete even if the other sheets are removed

---

How are you accomplishing the delete?  With a bot?

[wheaties]

No bot, just using the default "Delete" action on the parent row.  The child tables have a "Project Name" column that is set to Ref the "Project Name" in the parent table.

[MultiTech]

---

@wheaties wrote:

@MultiTech you beautiful beast!

---

Glad you got it solved!