Checkbox script to update a 'many connection' field, get the current records then add to them

//This script uses the standard community add checkboxes script available in other threads on this forum

$(document).on('knack-view-render.view_' + 'your_view', function(event, view, record) {
var url = window.location.href.split('/');
var UrlBase = 'https://api.knack.com/v1/pages/page_XX/views/view_XX/records/';
var target = url[url.length - 2]; // get target record.id from url
var record_ids = [];
var webmerge_ids = [];

addCheckboxes(view);


var get_url = UrlBase + target;

// event for the button when clicked
$('#update').on('click', function() {
//var webmerge_ids = [];
alert('Updating your order. This may take a second. Please click okay to continue');

//getting existing connected fields
$.ajax({
url: get_url,
type: 'GET',
headers: {
Authorization: Knack.getUserToken(),
'X-Knack-Application-Id': Knack.application_id,
'Content-Type': 'application/json'
},
success: function(data) {
console.log('Got records!');
//console.log("data is: ", data);

var myvar = data.field_62_raw;

//console.log('field 62 is :', myvar);
myvar = getIfNotSet(myvar);

//This function resolves the error produced if the record has no records to get

function getIfNotSet(myvar) {
if (typeof myvar === 'undefined') {
return [];
}
else {
return myvar;
}
}

//getting new selections

$('#' + view.key + ' tbody input[type=checkbox]:checked').each(function() {
myvar.push(
$(this)
.closest('tr')
.attr('id')
); // record id
});

//combine new and old records

webmerge_ids = myvar.slice();

Knack.showSpinner();

// set data variable so that field XX will contain the new values
var data = {
field_XX: webmerge_ids
};

//delay prevents hitting the api limit

var myDelay = 100;

$(function() {
setTimeout(updateRecords(record_ids.shift(), record_ids, data), myDelay);
});

function updateRecords(id, records, data, target) {
var target2 = url[url.length - 2]; //getting record ID from the url. You could do this other ways as well
var puturl = 'https://api.knack.com/v1/pages/scene_XX/views/view_XX/records/' + target2;

var headers = {
Authorization: Knack.getUserToken(),
'X-Knack-Application-Id': Knack.application_id,
'Content-Type': 'application/json'
};

console.log('making ajax put call');

$.ajax({
//url: 'https://api.knackhq.com/v1/objects/object_XX/records/' + id,
url: puturl,
type: 'PUT',
headers: {
'Authorization': Knack.getUserToken(),
'X-Knack-Application-Id': Knack.application_id,
'Content-Type': 'application/json'
},
data: JSON.stringify(data),
success: function(response) {
Knack.hideSpinner();
//alert('Success');
location.reload();
},
error: function(jqXHR, exception) {
var msg = '';
if (jqXHR.status === 0) {
msg = 'No connection. Verify Network.';
} else if (jqXHR.status == 404) {
msg = 'Requested page not found. [404]';
} else if (jqXHR.status == 403) {

msg = 'Token or permissions Error [403]';
} else if (jqXHR.status == 500) {
msg = 'Internal Server Error [500].';
} else if (exception === 'parsererror') {
msg = 'Requested JSON parse failed.';
} else if (exception === 'timeout') {
msg = 'Time out error.';
} else if (exception === 'abort') {
msg = 'Ajax request aborted.';
} else {
msg = 'Uncaught Error.' + jqXHR.responseText;
}
console.log(msg);
console.log('data is: ', data);
}

});
}
}
}

);
});
});

Hi Justin, Steve,

sounds like you've solved a problem similar to the one I want to fix, but I haven't got the JS skills to do it. Would either of you be willing to take a look at what I want to do, and maybe help out (this is important to my business, so I would be happy to consider it 'not for free'...)

Neil (neilparkin3 at gmail.com)

I'll be amazed if you do! :-) Having said that, maybe the "async: false," Ajax trick could come in useful to you, if you hadn't already been aware of it.

Beware of the method I used to iterate through the retrieved JSON object though. The way my tables are configured, I was getting the ID, plus a description in the JSON object that is the result of extracting the Raw value of the multi-select field from the JSON object of the retrieved record. I needed just the IDs out of the multi-select connector field. I found various methods for iterating through JSON objects on the web and it seems that some of them only work in certain situations. I struck lucky and found a simple method that worked on my data, but it may not work for JSON objects with other types of content.

On another topic I still don't quite get the Knack.showSpinner(); functionality. I'd tried putting it all over the place but I still can't seem to get it to start spinning as soon as I'd like it to. A minor issue, however, and I really cant afford to waste any more time on whistles and bells, so unless there is a really simple explanation, I think I'll put up with it as is. Thanks again for your help.

Cheers Steve

Nice Work! I'm going to pick up some tricks going through this :)

Hi Justin,

Well, I finally achieved what I needed to do, but it hasn't been without a real struggle. Because I needed to read in and update a record in two tables with the same record IDs selected on the table view (long story, so wont bore you with the details about that) I thought the easiest approach would be to define a function and call it twice. I only discovered today about the asynchronous vs synchronous mode of making Ajax calls. All the while I'd been working on this stuff I couldn't quite understand why an alert I'd put at the end of the program (to prove I'd made it that far) was popping up before the Ajax calls had completed. Anyway, once I discovered that you can add async: false, to the properties of an Ajax call, which prevents any more Javascript executing until the Ajax call completes, everything got easier.

Whilst I am sure there are more elegant and thorough ways to program what I've done, I am going to post a copy of my stuff here also, as it is based on your code but a variation of what your code does. I removed the built-in delay, as it will only ever make four API calls, so should never hit the maximum allowed limit (I assume). I am also aware that it is flawed, in so much that Knack User Names are not necessarily unique, but I was preapred to put up with that, I've commented the code quite heavily, which was primarily to help myself, but I guess other Knack API/Javascript novices, like me, might glean something useful from it. Thanks again for your example above, without which I would have wasted even more time trying to get this working.

Cheers Steve

// Script to put tickbox selector on rows of Works Table View, plus Select All tickbox and Button to perform update.
// The update will add the IDs of the selected Works to the logged in User's Worksheet record within the two Worksheet tables.
// The user is initially prompted as to whether to start a new Worksheet or append to the existing list.

var addCheckboxes = function(view) {

// add the checkbox to to the header to select/unselect all
$('#' + view.key + '.kn-table thead tr').prepend('<th><input type="checkbox"></th>');
$('#' + view.key + '.kn-table thead input').change(function() {
$('.' + view.key + '.kn-table tbody tr input').each(function() {
$(this).attr('checked', $('#' + view.key + '.kn-table thead input').attr('checked') != undefined);
});
});

// add a checkbox to each row in the table body
$('#' + view.key + '.kn-table tbody tr').each(function() {
$(this).prepend('<td><input type="checkbox"></td>');
});
};

/**** CHANGE VIEW_ID TO YOUR OWN VIEW ID ****/
$(document).on('knack-view-render.view_13', function(event, view) {

// Add an update button
$('<button id="update"">Update My Worksheet</button>').insertAfter('.view-header');

// Add checkboxes to our table
addCheckboxes(view);

// Click event for the update button
$('#update').click(function() {

// Declare variable and Ask user if they want to add to existing Worksheet or start a new one
var ClearFlag = 0;
if (confirm("Start new Worksheet? (OK)" + "\n" + "To append to existing list click (Cancel)" + "\n" + "\n" + "Click and wait for page refresh.")) {
ClearFlag = 0;
} else {
ClearFlag = 1;
}

Knack.showSpinner();

//First let's define the Keys needed to call the API functions
var AppID = 'Knack App ID goes between these quotes';
var APIKey = 'Knack API Key goes between these quotes';

// Declare empty variables, which will be set and used at time of calling the function below
var TableID = '';
var FilterFieldID = '';
var TargetFieldID = '';

// Set up parameters for the first call of the function, which will update the first table
var TableID1 = 'object_48'; // This is the User Worksheets table
var FilterFieldID1 = 'field_504'; // This is the Name field
var TargetFieldID1 = 'field_506'; // This is the Works field

// Set up parameters for the second call of the function, which will update the second table
var TableID2 = 'object_47'; // This is the general Worksheets table
var FilterFieldID2 = 'field_493'; // This is the Name field
var TargetFieldID2 = 'field_494'; // This is the Works field

// Now get the user name to to use as the filter to retrieve the record from the table
var UserName = Knack.getUserAttributes().name;

// Begin record retrieve and update process

// Set the parameters for table 1 update
TableID = TableID1; // This is the User Worksheets table
FilterFieldID = FilterFieldID1; // This is the Name field
TargetFieldID = TargetFieldID1; // This is the Works field

//Call function to rereieve and update record in Table 1
$(function() {
GetThenUpdate(AppID, APIKey, UserName, TableID, FilterFieldID, TargetFieldID, ClearFlag);
});

// Set parameters for table 2 update
TableID = TableID2; // This is the User Worksheets table
FilterFieldID = FilterFieldID2; // This is the Name field
TargetFieldID = TargetFieldID2; // This is the Works field

//Call function to rereieve and update record in Table 2
$(function() {
GetThenUpdate(AppID, APIKey, UserName, TableID, FilterFieldID, TargetFieldID, ClearFlag);
});

// Definition of the function GetThenUpdate starts here
function GetThenUpdate(AppID, APIKey, UserName, TableID, FilterFieldID, TargetFieldID, ClearFlag) {

/*
console.log ('AppID is: ' + AppID);
console.log ('APIKey is: ' + APIKey);
console.log ('UserName is: ' + UserName);
console.log ('TableID is: ' + TableID);
console.log ('FilterFieldID is: ' + FilterFieldID);
console.log ('TargetFieldID is: ' + TargetFieldID);
console.log ('ClearFlag is: ' + ClearFlag);
alert('Just wrote the parameters passed to the function to the log');
*/

//getting Worksheet record to extract the record ID, plus the IDs of the Works connected to the record
var BaseRequestURL = 'https://api.knack.com/v1/objects/' + TableID + '/records';
var RequestFilter = [{
'field': FilterFieldID,
'operator': 'is',
'value': UserName
}];
var get_url = BaseRequestURL + '?filters=' + encodeURIComponent(JSON.stringify(RequestFilter));

$.ajax({
url: get_url,
type: 'GET',
async: false,
headers: {
'X-Knack-Application-Id': AppID,
'X-Knack-REST-API-Key': APIKey,
'Content-Type': 'application/json'
},
success: function(data) {

/*
console.log('Got record!');
console.log("data is:);
console.log(data);
console.log('End marker for data');
alert('GET just succeeded');
*/

// Out of the retieved record we need the record ID, plus the list of current IDs within the multi-select connector field
var TargetRecID = data.records[0].id;
var RawFieldID = TargetFieldID + '_raw';
var IDsInJSON = data.records[0][RawFieldID];

//This defines list as result of getIfNotSet function
IDsInJSON = getIfNotSet(IDsInJSON);

// This function resolves the error produced if the record has no records to get
function getIfNotSet(IDsInJSON) {
if (typeof IDsInJSON === 'undefined') {
return [];
} else {
return IDsInJSON;
}
}

// Need an array into which we can add the IDs from the JSON Object
var RecIDList = [];

/*
This next code iterates through the JSON object, picks out just the
IDs and adds each of them to RecIDList. If user opted for a new list
at the start, RecIDList will subsequently be cleared. Earlier attempts
to combine this function within CheckClearFlag didn't work, however.
Consequently leaving it here and ignoring the potential waste of processing.
*/
IDsInJSON.forEach(function(item) {
RecIDList.push(item.id);
});

// This defines the list as the result of the ClearFlag test
RecIDList = CheckClearFlag(RecIDList, ClearFlag);

// This function checks the ClearFlag and either clears down the existing list or leaves it as is
function CheckClearFlag(RecIDList, ClearFlag) {
if (ClearFlag == 0) {
return [];
} else {
return RecIDList;
}
}

/*
console.log('Next are the values for ClearFlag and RecIDList, after CheckClearFlag function call');
console.log(ClearFlag);
console.log(RecIDList);
console.log('End marker for ClearFlag and RecIDList');
alert('Just finished the two checks on RecIDList');
*/

// Now the ticked items are added to the RecIDList (which may or may not be empty).

$('#' + view.key + ' tbody input[type=checkbox]:checked').each(function() {
RecIDList.push(
$(this)
.closest('tr')
.attr('id')
); // record id
});

/*
console.log('Just finished adding ticked items, RecIDList now looks like this:');
console.log(RecIDList);
console.log('End marker for RecIDList');
alert('Just finished adding the ticked items (.push)');
*/

//I think this lifts out just the IDs
var webmerge_ids = [];
webmerge_ids = RecIDList.slice();

/*
console.log('Next is the value of webmerge_ids:');
console.log(webmerge_ids);
console.log('End marker for webmerge_ids');
alert('Just finished .slice command');
*/

// This pushes the value of the TargetFieldID variable into the data object used by the Ajax PUT
data[TargetFieldID] = webmerge_ids;

var puturl = 'https://api.knackhq.com/v1/objects/' + TableID + '/records/' + TargetRecID;

/*
console.log('Just about to do the PUT. next is puturl, the data array and TargetFieldID:');
console.log(puturl);
console.log(data);
console.log(TargetFieldID);
console.log('End marker for put_url, data array and TargetFieldID');
alert('Just about to do PUT, check console log for values');
*/

$.ajax({
url: puturl,
type: 'PUT',
async: false,
headers: {
'X-Knack-Application-Id': AppID,
'X-Knack-REST-API-Key': APIKey,
'Content-Type': 'application/json'
},
data: JSON.stringify(data),
success: function(response) {
TargetRecID = '';
TargetFieldID = '';
IDsInJSON = [];
RecIDList = [];

},
error: function(jqXHR, exception) {
var msg = '';
if (jqXHR.status === 0) {
msg = 'No connection. Verify Network.';
} else if (jqXHR.status == 404) {
msg = 'Requested page not found. [404]';
} else if (jqXHR.status == 403) {
msg = 'Token or permissions Error [403]';
} else if (jqXHR.status == 500) {
msg = 'Internal Server Error [500].';
} else if (exception === 'parsererror') {
msg = 'Requested JSON parse failed.';
} else if (exception === 'timeout') {
msg = 'Time out error.';
} else if (exception === 'abort') {
msg = 'Ajax request aborted.';
} else {
msg = 'Uncaught Error.' + jqXHR.responseText;
}
console.log('Logging msg, jqXHR.status, puturl, headers and data');
console.log(msg);
console.log(jqXHR.status);
console.log(puturl);
console.log(headers);
console.log(data);
console.log('End marker of log entries');
}
}); // This is the end of the Ajax PUT
},
}); // This is the end of the Ajax GET

//alert ('Made it to then end of the function definition');

} // This is the end of the definition of the GetThenUpdate function
location.reload();
}); // This is the end of the on click function
}); // This is the end of the view render

Hi again Justin,

Just noticed your other post requesting a copy of the code. Rather than clutter your topic here, I'd already opened another post on the subject of the suspect API example. You can get at the code via that topic:

https://support.knack.com/hc/en-us/community/posts/360009328631-Help-needed-to-get-the-Knack-API-Developer-Guide-GET-example-working-in-the-Contact-Directory-app

Cheers Steve

Hi Justin,

Thanks for your on-going help. Over the weekend I eventually realised that I needed work on this in a quiet period without any distractions. There are a couple of small difference between what your program does and what I'm trying to do, but I'm confident I can unravel it! I'll come back to the Knack example, once I have the priority objective completed and I think your program will solve it for me. Having said that, I did just drop a stripped down copy of my code (that was based on the Knack example and I had been trying to figure out how to get it working), in JSHint.com. I can see that on one of the offending console log operations it reports:

Unreachable 'console' after 'throw'.

I cann see these tools are going to eb really healpful! Will keep you posted, thanks again.

Cheers Steve

Also, with the code you think is flawed - try swapping out the error handler from my example with the one from knack. It will tell you what went wrong. And get a linter. I like atom for code but I started just using jshint.com. It's especially helpful in the beginning when syntax errors abound.

Can you post the example here? I don't think the example is flawed. Although, the code is examples in the dev docs assume you know a little javascript / jquery, so sometimes it's not syntax literal and you have to fill in the blanks. The code posted here is often friendlier to beginners, with more comment explanations and detailed instructions.

I recommend you go through the w3 tutorials to learn the fundamentals then hire someone at codementor to get you over humps as you go. That's how I learned.

Good luck. Hope you get this problem squashed.

Thanks, I'm working on it now. Upon reflection, I think I'm going to use my existing code but lift and massage the part of your code that retrieves the record for update, Based on what you have said in your last message, I'm going to go with your stuff for merging the values and writing them back to the record also. I can't believe how lucky I've been that you happen to have been working on almost exactly the same thing I've been struggling with for days!

If you get a minute, could you take a look at my earlier post from this morning, which asks for help with the Knack API Developer Guide example for a GET routine, installed within the Contact Directory template app. I was trying to use it as an example of how to retrieve a single record from a table and get the JSON object into a variable and I'm pretty sure it simply doesn't work! For people like me, who don't really know what they are doing, it causes no end of problems if a code example doesn't actually do what it's supposed to do! I've wasted hours, if not days on this! I regret to say that Knack Tech Support have not been enormously helpful either but, to be fair, that may be because I don't have the necessary developer experience to accurately convey what it is I'm trying to tell them, or ask of them.

Take a look and see what you think, if you have a spare 30 minutes or so. If I'm right about this and their example is flawed, it would be great if you could fix it and give it back to them to have the existing example replaced with the fixed version. I'm sure it would help others enormously.

Will keep you posted on my progress.

Kind regards,

Steve

Hi Steve, I was going to say, I don't think you need to remove the duplicates. It should ignore them the same way it ignores a record.id I accidentally put to the wrong field, or object. Hope the comments are clear. Good luck!

Hi again Justin,

Actually, having taken another quick look at it, I'm beginning to think it basically does EXACTLY what I need, without me hardly needing to change anything!!!! :-)

Will let you know how I get on.

Cheers Steve

Hi Justin,

What an incredible coincidence! What you have done here is almost exactly what I have been trying to develop myself over the last week or two! I'm no developer, however.

In my case, when the user ticks records in the table view, I need to add the IDs of those records to a many connection field within a record in another table. I'd got stuck trying to successfully retrieve the record and read in the values of the many connection field to which I want to add the IDs of the ticked records. I also need to make sure I don't add IDs that are already there, so I figure I will concatenate the two arrays of the IDs (the array of retrieved IDs and the one holding the IDs of the ticked items) and then remove the duplicates, before writing the values back. I found a bit of code on the web that does the duplicate removal bit.

Many thanks for this, I will play around with it and see it it helps me to get over my current obstacle! If/when I get my stuff working, I guess I should post my example up here as well, in case it might someone else, like you have done!

Thanks again.

Steve