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:
- 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.
- Cascade Delete - which would simply delete all child records when a parent is deleted.
- 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!
1 Like
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.
2 Likes
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
Just adding my name to the list of folks who want this feature.
I ended up building my own 
but it works GREAT. Basically here is the concept I used:
Inside a promise:
- Make an array for each connected table that has records to delete from (8 in my case)
- GET from each of the connected tables the IDs which need to be deleted and add to the appropriate array.
- When each of those GET ajax calls are finished, increment a variable TablesProcessed
- Don’t resolve the promise until TablesProcessed === 8.
The order of these doesn’t matter, as long as they all get done before moving on.
After promise is fulfilled:
- Run a DELETE ajax statement for each of the 8 tables, which loops through each record in the corresponding array and provides the record id to delete.
This may be slightly faster because they are all running at the same time (as much as Knack allows).
*The order of deletions doesn’t matter either, because I already have the ids in an array. So even if I code it to delete child records and then the parents, it may finish the parents first and then the connection is gone. *
This took me a really long time to figure out (I am not good at async/synch operations and didn’t realize that a DELETE will not work on multiple records with a filter, rather you must delete one record at a time). I used a bunch of test records and at first just ran it with the GET part and then console.logged what I would delete so that I could make sure it was picking up all the records.
Now this has been resurrected I thought I would re-iterate my comment from 5 years ago that I consider this an essential feature for any database system!
HOWEVER, I have implemented this in Make (formerly Integromat) several times. This keeps the solution a NO CODE solution.
The logic would be something like:
-
Set a field value to indicate deletion in the parent record.
-
Trigger make or have it regularly search for records to delete.
-
Use a Router in Make with a path for deleting records from each subtable, search for the records linked to the parent and delete them.
-
Add a final path to the router (make sure it is the last one you add and then it will be processed last) and delete the parent record.
This most simple approach relies on the fact that router paths are processed in order of creation - it is also possible to use variables in Make to determine when all sub records are deleted and when they are to delete the parent - but in many cases this is not necessary.
I have lots of examples of using Make to automate Knack in my blog:
Maybe I will do a specific blog post explaining this specifically…
Hope this helps!
Julian
@RichTrice46505 Thank you for the code snippets. I finally got my cascading deletion to work thanks to your example.
Julia