Delete child rows when deleting a parent row (via multiple relation)

Hi there,

In my app, users can create categories, then subcategories, then subsubcategories and so on (5 levels).

But they can also delete any category or subcategory, at all level.

I am using unique id’s in order to ensure the link between these items remains even if the user change the item name. But, in case of item deletion, the child of this item remain alone, most in the dark of my sheet, and are consuming rows.

I would need your advice on the best way to manage this issue. I thank about creating an “orphans items” sheet were all lost items would be stored, using a formula or a Google script based on unique id’s manipulation.

What would be your advice, or your experience with such behavior and issue?

I agree that any child of a category can not be automatically deleted, the app can not (must not) delete user data without the consentment of the users.

Many thanks for any advice or quickwin!

Cheers,
Christophe

Thinking out loud:

In the Categories sheet add a array-Formula: There must be a column which shows “yes” IF category has children

In glide show a tab to users with a filter. Only show categories where “children column” is empty.
When a user clicks these no-child-categories, the deletion option is enabled.

Another tab with the oposite filtering where users can NOT delete. Only show categories where “children column” is NOT empty.

@Sidney_Kunst I don’t think you can control the appearance of the Delete button that way. I know you said you were thinking out loud, so I’m guessing that you didn’t try it.

@Christophe_HK The only way I can think of would be to run a script on a nightly time based trigger, that would clean up these orphaned rows.

Hi guys,

Many thanks for your feedback. I am going to check the most suitable. I think a good alternative is a mix of the two :it would be to isolate orphans items in a dedicated list, make them editable with a “delete?” checkbox, and then a script would be responsible in deleting all to-be-deleted items, nightly.

linking similar feature request for housekeeping:
https://community.glideapps.com/t/option-to-delete-all-child-items/

I could use this feature as well…creating custom carts with cart items. If I delete a cart (row in the cart sheet), I’d want to also delete items in that cart (all related rows in the cart items sheet) in order to keep the sheet nice and tidy without any orphaned child items.

2 Likes

Another great feature idea well overdue.

Hi @Christophe_HK, I have more or less the same use case than you, and struggle with it.

My user can a Create Category > x Sub categories > y Questions > y Answers.
At the end I will have to perform calculations on Answers.

1/ I wonder if my Tab structure is relevant?

  • Tab 1: user | CATEGORY | autonb-cat
  • Tab 2: user | category | autonb-cat | SUB CATEGORY | autonb-sub
  • Tab 3: user | category | autonb-cat | subcategory | autonb-sub | QUESTIONS | autonb-question | USER SPECIFIC ANSWER
  • Tab 4 (in progress): copy-paste of user, category … user specific answers, with an aim to perform calculations & create charts

2/ I struggle to enable the user to modify the ‘Category’ or ‘Sub category’ name and report it into each Tab.
My so so workaround is to have 4 columns in each Tab (autonb, input name, initial name, display name).

For instance for ‘Category’, the columns are:
Autonb (doesn’t change) | Category input name (create or modify) | Initial name (copy paste of the ‘created’ input but not of the ‘modified’ one) | Display name (if then else Input is different of Initial).

Thanks in advance for your advice

For something like this, I would store something like an ID for a Category in the other sheets, instead of a category name. ID’s will never change, but you can can change the name all you want. Then all you need is a relation and lookup to get the actual name if you want to see it in other sheets. I do this with student names. I only have one sheet with a student name and a Row ID. The row id is what I save in several other sheets. That way I can change or modify a student name without breaking the link to other sheets. If I need the student’s name elsewhere, I just create a relation and lookup the pull in the student name from the student sheet.

1 Like

Pfff … too simple, it’s annoying! Thank you @Jeff_Hager

(I just have to break-rebuild everything I did… and I’ll be able to finalize step 1 of this 600 columns for a form? - #6 by Jeff_Hager, on-going thanks to you also)

1 Like

I created two tables. Orders and OrderItems and both have relationship columns with each other but they are single match relationships even though an Order might have more then one item. Then on the button to delete you add multiple entries to delete a row and select the relOrderItems and one by one it deletes them.
This video shows how I did it… Sorry don’t know how to embed videos.
(https://img1.blogblog.com/img/video_object.png)

Makes sense…it’s not a “delete all children record” button but rather “delete first/next child record” button that you have to press many times…Not what I’m after though…I want a single button press to delete all child rows.

Similarly, I want the ability to do a set column via multiple relation as described here:
Ability to "Set columns" and "Delete Rows" via multiple relations

1 Like