I currently perform this process by manually and would like a way to do it programmatically.
My object is named Allocation.
It documents what a resource is assigned to during a given date range.
The date range is controlled with a Date/Time Field named “Assignment Date.”
I have Text Formulas that strip out the start and end dates from the “Assignment Date”.
I have Date/Time fields for “Start” and “End” that use conditional rules that to set them to the text formula values above.
The connection is an Object named “Weeks”
It has a Field named “Week Number” that is unique. It is formatted as YYYY.WW (2024.01 being the first week of 2024) I create all 52 weeks at the beginning of the year.
There are Date/Time fields for each day of the week (Sunday-Saturday.)
I update the many to many connection on the “Allocation” manually when the “Assignment Date” changes. I select the Weeks that the assignment dates occur in.
This allows me to create a Graph of the number of resources forecast during that week.
I’d like to have code run when the “Assignment Date” is changed that edits the “Weeks” connection and adds all of the weeks that the Allocation occurs in.
So, if the “Start” date of the Allocation is before the “Saturday” of that Week AND the “End” date of the Allocation is after the “Sunday” of that week, it is added to the Many-to-Many “Weeks” connection.
The struggle when doing this manually is that you have to select each individual week in the Many-to-Many connection. You cannot use CTRL or SHIFT to multiselect in either the Builder or the Live App. This is very tedious for long assignments (sometimes multiple years.)
In order to do this programatically you’re going to need to use the Knack API in combination with some sort of back-end hosting provider to handle the background job. This is a task that my team of software engineers can help with. We are experts at Knack and can probably set this up for you pretty quick.
The API documentation gives this example for setting connection fields.
$(document).on(‘knack-view-render.view_52’, function(event, view, data) {
// The connected record id value
var connectionId = “5b2ac7fc66b6036b8f859c45”;
// Update the view and field ids for your application
$(‘#view_52-field_3’).val(connectionId);
$(‘#view_52-field_3’).trigger(“liszt:updated”);
});
I was thinking of creating an array and adding the the week to the array if it meets the above criteria and then writing the array to the connection field.
What does a “back-end hosting provider” do in the solution you were thinking about? I’m not sure I understand why we need to go outside of JavaScript and the API.
Admittedly, my JS is very elementary, but I have an in-house programmer who might have time tackle this and I’d like to point him in the right direction. He’s not super familiar with Knack, but he knows JS and has worked with other APIs (like ReTool, etc.)
We do something similar with an object that has 52 fields 1 for each week of the year. When an event happens we want to record that event in the current week of the year. The week number is important to us for this.
For this to work we have made sure that weeks 1 to 52 are all in field order i.e. field 123 = week 1, field 124 = week 2 etc.
In JS we create an object of weeks and fields. With only the starting field as reference. We can then add any event to a specific field using date to week numbers using JS on a “knack-form-submit” and Knack API.
This might not be exactly what you are after but might give you some ideas to work with.
I can also give you some specific code if you think this is close to what you need, but I’m on my phone at the moment.
If I understand correctly, I’m not sure it’s similar to how I have things set up now. My challenge is setting a many-to-many connection field to an array of week objects.
I’m not opposed to changing how that’s done though.
One challenge I see with how you have configured your app is that our reporting spans multiple years (particularly when we are looking at the information during 3rd and 4th quarter.) That’s why I went the route of weeks as an object and not a field.
I didn’t think it would be quite what you were after but yearly reports are definitely at challenge.
I am happy to have a chat with you about it and see if we can come up with a solution as I think your set up may be a better way to do it.
In terms of setting many to many relationships in JS is definitely possible the difficulty would be getting all the specific record IDs for each week which is something you may have to do using external software like Make but could be done with API.
Yes either by having a view on the page (could be hidden) then getting the row ID or a records render of that view and getting the ID from the records object or using API.
I see you commented on the Record ID saved to field post about _recid in KTL.
I’m off to look at that. @NormandDefayette_CortexRD has been a real help for my app.
It has two modes of operation: add and edit.
It requires two grids: source and destination. The keyword is placed in the destination grid.
Add mode: will create (copy) all records from the source grid to the destination grid. Edit mode: will update the destination grid based on the source grid, for each matching record ID and each specified fields. The field mapping is done based on the header text instead of the field ID. This is the secret sauce!
I won’t get into the details here, but I can tell you that I do pretty crazy an unusual stuff with this. Like connecting foreign objects that are completely unrelated. It supports multiple connected fields and can even fetch data from other Details views in the page.
I often combine this with _ar (auto refresh) to get continuous, real-time updates.
I recommend that you explore the keyword on a dummy page and data first.
