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

Michael Ritchie · ‎10-24-2014

How did you setup the many to many table? Did you use this feature:

Creating a Many-to-Many Relationship - ServiceNow Wiki

Or did you create your own table?

domaners · ‎10-24-2014

Hi Michael,

Personally I don't think you are going to be able to change this behavior, although you should be able to work around it. Because you are relating each side of your many-to-many relationship to the Change Request table, you are effectively creating two separate relationships, with two Related Lists required to see the records, depending on which side of the relationship the Change is added to.

What I would do is open the List Control for each related list (once for Change Request and again for Associated Change Requests). Check the "Omit columns if empty" box and save. Then open your many-to-many definition by navigating to "sys_m2m.list", open the record for your list and change both Label fields so that they have the same name.

Now when you open a change, there will only be one Related List displayed each time, and the Label will be the same for each. This keeps the consistency for the user without requiring a radical amount of work. The only hurdle you still may have here is reporting on related changes, as the two different relationships may prove difficult to aggregate.

Nate23 · ‎10-24-2014

Adam,

That is a good idea let me try that and see if it is acceptable on the other end!

Thanks,

Nathan G.

Nate23 · ‎10-29-2014

Adam,

Unfortunately this method will not work, because depending what column it is in will depict what table shows up, which means if the record is in both columns down the line they both show up still... I will have to revamp the whole structure and try to find a new way.

Thanks,

Nathan G