Create new variables on existing RITM's

Tzach Benbenist · ‎12-03-2023

Hello,

I have a requirment to add 2 new reference variables instead of 2 old string fields.

I want to create data fix to the existing records and switch between the fields on the variable editor view.

The problem is that the existing record created without these new ref fields.

There is a way to create those new variables to the existing records?

Thank you 🙂

Tzach Benbenist · ‎12-03-2023

I found solution 🙂

This is the script:

var ReqLog = new GlideRecord("staging table");

ReqLog.addEncodedQuery("u_requestISNOTEMPTY");

ReqLog.addQuery("sys_id", "4203efe6938935104bb035aa6aba1062"); //Test value

ReqLog.query();

if (ReqLog.next()) {

var ritm = new GlideRecord("sc_req_item");

ritm.addQuery("request", ReqLog.u_request.sys_id);

ritm.query();

if (ritm.next()) {

var option1 = createOption("93d8c711933eb15046fbff8a6aba10ec", getRefMan(ReqLog.u_yyhr_manager), 28); //question,value,order

var option2 = createOption("88498b11933eb15046fbff8a6aba1069", getRefMan(ReqLog.u_yyhr_new_manager), 29); //question,value,order

createOptionM2M(ritm.sys_id, option1); //ritm_id, dependant_item

createOptionM2M(ritm.sys_id, option2);

ritm.update();

}

function createOption(question, value, order) {

var gr = new GlideRecord("sc_item_option");

gr.initialize();

gr.item_option_new = question;

gr.value = value;

gr.order = order;

return gr.insert();

}

function createOptionM2M(ritm_id, dependant_item) {

var gr = new GlideRecord("sc_item_option_mtom");

gr.initialize();

gr.request_item = ritm_id;

gr.sc_item_option = dependant_item;

return gr.insert();

}

function getRefMan(emp_id) {

var int_id = Number(emp_id.toString());

var id = int_id.toString();

var gr = new GlideRecord("sys_user");

gr.addQuery("employee_number", id);

gr.query();

if (gr.next()) {

return gr.sys_id;

}

View solution in original post

Anwesha3 · ‎12-03-2023

Hi @Tzach Benbenist
If my understanding is correct, there are 2 existing string variables in a Catalog form . Which hold values in production as well. But now you want to change the field type to reference but don't want to lose your existing data that are stored in those string variables?

If the above scenario is what you are referring then, you first need to replace those existing values with their respective sys_id s. For example, one field hold username and hold asset details then replace those values with that particular user's and asset's sys_id respectively. And only after that change the field type to reference.
To replace the data with sys_id you can use fix script which will run one time and change values.

You should take backup of existing records before trying this. Keep the "Record Roll back" true on fix script in case you need to revert the changes done by your script.

Please mark helpful if it has contributed in some way to your query 🙂

Tzach Benbenist · ‎12-03-2023

Thank you for your response @Anwesha3,

I don't want to change the field type directly, because I'm retreiving the inputs from integration and we don't want to modify it.

I looking for solution like creation of the new variables via script on sc_item_option_mtom table or sc_item_option.

Anwesha3 · ‎12-03-2023

Hi @Tzach Benbenist,

Can you please explain the scenario in detail please. When you want to create? Is it based on another action? will you create 2 variables each time?
If you can share the business requirement in detail it would be helpful to assist.

Thanks, and regards,

Anwesha

Tzach Benbenist · ‎12-03-2023

Ok, so this is the scenario:

1. there is a staging table for requests that receiving string inputs from integration and based on this inputs we are creating requests (cart API).

2. two of these inputs is managers (employee id).

3. In the current implementation, the request hold these managers id on string variables.

4. The customer wants to change these string variables to reference variables

5. I don't want to mess with the integration and involve more dev team, I want to create another 2 ref variable and based on the manager id to populate the ref variable.

6. For new records it's working perfect, the problem start with the old record because the new variables does not created for them.