variable sets - how to capture new variable values to a new table?

woodyfairley · ‎10-17-2023

A former developer who is highly skilled created a service portal app which uses a variable set to collect accounting data and populate table fields somewhere, I am still looking. Now the accounting fields have changed and I need to incorporate new variables to collect and preserve new accounting data. I created a new table in scope with the new accounting fields, and added new variables to the variable set, but I am not sure how to use the new variables to populate new records in the new table.

I want to follow best practices, do I create a business rule and somehow send the variables' values to a new record in the new accounting table with an Insert trigger? Do I create a Flow to do this?

His application is very advanced and while I am learning a lot by examining his work, my deadline is fast approaching and I need to deliver the changes quickly.

Ankur Bawiskar · ‎10-18-2023

@woodyfairley

try this

(function executeRule(current, previous /*null when async*/ ) {

	var req = new GlideRecord('sc_req_item');
	req.get(current.sys_id);

	//gs.info("Org Code: " + current.org_code + "\nProject Number: " + current.project_number + "\nTask Code: " + current.task_code + "\nFiscal Period: " + current.fiscal_period);


	if (!gs.nil('' + req.variables.cao_payment_and_billing)) {
		var segs = '' + req.variables.cao_payment_and_billing;
		segs = JSON.parse(segs);

		for (var x = 0; x < segs.length; x++) {
			//this should create the new record in the BAS Accounting Segments table;

			var segmentsBAS = new GlideRecord(x_g_cao_print_serv_bas_accounting_segments);
			segmentsBAS.addQuery('bas_task', segs[x].bas_task);
			segmentsBAS.query();
			if (!segmentsBAS.next()) {
				segmentsBAS.initialize();
				//segmentsBAS.agency = current.variables.agency;
				//segmentsBAS.print_request = segs[x].sys_id;
				segmentsBAS.bas_project = segs[x].bas_project;
				segmentsBAS.bas_task_code = segs[x].bas_task_code;
				segmentsBAS.bas_organization = segs[x].bas_org_code;
				segmentsBAS.bas_object_class = segs[x].bas_object_class;
				segmentsBAS.bas_budget_fiscal_year = segs[x].bas_budget_fiscal_year;
				//var newSegmentsBAS = segmentsBAS.insert();
				segmentsBAS.insert();
			}
		}
	}

})(current, previous);

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

Regards,
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader

View solution in original post

AshishKM · ‎10-17-2023

Hello @woodyfairley

You can find out the table name from portal widget ( if portal based app ) , there should be some business rule with the same name ( as app ) try to find out, also if only one ( or few ) field has change use the same table with same logic and a new field ( column ) in the same table and update the related logic ( BR or widget based ).

Creating a entire new table will increase your work and time.

We can surely help if you can provide some more technical details like data diagram, application flow , application forms via screenshot.

Please mark this response as correct and helpful if it helps you can mark more that one reply as accepted solution

woodyfairley · ‎10-18-2023

Thank you Ashish, you were correct, it is a business rule that creates the accounting table from the variables, but the old accounting table was created as a reference table and we are unable to use it any longer. I created the new accounting table in the scope using Studio, and the relationship, as you can see in the screenshots the RITM contains the new variable fields I created in the variable set, and I added the table to the bottom as a related list, but the values do not populate the new accounting table, i think it is the script I created.

Finally, I was able to get it working by duplicating the original developer's business rule script, but with the tweaks offered by Ankur (see the accepted solution and my pasted code). It worked with a multirow variable set, added to all of the catalog items, with adjustments to the UI Policies to hide the deprecated accounting fields and variables, and show the new MRVS. Thank you for your help and support!

AshishKM · ‎10-18-2023

@woodyfairley thanks for replying and explaining all details, at the end your issue resolved. Ankur Sir, he has great knowledge and experience, when he is replying be sure for some solution.

Please mark this response as correct and helpful if it helps you can mark more that one reply as accepted solution