Data from Catalog request to the target table

[Rahul84]

Hi @Ankur Bawiskar / @shloke04 / @Sandeep Dutta / Leaders,

I am having a tricky requirement for service catalog. Can you help me to start with the logic and guide me in the right path.

I am having two fields on a service catalog form. Fields are (User name & department name). 

Both fields are of type list_collector and variable attributes as glide_list.

User can select multiple values on the form for the fields ( User name & department name). 

So the exact requirement here is : 

Upon submission of a request , the values(data) selected for the fields ( User name & department name) should be inserted in one of the target table( for example table name is u_abc) in ServiceNow.

The target table is having three fields as (FieldA, FieldB & FieldC)

1). FieldA is a type of reference field.

2). FieldB is also a type of reference field. 

3). FieldC is a type of choice field. ( choices as type-user, type-department)

Now after submission of a service request , data of a field "User name" (type of list_collector on a form) should be added in a FieldA (type of a reference field) in the table.

Data of a field "department name" (type of list_collector on a form) should be added in a FieldB (type of a reference field) in the table.

Let me take an example for the scenario.

If a user is selecting 2 values for field "User name" (type of list_collector on a form) and 3 values for field "department name" (type of list_collector on a form) 

then total 5 entries should be inserted in the target table in ServiceNow 

"User name" -> FieldA ( two entries ) and this third field "FieldC " will set as "type-user" for two entries (in the table)

"department name" -> FieldB  (three entries)  and this third field "FieldC " will set as type-department for three entries (in the table)

I am able to implement the logic for the same and it is working fine but having addition requirement in this.

If the requested values are already available in the target table , then we do not need to insert the data. If it is new only then we need to insert.

for eg. If user is selecting 3 values from a glide_list on a catalog form and out of 3 values 1 is already available in the target table , then we need to insert only two value that are new.

Pls help me to extend my logic for this addition requirement.

var uList = objRITM.variables.user_list.toString();

var uList _split = uList .split(',');
if (uList _split.length> 0) {
for (var i = 0; i < uList _split.length; i++) {
var gr = new GlideRecord('table_name');
gr.initialize();
gr.user_field = uList _split[i];
gr.choice_field = type-user;
gr.insert();
}
}

Thanks !!

[shloke04]

Hi,

I am assuming this is a Record Producer which you are using here. So you can use below script in your Record Producer as below:

This will check if value exists or not and then will insert records. You need to replace the correct field name and variable name appropriately in script shared below:

var getUserName = producer.Variable_Name_1; // Replace the variable name of User Name
var getDepartment = producer.Variable_Name_2;// Replace the variable name of Department

var splitUserName = getUserName.split(',');
var splitDept = getDepartment.split(',');

if(splitUserName.length > 0){
	createRecords(splitUserName);
}
if(splitDept.length > 0){
	createRecords1(splitDept);
}

function createRecords(userName){
	var gr = new GlideRecord('Your Table Name here');
	gr.addQuery('Field A',userName);
	gr.query();
	if(!gr.next()){
		gr.initialize();
		gr.FieldA = userName;
		gr.insert();
	}
}

function createRecords1(dept){
	var gr = new GlideRecord('Your Table Name here');
	gr.addQuery('Field B',dept);
	gr.query();
	if(!gr.next()){
		gr.initialize();
		gr.FieldB = dept;
		gr.insert();
	}
}

Hope this helps. Please mark the answer as correct/helpful based on impact.

Regards,
Shloke

[Rahul84]

Hi @shloke04 ,

Thanks a  lot for replying , but here I have created catalog item instead of a record producer because I need to generate a task also for one of the group to review the request and after closure of the task , data needs to be inserted in the target table.

I have added my data insertion code in one of the existing script include in the system and call the script include function  in a run script workflow activity ( which I have added after the catalog task activity).

Pls let me know , how can I use your given code in my script include or workflow run script activity.

Thanks

[Ankur Bawiskar]

Hi,

you have shared incomplete script

the script is written in BR or workflow of catalog item?

please provide complete details

Regards
Ankur

[Rahul84]

Hi Ankur,

Script is written in the script include function and I called that function in a run script workflow activity after the catalog task activity.

This is the only script I have implemented so far. And using this I am able to insert the data into table but for checking the value already exists in the target table and if user selected 3 values from catalog form and 2 are already present in the target table and only one ( which is new ) needs to be inserted. This logic I need to extend in the below . Pls help.

var uList = objRITM.variables.user_list.toString();

var uList _split = uList .split(',');
if (uList _split.length> 0) {
for (var i = 0; i < uList _split.length; i++) {
var gr = new GlideRecord('table_name');
gr.initialize();
gr.user_field = uList _split[i];
gr.choice_field = type-user;
gr.insert();
}
}