Need to update record's based on selection made in catalog request

[bishalsharm]

I have one table called "xyz". In this table there are three columns called
configuration_item, key and value
configuration_item is a refernce field and it is referencing to cmdb_ci table.
1 CI from cmdb_ci table can have multiple records under xyz table like below

configuration_item key value
item1                         A     B
item1                         E      J
item2                         K      L

Now the ask is I have one catalog item which is used to update the key value records in xyz table for specific CI. As you can see in above example, the total count is 3... but if the user selects the count as 1 under one variable and then removes the random key value while raising the request then once the RITM generates the same count and with the same key value it should get update in the xyz table for that CI.
For example: Lets say we have above CI and its key value count and records.
while raising update request in service portal
User selected the count as 1 and kept the mapping as below
key value
Z       N

now once the RITM generates the update should look like below in the xyz table
configuratio_item key value
item1                      Z      N

can u help me to build the logic for above ask

[Ankur Bawiskar]

@bishalsharm 

why not use reference variable and then have 2 more string input variables?

once RITM is submitted simply query that table with sysId in 1st variable and update the other 2 columns

where are you stuck?

If my response helped please mark it correct and close the thread so that it benefits future readers.

[KKM]

Hi Bishalsharm,

Sure! Here is the logic to handle updating the xyz table in ServiceNow when a user submits a request through a catalog item.

How to approach

Retrieve the existing key-value pairs for the selected configuration_item from xyz.
Check the count entered by the user (i.e., how many key-value pairs should remain).
Remove the old records and insert the new ones based on the user's input.
Ensure that only the new key-value pairs remain for the configuration_item.

Implementation - Script Include
You can create a Script Include to handle the update operation when the Request Item (RITM) is generated.

Script Include (Server-side)
-Name: UpdateXYZTable
-Client Callable: False
-Accessible from: All Application Scopes

var UpdateXYZTable = Class.create();
UpdateXYZTable.prototype = {
initialize: function() {},

updateConfigurationItems: function(ritmId) {
// Get RITM record
var ritm = new GlideRecord('sc_req_item');
if (!ritm.get(ritmId)) {
gs.error("Invalid RITM ID");
return;
}

// Get user-selected CI and count from variables
var ciSysId = ritm.variables.configuration_item; // Reference field for CI
var newKey = ritm.variables.key; // New key from catalog item
var newValue = ritm.variables.value; // New value from catalog item
var userSelectedCount = parseInt(ritm.variables.count, 10); // Expected count

if (!ciSysId || !newKey || !newValue || isNaN(userSelectedCount)) {
gs.error("Missing required data for update.");
return;
}

// Query the xyz table to find existing records for the selected CI
var xyzGR = new GlideRecord('xyz');
xyzGR.addQuery('configuration_item', ciSysId);
xyzGR.query();

// Delete existing records for this CI
while (xyzGR.next()) {
xyzGR.deleteRecord();
}

// Insert new records based on user-selected count
for (var i = 0; i < userSelectedCount; i++) {
var newRecord = new GlideRecord('xyz');
newRecord.initialize();
newRecord.configuration_item = ciSysId;
newRecord.key = newKey;
newRecord.value = newValue;
newRecord.insert();
}

gs.info("Updated 'xyz' table successfully for CI: " + ciSysId);
},

type: 'UpdateXYZTable'
};

Triggering the Update
You can call this Script Include from a Business Rule or Workflow Script when the RITM moves to an appropriate stage (e.g., "Work in Progress" or "Completed").

Business Rule (on sc_req_item)
-Table: sc_req_item
-When: After Insert
-Condition: current.cat_item == <YOUR_CATALOG_ITEM_SYSID>
-Script:

var updater = new UpdateXYZTable();
updater.updateConfigurationItems(current.sys_id);

Expected Outcome
Before Update (xyz table)
configuration_item | key | value
------------------------------------------------
item1 | A | B
item1 | E | J
item2 | K | L

User Input in Service Portal
-CI: item1
-Count: 1
-Key: Z
-Value: N

After Update (xyz table)
configuration_item | key | value
------------------------------------------------
item1 | Z | N
item2 | K | L

Notes
If you want to retain some records rather than deleting all, modify the delete logic.
Ensure validation on input values before updating.
Add logging for tracking updates.

This should solve your issue!

Kindly mark it as "Accepted Solution"/"helpful", as it resolves your query. Please press like button for the resolution provided.

With Regards,
Krishna Kumar M - Talk with AIT3ch
LinkedIn: https://www.linkedin.com/in/mkrishnak4/
YouTube: https://www.youtube.com/@KrishAIT3CH
Topmate: https://topmate.io/mkrishnak4 [ Connect for 1-1 Session]

[bishalsharm]

Hi @Ankur Bawiskar ,

could you please elaborate this, I couldn't get your point.
an example would be helpful here.

Thanks

Bishal

[Ankur Bawiskar]

@bishalsharm 

what did you start with?

I already shared the approach, sharing some technical insights

why not use reference variable and then have 2 more string input variables? -> create reference variable, create 2 more string variables

once RITM is submitted simply query that table with sysId in 1st variable and update the other 2 columns -> you can use after insert business rule or workflow run script here

If my response helped please mark it correct and close the thread so that it benefits future readers.