Setting variable based off of two other variables all in a custom table

[erin-marie]

I have a custom table which contains four columns:

Application

Department

Approver

AD Group

On the catalog item, the user selects the desired application and department.  I then have an approval that uses these two variables to look up and set the the approver from the table.  This works great.  I now want to add a hidden or read-only field that will take these same two variables from the item and look and and set this value.

So if user picks Application A and Department 1, the new variable should be set to something.  If they pick Application B and Department 2, then the new variable would be set to whatever the AD value is in the lookup table.

I tried it as an OnChange Client Script, but saw on here that wasn't recommended.  So I am doing a GlideRecord run script in the workflow.  This similar script works for the approval, but I am just not sure how to set the new variable.

// Set the variable 'answer' to a comma-separated list of user ids and/or group ids or an array of user/group ids to add as approvers.
//
// For example:
// answer = [];
// answer.push('id1');
// answer.push('id2');

// get value of variable
var adobe_application = current.variables.sc_adobelicense_adobe_application;
var adobe_license_dept = current.variables.sc_adobelicense_department;

// get approver of application
var gr = new GlideRecord('u_adobe_license_approvers');
gr.addQuery('u_choice_1',adobe_application);
gr.addQuery('u_reference_3',adobe_license_dept);
gr.query();

//var adobeapprover = '';
//if (gr.hasNext()) { gr.next(); adobeapprover = gr.u_reference_1; }

var securitygroup = '';
if (gr.hasNext()) {
gr.next();
securitygroup = gr.u_ad_security_group;
gForm_setVariable('current.variables.sc_adobe_ad_group', securitygroup); }

// answer populated
answer = [];
answer.push(securitygroup.toString());

TY,

Erin

[Shruti · Accepted solution]

Hi,

It can be achieved by using catalog data lookup definitions

Open the catalog item. Under Catalog data look up definitions related list create a new definition

Select custom table as the matcher table and save it

Under the related list create 2 catalog data matcher variable definitions. One for Application variable and other for department

Create a catalog variable setter definition for AD group

Also create 2 catalog on change client scripts on application and department variables 

function onChange(control, oldValue, newValue, isLoading) {

   if (newValue == '') {

      return;

   }

  if(oldValue!=newValue){

    g_form.clearValue('sc_adobe_ad_group');

  }

}

[erin-marie]

This worked like a charm except for the clear scripts.  I am still trying to get those working.

[Shivaa1]

Hi, thanks for sharing, that’s good to know.

Mini Militia App Lock