So in case it helps someone else one day, this is what ChatGPT and I determined is a working approach to this.
To restate the problem. I have two tables SUPPLIER and SYSTEM. In SYSTEM, I have a connection to SUPPLIER (each system can have 1 supplier, each supplier can have many systems).
For user convenience, I need a way for user to connects SYSTEM(S) to a SUPPLIER while viewing the SUPPLIER … rather than having to navigate to the SYSTEM page.
As far as I can tell, this requires an API. So what I did was this:
In SUPPLIER table, added a SYSTEM connection - 1 supplier to many systems. This is used temporarily by the API to know which systems to connect to this supplier.
I created a child page off the SUPPLIER grid view, which is a form to update the SUPPLIER, with SUPPLIER shown as read only, and the new SYSTEM field editable. (For convenience, I also filter this field to only SYSTEMSs where SUPPLIER is BLANK). In the API comments this is the USER’s VIEW. (I also added a grid of systems connected to this supplier, with a remove action, just to complete the “ease of use”)
User selects SYSTEM(s) to connect to this SUPPLIER and submits. And the API kicks in.
To use a secure view-based API, I have created another page (which is HIDDEN, not connected to the menu, and users who have access to the SUPPLIER page also have access to this page.
It contains a grid of SYSTEMs, enabled for inline edit, with just 2 fields SYSTEM and SUPPLIER. This is used to achieve the purpose of connecting SYSTEMS to the SUPPLIER.
It also contains a grid of SUPPLIERS, enabled for inline edit, with just 2 fields SUPPLIER and SYSTEM. This is used to clear the temporary SYSTEM connection in SUPPLIER table after that primary API is run - just as cleanup - otherwise those selections hang around for the next time the user might go here.
The API javascript is:
/ Bind a function to form submission on scene 12, view 11 in Knack. (USERs VIEW)
$(document).on(knack-form-submit.scene_12.view_11, function(event, view, record) {
// Capture the suppliers ID from the submitted form.
var supplierId = record.id;
// Capture the list of connected systems from the form, assuming field_27_raw contains these connections.
var systemsConnected = record.field_27_raw;
// Check if there are any systems connected.
if (systemsConnected && systemsConnected.length 0) {
// Iterate over each connected system.
systemsConnected.forEach(function(system, index, array) {
// Prepare data to update the field_18 (Supplier field) in the SYSTEM table with the suppliers ID.
var data = {
"field_18": [{ "id": supplierId }]
};
// Execute an AJAX request to update each systems supplier reference. the URL below is the hidden page
$.ajax({
url: `https://api.knack.com/v1/pages/scene_18/views/view_19/records/${system.id}`,
type: PUT,
headers: {
"X-Knack-Application-ID": Knack.app.id, // Knack application ID for API access.
"Authorization": Knack.getUserToken(), // Authorization token for secure access.
"Content-Type": application/json // Set content type to JSON.
},
data: JSON.stringify(data),
success: function(response) {
// After the last system is updated, clear the suppliers systems field.
if (index === array.length - 1) {
clearSystemsForSupplier(supplierId);
}
},
error: function(request, status, error) {
// Handle errors (if any) during the AJAX request.
}
});
});
}
});
// Function to clear the systems field for a supplier.
function clearSystemsForSupplier(supplierId) {
// Prepare data to clear the field_27 (Systems field) in the SUPPLIER table.
var clearData = {
"field_27": [] // Clearing the connection by setting it to an empty array.
};
// Execute an AJAX request to update the supplier record, clearing its systems connections. This uses that second grid on the hidden page.
$.ajax({
url: `https://api.knack.com/v1/pages/scene_18/views/view_20/records/${supplierId}`,
type: PUT,
headers: {
"X-Knack-Application-ID": Knack.app.id, // Use Knack application ID for API access.
"Authorization": Knack.getUserToken(), // Use authorization token for secure API access.
"Content-Type": application/json // Ensure the content type is set to JSON.
},
data: JSON.stringify(clearData),
success: function(response) {
// Successfully cleared the systems connection field for the supplier.
},
error: function(request, status, error) {
// Handle errors (if any) during the AJAX request.
}
});
}
I hope that helps someone - seems to work and makes life much easier for our users, especially when onboarding a new SUPPLIER for existing SYSTEMS.
