How to create a Many to Many Relationship from a 1 to Many Relationship?

[Su522]

We have a need on Change Requests, Incidents, Problems and Cases to create a Related List with Many to Many Relationships. For Instance:

Many Changes to Many Cases and Many Cases to Many Changes

Many Problems to Many Cases and Many Cases to Many Problems

Many Incidents to Many Cases and Many Cases to Many Incidents

To accommodate this I have created the Many to Many Relationships on the 'sys_m2m' table, and added the Related List to the appropriate table (Change Requests, Incidents, Problems and Cases). This works great- as expected.

The issue is on CSM/FSM Configurable Workspace:

From a Case in the CSM/FSM Configurable Workspace  > you can create a Change Request, Incident, Problem > this creates a 1 to Many Relationship with the Case. IE: with the 'Parent' field. Ex. Case-> Incident

This works well, however there are 2 issues:

1. It does not update the Related Lists where I created the Many to Many Relationships (those are on a different table) 

2. It does not update the CSM/FSM Workspace Related List when I edit the Many to Many Relationships (that is on a different table)

How would you solution for this?

We need the Many to Many Relationship Related Lists.

The Desired Uutcome is:

Any time we add a record (edit) the Many to Many Relationships (using the Related Lists), 

And any time we use the CSM/FSM Configurable Workspace to create a needed Change Request, Incident, Problem from a Case:

The Many to Many Relationships Related Lists (on the same table) are updated to reflect this

The Related Lists in the CSM/FSM Configurable Workspace are updated to reflect this.

I appreciate your help in advance!

Thank you.

[Tushar]

Hi @Su522 

Create a BR which triggers on creating/editing records in the sys_m2m table.

Using a Script Include to update Related Lists on relevant tables (Cases, Changes, Incidents, Problems).

Script Include should contain a logic for updating Related Lists.

Now create a UI policy on Case form which will trigger on updating the Parent field and maybe refresh related Lists using g_form.getRelatedLists().

Please, don't forget to mark my answer as correct if it solves your issue or mark it as helpful if it is relevant for you!

Regards,
Tushar

[Rajdeep Ganguly]

To achieve your desired outcome, you can consider the following steps:

1. Create a Business Rule or a Scripted REST API to synchronize the data between the two tables. This will ensure that whenever a record is added or updated in one table, the corresponding record in the other table is also updated.

2. For the Business Rule, you can use the 'after' insert and update events to trigger the synchronization. The script should check if the record exists in the other table and if not, create a new record. If the record exists, it should update the existing record.

3. For the Scripted REST API, you can use the HTTP methods POST (for creating a new record) and PUT (for updating an existing record). The API should be called whenever a record is added or updated in one table.

4. In the CSM/FSM Configurable Workspace, you can use the 'onLoad' client script to refresh the related list whenever the record is loaded. This will ensure that the related list is always up-to-date.

5. You can also consider using the ServiceNow IntegrationHub to automate the synchronization process. This will require the creation of a Flow that triggers whenever a record is added or updated in one table and then updates the corresponding record in the other table.

6. Finally, you should thoroughly test the solution to ensure that it works as expected and does not introduce any performance issues.

Please note that this is a high-level solution and the actual implementation may vary depending on your specific requirements and ServiceNow environment.

nowKB.com

[Maik Skoddow]

Hi @Su522 

sorry, but I cannot recommend that at all. The entire platform and all of its products rely on the OOTB relationships. Introducing m2m relationships now would result in extreme adjustments in probably hundreds of artifacts. With such a profound intervention, you will lose ServiceNow support and the effort required to review the skipped records increases immeasurably after each upgrade. I can promise you that if you continue down this path, you will drive your instance against the wall.

Why don't you check first alternatives? For example, what about grouping several cases/incidents below a parent  case/incident and then reference these parent records?

Maik

[sneidich22]

Instead of building a custom M2M table, have you checked to see if you're entitled to the Many to Many Task Relations plugin? This gives you the Task Relationship [task_rel_task] table as well as a few other tables to help define how various tickets can be connected (similar to CI Relationships if you've used those). You could then write code/scripts/flows, etc. to populate task relationships based on your use cases.