How can we add dynamic dropdown choices to the variable in catalog?

Community Alums · ‎06-10-2020

Hi All,

How we can add dynamic dropdown choices to a variable without any custom table. I have one orchestration activity and getting output response as below.

{  "databasename":"",
   "datasource":"testdatasource",
   "instancename":"dev123",
   "output":"\n\nname\n----\nA\nABC_Test\nABC_test0\nABC_test0_TDE\nABC_test1\nABC_test2\nABC_test3\nABC_test3_new\nABC_test5\nABC_test6\nABC_test7\nABC_test8\nABC_TEST9\ntest0520\ntest1001\ntest1002\ntest1003\ntest1004\ntest1006\ntest1007\ntest500\ntest501\ntest502\ntest503\ntest504\ntest505\ntest506\ntest507\ntest508\n",
   "serverInstance":"testSERVER,143",
   "sqlPort":"143398",
   "sqlquery":"SELECT name FROM master.sys.databases WHERE name NOT IN('master','msdb','model','tempdb','AdminDB')"

}

I need to take "output" from this json then split with \n. The remaining answers/responses after splitting with \n i should add as a drop down choices.

Is it possible?

Regards,

Sirraj

Harsh Vardhan · ‎06-10-2020

where are you storing the response ? are you using script include to fetch he response of json ?

are you able to get the json response in client script ? if yes then use the same logic part which i had mentioned , so instead glide record , you will directly use addOption() to set the choices in your client script.

you have to write here onChange catalog client script on your database name variable and you will get json response and parse it and then set it using addOption()

eg:

var json= {
"databasename":"",
"datasource":"testdatasource",
"instancename":"dev123",
"output":"\n\nname\n----\nA\nABC_Test\nABC_test0\nABC_test0_TDE\nABC_test1\nABC_test2\nABC_test3\nABC_test3_new\nABC_test5\nABC_test6\nABC_test7\nABC_test8\nABC_TEST9\ntest0520\ntest1001\ntest1002\ntest1003\ntest1004\ntest1006\ntest1007\ntest500\ntest501\ntest502\ntest503\ntest504\ntest505\ntest506\ntest507\ntest508\n",
"serverInstance":"testSERVER,143",
"sqlPort":"143398",
"sqlquery":"SELECT name FROM master.sys.databases WHERE name NOT IN('master','msdb','model','tempdb','AdminDB')"
}

var ab = JSON.stringify(json);
var obj = JSON.parse(ab);

var arr = obj.output.split('\n');
for(var k in arr){
if(arr[k] != ''){

g_form.addOption('Variable NAme' ,arr[k],arr[k]);

}
}

View solution in original post

Harsh Vardhan · ‎06-10-2020

\n treated as line change. once you will run the below query you will see the result in background script.

var json= {
"databasename":"",
"datasource":"testdatasource",
"instancename":"dev123",
"output":"\n\nname\n----\nA\nABC_Test\nABC_test0\nABC_test0_TDE\nABC_test1\nABC_test2\nABC_test3\nABC_test3_new\nABC_test5\nABC_test6\nABC_test7\nABC_test8\nABC_TEST9\ntest0520\ntest1001\ntest1002\ntest1003\ntest1004\ntest1006\ntest1007\ntest500\ntest501\ntest502\ntest503\ntest504\ntest505\ntest506\ntest507\ntest508\n",
"serverInstance":"testSERVER,143",
"sqlPort":"143398",
"sqlquery":"SELECT name FROM master.sys.databases WHERE name NOT IN('master','msdb','model','tempdb','AdminDB')"
}

var ab = JSON.stringify(json);
var obj = JSON.parse(ab);

gs.print(obj.output);

output.

Community Alums · ‎06-10-2020

I am able to get this output. But how i can insert this answer to a drop down variable as a choices.

Harsh Vardhan · ‎06-10-2020

is it one time insertion ? you are going to insert the choices on each response ?

Harsh Vardhan · ‎06-10-2020

for sys_choice table, below code i had created

var json= {
"databasename":"",
"datasource":"testdatasource",
"instancename":"dev123",
"output":"\n\nname\n----\nA\nABC_Test\nABC_test0\nABC_test0_TDE\nABC_test1\nABC_test2\nABC_test3\nABC_test3_new\nABC_test5\nABC_test6\nABC_test7\nABC_test8\nABC_TEST9\ntest0520\ntest1001\ntest1002\ntest1003\ntest1004\ntest1006\ntest1007\ntest500\ntest501\ntest502\ntest503\ntest504\ntest505\ntest506\ntest507\ntest508\n",
"serverInstance":"testSERVER,143",
"sqlPort":"143398",
"sqlquery":"SELECT name FROM master.sys.databases WHERE name NOT IN('master','msdb','model','tempdb','AdminDB')"
}

var ab = JSON.stringify(json);
var obj = JSON.parse(ab);

gs.print(obj.output.split('\n'));
var arr = obj.output.split('\n');
for(var k in arr){
if(arr[k] != ''){
var gr = new GlideRecord('sys_choice');
gr.initialize();
gr.name='incident';
gr.element='u_custom_field'; // my custom column
gr.value=arr[k];
gr.label=arr[k];
gr.insert();
}
}

result

Hope it will help you.

If my answer helped you, kindly mark it as correct and helpful.