Many to Many on same table?

Nate23 · ‎10-24-2014

Hello All,

I've been put to the task of trying to create a Many-to-many relationship on the Change Request table. We sort of have it working but not to our liking. We created another table to associate the changes with each other. but if you go on one change request and show the related list for the Change Request table it will show the related change requests like it should. However, you click on one of the related change requests to view it and it shows the relation on the other table we created not the Change Request table.

We want it to be consistent on one table not two. The attached link shows the related lists on two different Change Requests. Notice that the table names are different.

Any help would be appreciated.

Thanks,

Nathan G.

Nate23 · ‎10-07-2015

Hello,

I ended up creating a list type field on the change table. From there I created a relationship(System Definition>Relationships) From the Change Req table to the Change Req table with the following query:

current.addQuery('sys_idIN' + parent.u_related_changes);

Then I overwrote the save and update functions for when they edited the list to query the change table and add the current change to the associated changes added or updated. as well as removed them with this code:

var gr = new GlideRecord('change_request');


gr.addQuery('sys_id','IN' , current.u_related_changes);


gr.query();


/*


gs.addInfoMessage('Current Sys ID: ' + current.sys_id);


gs.addInfoMessage('selected Sys ID: ' + current.u_related_changes);*/






while(gr.next()){


   if(gr.u_related_changes == ''){


// gs.addInfoMessage('No Recored so the parent Child is entered1');


   gr.u_related_changes =   current.sys_id;


   }else if(gr.u_related_changes.indexOf(current.sys_id) == -1){


// gs.addInfoMessage('No Recored so the parent Child is entered2');


   gr.u_related_changes = gr.u_related_changes + ',' + current.sys_id;


   }


   gr.update();


}


action.setRedirectURL(current);


current.update();

Hope this helps

View solution in original post

Nate23 · ‎06-22-2017

so I did it without creating a new table, but off the top of my head the easiest way I think would be to create a new table. create 2 reference fields both on in your instance the KA table. I would also create a true/false flag (default false). Then make an on create BR triggered when flag is false and create a second record and flop the ref field values new record has flag true. (this prevents an endless loop of creating records). then create a delete BR that finds the flip flops and deletes it too so you dont have orphaned relationships. That would be my approach. that way you have the new/edit capabilities on the table.

simmons_andre · ‎09-08-2017

I am facing a similar problem as described. For my client we are creating a scoped app with custom tables. The client wants to be able to relate records on the custom table to other records within that table. Kinda like relating multiple incidents to each other. Do you have any code the references your new approach?