S
ervice-now allows you to easily create relationships between tables without having to know a whole lot about the underlying table structure. If you’ve ever worked with a reference field then you’ve worked with one of these relationships. A reference field is simply a one-to-many relationship between 2 tables in ServiceNow. The ‘one’ end of the relationship is displayed as a reference field on the form. The ‘many’ end of the relationship is displayed on the referenced table as a related list.
In some cases, it may also be necessary to create a many-to-many relationship in your instance. Because ServiceNow ships with most of these relationships already defined, it’s rare that you’ll have to create one. If you do have to create a many-to-many relationship, here’s how you could do it.
For this example, we’ll assume that we need a many-to-many relationship between the Incident and the Change request tables. One Incident can be related to many changes (maybe several changes are required as a result of an incident) and one Change request can be related to many Incidents (implementation of a change results in many incidents).
1) Navigate to the m2m table
There are two tables in your ServiceNow instance that contain many-to-many relationship information. The first one (sys_collection) contains the out-of-box relationships and should be used only for reference purposes. The second one (sys_m2m) is where you’ll want to create your own many-to-many definitions. It’s probably a good idea to create your own modules to link to these tables so that you can easily access them if you’re going to be dealing with m2m tables very often. In most cases these tables won’t be used very much so it may be easier just to navigate to them directly in the application filter in your left navigation or by navigating directly by url. The easiest way is just to type ‘sys_m2m.list’ in your left navigation filter.
2) Create a new Many-to-Many relationship tableClick the ‘New’ button on the ‘sys_m2m’ table list and create a new record. There are really only 2 fields that you need to fill in since the rest gets automatically calculated for you. All you need to specify are the ‘From table’ and the ‘To table’ values. For this example we’ll specify ‘Incident’ as our ‘from table’ and ‘Change request’ as our ‘to table’. These values could be interchanged with no impact on the end result. You do want to pay attention to the values in the other fields though since they indicate what your table will be named along with how your related lists will be labeled. Although these are all calculated automatically, you can change them if needed.
3) Add the related list to your Change request table
Navigate to your Change request form, right-click the form header, and personalize the related lists for the form. Select the ‘Incidents’ list to add to your form. All One-to-Many lists have a ‘->’ separating the relationship. Many-to-Many lists (and custom formatters) have no separator.
4) Add the related list to your Incident tableFollow the same procedure for your Incident table. This list should show up as ‘Change requests’, but may show up with the name of your many-to-many table as shown below. If your related list doesn’t show up correctly, submit an incident to ServiceNow support.
5) Relate a record to test it outYour related lists will show up at the bottom of each form. Once you have added the related lists to both sides of the relationship, it is easy to relate records to each other using the ‘New’ and ‘Edit’ buttons on the related list.
When creating or using a M2M table, created via ‘many to many’ module or default, we see behavior that we would want to control.
If you delete one of the records referenced, the M2M record is deleted. Sometimes you want it to happen and sometimes you don’t.
Can someone please explain where this is happening and how can we control it, we can not find it….
Also: the delete happening is not triggering an ‘on delete’ business rule, is the system triggering the correct delete routine?
To see this behavior:
Go to Many to Many, create a m2m between change an incident. connect ‘incident’ and ‘change_request’, create a record and delete one of both -> will delete the m2m record.
Thanks for your answers!
Rik.
I can’t think of any time where you wouldn’t want this to happen. If the record is deleted then there is no relationship that should be shown. I did just check the ‘before delete’ rule and you’re correct there. It doesn’t run, but an ‘after delete’ rule will run. You may want to contact support about that.
One other thing you might consider is not using the m2m table at all. For incident/change there are already reference fields that give you both ends of the common relationship between the tables. You can check out the ‘RFC’ field and ‘Caused by change’ fields on the Incident form. In my experience the use of these reference fields makes a lot more sense than a m2m table anyway.
after we create a m2m table…do we have an option to edit or delete it.
i checked it and was unable to edit/delete…
could any one help me with this….
You can edit or delete the table by opening up the table and personalizing the form (edit) or table dictionary entry (delete). You may want to contact support about the delete operation though. I put a bug in a while ago for this to be fixed, but it looks like it isn’t fully fixed yet. Right now, you can delete the table, but the m2m entry remains. The only way to delete the m2m entry is to disable the security ACL on the ‘sys_m2m’ table first. It should just be deleted when you delete the table.
Thank you mark will try that and will get back to you.
Yeah when i tried to deleted from the table dictionary entry (delete) the m2m still stay there…i was trying to just change the lable name,…but its failed…
will try any other way to do that…thank you very much
Is there any way to audit changes of relationships established this way? Is this still the best way to set this up, or should the Related Items plugin be used?
Auditing is kind of tricky in this situation because the operations are almost always an insert or a delete. If you want to audit these changes, then you need to set up a business rule on your m2m table to capture those changes and then store them in some other related record.
As far as the best way, I’ve never really been a huge fan of the many-to-many task relations plugin because it’s got such a broad scope (all task types) and that makes it so that you always have to filter the information in the related list. In my experience, the cases where you need this type of functionality are generally very few and very targeted, which is why I prefer to set up my own table.
Thanks for the info Mark. When I looked at it, I didn’t exactly like the way that the plugin worked either. I think I will stick with the method you are using here.
HI Mark
I have Created a Related list from Relationship under the system definitions application while displaying this as a related list on the form the edit button is not showing up… But by default every related list has a edit button
I have also tried that from list control enable edit but i am enable to show the edit button on my related list…
Regards
Akshat
You’ll want to take a look at ACL security for that table as well. Make sure that users have create, write, and delete access.
Hi Mark
This works well BUT what it doesn’t do is put a link on the record you have linked to. I’ll explain. I’m linking problems to problems. So for example I’m in PRB001 and link to PRB002. So PRB001 shows PRB002 in the related list but in the PRB002 record it doesn’t show PRB001 in it’s related list. Is there a way of doing this?
It should do exactly that. I’d guess that you don’t have the right related list associated…or maybe not the correct columns shown. If you’re relating a problem to a problem you would probably need to include both columns to show both sides of the relationship.
Hi Mark,
ITIL users cannot able to view New/Edit button in the related list.
Any solution?
Thanks,
Dharani
Hi Dharani,
Have you created the appropriate ACLs on the new many-to-many table to allow users with the ITIL role to create and write to records on that table?