How to add Relationship to a custom table?(Incident and task)

[sony8]

I have below requirement.

there is a  Multi Row variable set called 'Selection'

selection contains User ID, Username'

when i add 3 User ID for the below request

it should be added as relationship on my custom table with 3 User ID record like incident and Incident Tasks

How to create relationships on my custom table?

Thanks 

[Karthiga S]

Hi @sony8 

Creating relationships in a custom table in ServiceNow involves several steps. 

1. Create a new table or use an existing custom table.
2. Add a new field to the table. Set the type of this field to 'Reference'. In the 'References' field, select the table you want to create a relationship with (in this case, the User table).
3. Save the changes.
4. Now, you can create a new record in your custom table and select a user in the new reference field. This creates a relationship between the record in your custom table and the user.

To add multiple users to a single record, you would need to create a many-to-many relationship. 

1. Navigate to System Definition > Tables.
2. Click New to create a new table.
3. Name the table and provide a label.
4. Click Submit to create the table.
5. Now, create two fields in this new table. Both should be of type 'Reference'. One should reference your custom table, and the other should reference the User table.
6. Save the changes.
7. Now, you can create new records in this table to represent relationships between users and records in your custom table. Each record represents one relationship.

To automate the process of creating relationships based on a Multi Row Variable Set, you would need to write a script. This script would run when a record is created or updated, read the values from the Multi Row Variable Set, and create the appropriate relationships. 

var gr = new GlideRecord('your_custom_table');
gr.addQuery('sys_id', current.sys_id);
gr.query();
if (gr.next()) {
var mrvs = gr.variable_set.getDisplayValue(); // replace 'variable_set' with the actual field name
var users = mrvs.split(','); // replace ',' with the actual delimiter
for (var i = 0; i < users.length; i++) {
var gr2 = new GlideRecord('your_relationship_table');
gr2.initialize();
gr2.user = users[i]; // replace 'user' with the actual field name
gr2.record = current.sys_id; // replace 'record' with the actual field name
gr2.insert();
}
}

This script assumes that the User IDs are stored as a comma-separated string in the Multi Row Variable Set. You may need to adjust the script to match the actual structure of your data.

Please mark it Correct and Hit Like if you find this helpful!

Regards,

Karthiga

[Harish Bainsla]

1. Create Reference Fields in Custom Table:

   • Log in to your ServiceNow instance.
   • Navigate to your custom table's configuration.
   • Create three reference fields, one for each user record, to establish relationships with the "sys_user" table.

2. Configure the Multi Row Variable Set:

   • Navigate to the Service Catalog and find the item where you're using the "Selection" variable set.
   • Edit the variable set and access the "Selection" variable.
   • Ensure that the variable in the "Selection" variable set is set as a reference variable and references the "sys_user" table.

3. Use Business Rules or Scripting:

   • You'll need to create a business rule or script that triggers when the request is submitted and the "Selection" variable is populated.
   • The business rule or script should iterate through the selected user records in the variable and create records in your custom table, linking them to the appropriate user records using the reference fields you created.

Here's a basic example of what a business rule could look like

(function executeRule(current, previous /*, g*/) {
var userIds = current.variables.selection; // Assuming "selection" is the variable name

// Loop through the selected user IDs
for (var i = 0; i < userIds.length; i++) {
var userId = userIds[i];

// Create a new record in your custom table
var customRecord = new GlideRecord('your_custom_table');
customRecord.user_reference_field = userId; // Assign the user ID to the reference field
// Set other fields as needed
customRecord.insert();
}
})(current, previous);