Cascading Deletions

As a Knack Builder, I would like the option to choose how connected records are deleted (cascade delete, prevent delete, do not delete) when a parent record is deleted so that I can maintain a clean database.

Scenario: For example, suppose I have a table called “Widgets” and another table called “Widget Parts”, and the Widgets table has a one-to-many relationship with the Widget Parts table. When I delete a widget, it should automatically delete all of the widget parts associated with that widget. As it stands, Knack will delete the widget but leave all of the parts as orphaned records.

Hello! Does anyone know if Knck has improve on this lately?

114269858211, I'm interested in looking at the code in github if you're willing to post it there.  It would definitely be easier with the code there to ask the right additional questions clarifying your implementation to see how I can apply it to my use case.  Thanks in advance!

As for this thread... there's a lot of interest from the user community, which is awesome.  I, too, would love to see this implemented!  I ask Knack Support about it and pointed them to this thread, and this was their response:

"We are hard at work at releasing a new Builder experience! The main impetus of this new Builder is to lay a foundation of an updated code base so that we can start a new cadence of continuous improvements and feature releases.  There definitely are some new features & updates like side-by-side views, import updates, view previews, updated equation editor, etc. You can see more details on this on our page about the new Builder. That said, the ability to perform cascade deletions isn't included in this new Builder. Let me know if there are other new Builder questions I can answer for you, thanks!"
 
So, for now (as of this writing), nothing is being solved for this feature enhancement in the short term.

Urgh.. still looks like hell... if you like guys, I can create a little github with a library in it, and some examples.  

Correct!   it isn't... you have to loop through all of them.   Luckily Ajax will let you issue multiple queries at the same time, there's a slight trick in waiting for them all to return, but that's pretty easily handled.   

What I do is the following... 

issue a Get for all records matching the filter,

   on success... iterate through the returned records and issue the delete one at a time.  

    wait for all the deletes to finish, then proceed to the next step in the chain of deletes.   

 

The trick is to use deferred objects and promises to go get the records.  Going to try to paste the code in and format it a little better.  

 

function delete_kit_lines (args) {
   // Create a deferred object
   var def = new $.Deferred();
   // Where can I delete?
   var api_url = "scene_198/views/view_464/records"  //There's a library I wrote below which handles the ajax calls
   // I've got to wait till I'm completely done deleteing kit_lines before proceding. 
   //  So I've got to store all the promises in an array.. we'll call it promises
   var promises = [];
   // Tell the people what's going on. (basically change the span I added to tell them why they're waiting.
   $(args['status_location']).replaceWith('<span class="confirm_view_458" style="color:green">Deleting Kit...</span>')
   // OK for each thing I need to delete.... 
   $.each(args['kit_lines'], function (index, kit_line) {
         var url = api_url + "/" + kit_line   // This uses the url above and appends the kit.id  basically formatting the delete record
         console.log("Deleting Kit Line: " + kit_line) // because I like to debug.
         promises.push (BlackHall.del_knack_records(url,args))   // push the promise onto the stack. 
    })
    // Here's the magic of waiting till everything in my promises array is done.
    // This waits until all the delete promises are done and then in resolves *the promise this function created at the beginning
     $.when.apply($, promises).then( function () { def.resolve(args) });  
     return def.promise()

}

the magical BlackHall.del_knack_record... is ... 

var BlackHall = {
.....
.....
del_knack_records: function (url, args) {
    url = "https://api.knack.com/v1/pages/" + url;
    var def = new $.Deferred();   // Create a deferred object.   When ajax is successful, return stuff.
    console.log(url)  
    $.ajax({
       url: url,
       type: "DELETE",
       headers: {"X-Knack-Application-Id":Knack.application_id,  //I'd like to point out this is different than the docs.
                       "Authorization":Knack.getUserToken(),              // But we use multiple sites and this library needed to be
                       "X-Knack-REST-API-Key": "knack"                    // cross functional.  So I went looking..... 
     },
     success: function(data, textStatus, xhr) {
                   def.resolve(args);  // only resolve the promise when ajax is successful.  Yes... I know this is redundant and I
                                                 // could just return the promise rendered by ajax, but I was just getting the hang of promises.
      },
      error: function(xhr) {
                   console.log("Error Status: "+xhr.status+" "+xhr.statusText);
                   return(0);
       }
     });
   return def.promise();
},

 

......

Hopefully this will look better

 

I haven't tried this but I've been told that is is not currently possible: use the API to delete (or update) multiple rows by specifying a filter rather than a specific record id.

For example:

DELETE (or PUT)

https://api.knack.com/v1/objects/object_1/records?filters=[{"field":"field_12", "operator":"is", "value":"574ec622dadf69d041755605"}]

Wow, there's a lot of code there for something that should really be so simple!

I do appreciate that Javascript is a useful add on for Knack - but the more I try the more I hate the language - it's just too complex to be married up with a No Code tool like this - and the examples in the documentation assume too much existing knowledge.

Knack needs its own macro language - I have been pretty proficient with VBA in Access in the past, more recently with Macros and Data Macros in Access Web Apps and also, recently with Deluge in Zoho - all off these are relatively comprehensible whereas Javascript is not in my view.

In many ways Knack is a very cool system but the jump to Javascript is way too much without a more amenable option in between.

Here's how we handled cascading deletes.   Magic of javascript promises.  

 

$(document).on('knack-view-render.view_458', function(event, view, record) {
// Replace the delete trashcan with our own trashcan so as to override the delete button.
$('#view_458 table td .delete').replaceWith("<a class='confirm_view_458' ><span><i class='fa fa-trash-o' style=''></i></span></a>");
// Add in our own 'Are you sure' to our replacement button.
$('.confirm_view_458').click(function() {
if (window.confirm("Are you sure?") ) {
// Set some variables.... We're going to pass the args object all the way down the chain
var args = {}
args['view'] = 'view_458' // we need to know what to refresh at the end.
args['kit_id'] = this.closest('tr').id // Get the ID of the kit we're looking at.
args['status_location'] = '#' + args['kit_id'] + ' td .confirm_view_458'; //This is the trashcan location... we'll use that for status

Knack.showSpinner;
//should be readable. Each function feeds the data from the previous function into it, and passes args which we'll use to store results
get_kit_line_items(args)
.then( get_each_picked )
.then( delete_picked )
.then( delete_kit_lines)
.then( delete_kit )
.then( refresh_view )
}
});

Here's some of the sample functions... The trick is to create a return promise on every function.  And only resolve that deferred object when the function is done.

function get_kit_line_items (args) {
// Create the Magical promise
var def = new $.Deferred();
// Set the URL where we can find attached records. (This is easier than using a filter, but you have to create your view right)
var url = "scene_197/views/view_466/records?master-kit-instance-details_id=" + args['kit_id']
// Tell the people what we're doing in the status location
$(args['status_location']).replaceWith('<span class="confirm_view_458" style="color:green">Finding Kit Lines and Picked Inventory...</span>')
// Fancy wrapper for ajax. Resolves only on success.
BlackHall.get_knack_records(url,args)
.done(function (data,args) {
// Now that I've got my data, I can resolve *this functions promise and send along the needed info.
def.resolve(data,args)
})
return def.promise()
}

 

Another sample.  This one with multiple calls...

function delete_kit_lines (args) {
// Gimme a promise
var def = new $.Deferred();
// Where can I delete?
var api_url = "scene_198/views/view_464/records"
// I've got to wait till I'm completely done deleteing kit_lines before proceding.
var promises = [];
// Tell the people what's going on.
$(args['status_location']).replaceWith('<span class="confirm_view_458" style="color:green">Deleting Kit...</span>')
$.each(args['kit_lines'], function (index, kit_line) {
var url = api_url + "/" + kit_line
console.log("Deleting Kit Line: " + kit_line)
promises.push (BlackHall.del_knack_records(url,args))
})
// Here's the magic of waiting till everything in my promises array is done.
$.when.apply($, promises).then( function () { def.resolve(args) });
return def.promise()

}

 

Now... Since I have the id's of all the records I want to delete, I don't really need to wait each time before deleting things, but I'm trying to catch errors, and if anything fails to delete for some reason, I can cancel the stack and leave the object in a mostly complete state which is better than orphaned records.   

 

 

Personally, I think this is essential - there should be 3 options when you create a connection in builder:

  1. Prevent Delete - this would prevent the deletion of a parent record if child records exist - for example prevent the deletion of a training course if people are booked onto it.
  2. Cascade Delete - which would simply delete all child records when a parent is deleted.
  3. Ignore - this is the current option (i.e. do nothing.

Given that, as far as I can see, there are now ways of implementing this I consider his to be an urgent requirement!

Francesco Guiducci there´s no such option...


I think what there should be is an option on tasks to delete every orphan record.


Also... an option on runtime record deletion that also deletes (or not) connected records, this really depends on the scenario... sometimes it's logical to do that... sometimes is does not.

This option should be on builder.... when you make a connection field on any given record, there should be an option there to define how deletions should run.

Yes, would be very useful.

+1 for sure

On second thought, Francesco Guiducci, how did you create a daily task to Delete a record?  I can only find update…thanks!

 

Yes!  Me too!  Would be a useful option for me.  Thanks for the workaround idea Francesco Guiducci!  That will work for now.

 

Yes +1, I would also like to have this option in the future.

Grüße

André

yes it would be very useful!

 A workaround I use is a daily task to delete the "Widgets parts" with no "Widgets" relationship.

ciao,

Francesco