Question about connecting tables and grandparents

[LeighBerre96926]

Background: I’m creating a risk management system. There are 3 levels of info.

RISKs can have multiple CAUSES. A CAUSE may relate to multiple RISKS
CAUSES can have multiple CONTOLS. A CONTROL may relate to multiple CAUSES.
So in this scenario, the RISK is the grandparent, the CAUSE is the parent, and the CONTROL is the child.

USERS may OWN a RISK (connected as “Owned by”)
USERS may OWN a CONTROL (connected as “Owned By”)

Problem: There will be some users who only own CONTROLS. But they will want to see the RISK that control relates to. I cant find any way to show the RISKS that a CONTROL is descended from, as there doesn’t seem to be a way to determine the “Grandparent” connection in Knack.

Question: How can I structure the table connections so that a record can be related to its grandparent? Or is there some other way you can suggest to allow me to build a grid of all the CONTROLS that a user owns, and in that grid show for each CONTROLS a link to the RISK it descends from?

[ Take into account the way these are entered is the RISK is created first, then the CAUSES, then the CONTROLS ]

I hope that makes sense? I know there should be a way, but I cant wrap my head around it.

[LeighBerre96926]

OK. to make this easier I’ve reconstructed with the three tables GRANDPARENT PARENT CHILD. I get very close to what I need, but when i create a table of CHILDREN and add in the GRANDPARENTS, Knack has the right RECORDID for the GRANDPARENT but the link it forces me to use is to the PARENT DETAIL, not the GRANDPARENT DETAIL, and so it fails. See below.

Does anyone know if there is a way to override the link content of this field through javascript?

[LeighBerre96926]

Thanks to chatGPT, another small victory. I havent worke dout whay that Knack generated link is wrong, but I did work out how to change it to one that works. The following code changes the field URL to what i need it to be:

$(document).on(‘knack-view-render.view_87’, function(event, view, data) {
$(‘#view_87 td.field_158 a’).each(function() {
var currentUrl = $(this).attr(‘href’);
var recordId = currentUrl.split(‘/’).pop(); // Extract the RecordID
var newUrl = ‘Knack’ + recordId +‘/’; //replace the url and add the final / back.
$(this).attr(‘href’, newUrl);
});
});

Case closed.

[CarlHolmes]

Very impressive @LeighBerre96926