How do I delete all corresponding child records simultaneous to deleting the parent records?

  • 1
  • 1
  • Question
  • Updated 3 years ago
  • Answered

How do I delete all corresponding child records simultaneous to deleting the parent record? E.g., I want to delete an Appointment that is the parent records. How do I do that so it also deletes the corresponding Sales, Payments, and Claims records without accessing each table and deleting the individual record?

Photo of Joy

Joy

  • 0 Points

Posted 4 years ago

  • 1
  • 1
Photo of Drew

Drew

  • 492 Points 250 badge 2x thumb
There is no native way to do this within QB in one motion.  That being said, I recall Dan Diebolt showing some example that uses script to accomplish.  It's a fairly common question / need.    

The most common thing that people will do is create a report in each of the child tables that filters the records based on 'related parent' = blank.  Those reports will show all the child records without parents i.e. the parent record has been deleted.  You can then from the report choose to 'delete these records' which will delete them all.

I used to do this as kind of a daily / weekly thing.  You can set up a notification to send you the report daily / weekly, whatever kind of time frame you want.  

Now I typically do this via MS SQL server and QUNECT via a scheduled SP, but that obviously isn't an option for everyone.  
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
Drew, this was useful tip.  How  would you do the reverse of this where a child was deleted but master is still there? Could this be record count of child?
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,224 Points 20k badge 2x thumb
I could be done with script "in one motion" but should require user confirmation to delete the parent record. As you note there could be sibling child records as well as parent records involved in other relationships which should not be deleted without confirmation.
Photo of Drew

Drew

  • 492 Points 250 badge 2x thumb
Arshad - My understanding of what you are after:  If a parent record no longer has child records, you want to delete the parent record.  

If that is the case, yes - you would use the summary field option in the relationship to count the number of child records and have the report of parent records filtered where # of child records = 0.

I also do this, albeit for different reasons.  To Dans point, if you have multiple child tables off of a parent record, what I did was a summary field for each child table that counted the number of records for that child table, and then a formula field which added all summaries up.  So as an example, if you have three child tables for a parent table called tables A, B and C, you would have three summary fields like 'Number of A Records', Number of B Records', 'Number of C Records'.  For simplicity of reporting i'd then create a formula field called 'Total Number of Child Records' which added those three values up.  Then your report would only have one filter criteria - show all parent records where 'Total Number of Child Records' = 0.
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
Drew, thank you so much.  I figured out but then your solution to deal with many child tables is great.  I will put than place.

From experience in this forum, I have seen Dan comes up great solutions with the use of scripts and undoubtedly that would be an elegant solution. But unfortunately,  there are a lot of people like me who have no idea how to put such solution to practice.  This would be great service to this forum if a resource or guides area is established to up skill people like me.  I heard about terms such jquery, IOL etc but find it very difficult to learn or use these solutions.  Just a thought!
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,224 Points 20k badge 2x thumb
>But unfortunately,  there are a lot of people like me who have no idea how to put such solution to practice.  This would be great service to this forum if a resource or guides area is established to up skill people like me.

Just ask a question in a new thread. It is imperative that everyone learns how to use JavaScript with QuickBase so we can move on to more important applications like analytics, visualizations, machine learning, ethereum all of which use JavaScript. Also, there are going to be massive enhancements in JavaScript itself as new features land in browsers in 2017. Let's Make JavaScript Great Again!
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
Thanks Dan. Do you have a script I use 'in one motion'?