How to create a reference between tables Control Objectives and Citations

Anh H_ · ‎04-25-2022

Hello,

I have imported content into 2 tables in ServiceNow called Control Objectives and Citations. The source data for these tables is in a csv file in case it's relevant. Each row in the Control Objectives table (screenshot 1) corresponds to several rows in the Citations table (screenshot 2). In other words, different citations can share the same control objective. I'm trying to establish a relationship/reference between these 2 tables so that when I click on each name in the Control Objectives table, I can see a list of citations that are associated with the aformentioned name. For example, right now the list of citations for control objective "Acceptable Discoverable Information VPM-06.8" is blank and I want it filled with the associated citations from the Citations table (screenshot 3). Please find below the screenshots. I've searched a lot of places for suggestions, but couldn't find anything that I could easily understand or adapt for my problem. Any help would be greatly appreciated. Thank you so much!

Screenshot 1

Screenshot 2

Screenshot 3

dmathur09 · ‎04-25-2022

Hi,

This is an OOB behavior, if you have a control objective being used by multiple citation. Refer below screenshot.

Citation 1

Citation 2

So as you can see that the control objective will refer to both the citation in the related list.

Since you had done a manual upload using CSV where there were no relationship being given. You would be required to create the relation manually using the edit option in citation related list.

Regards,

Deepankar Mathur

Anh H_ · ‎04-25-2022

Hi,

Thank you for your reply. Creating the relations manually is simply not feasible for my case as I have hundreds of thousands of citations and control objectives. I believe there's already some sort of relationship established in the CSV files that I have. Let me describe them below:

1. Please find attached a simplified, abridged cleaned CSV file. As you can see, cells A2:A4 contain the control objectives. Cells B1:F1 contain the regulations (which are not really relevant here but are part of the data). And cell E2 contains more than 1 citation, so these citations need to be separated into separate rows. Further, because the Citations table in ServiceNow has a long structure, I have to transpose these citations in this file from the current wide format into a long format, which leads me to the transposed CSV file next.

2. In this transposed file, we have 3 columns for Control Objective, regulation, and citation. As the data have been transposed from a wide to long format to accommodate the long structure of the citations data, all of the control objectives are repeated. I suppose I could import this transposed CSV into the Citations table in ServiceNow and then each citation in that table would have the associated control objective. However, if I also imported this transposed CSV into the Control Objectives table in ServiceNow, then each control objective in that table would have the associated citations BUT we would end up seeing repeated control objectives like we see in the transposed CSV file, which we don't want.

So do I understand correctly that there's already some sort of relationship established in the transposed CSV file between control objectives and citations? If there's already a relationship, anything else I can do so there won't be repeated control objectives in the Control Objectives table in ServiceNow?

Any help would be greatly appreciated. Thank you so much!

dmathur09 · ‎04-26-2022

Hi Anh,

As we know Citation to Control Objective is a many to many relationship. Servicenow uses the m2m table to map these table.

Hence you can easily import your transposed CSV file to this table(sn_compliance_m2m_statement_citation)

As you can see from the screenshot below. That I have multiple records of same control objective but with different Citations and it can a vice versa case as well.

Hope this helps.

Regards,

Deepankar Mathur

Anh H_ · ‎04-26-2022

Hi Deepankar,

Thank you for your reply. I'm required to use the two main tables that I described in my original post (sn_compliance_citation and sn_compliance_policy_statement), not the relationship table (sn_compliance_m2m_statement_citation). Also, another requirement is that I do not have duplicated control objectives or duplicated citations in their respective tables. Thus, I'm afraid the solution you proposed might not work for my case. I'd appreciate any other ideas. Thank you!