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

[Community Alums]

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

[Ankur Bawiskar]

Hi Sirraj,

below would be the best approach; not tested though

1) make API call

2) Iterate over the values & create choices on that variable using script include if the choice value not already found for that variable

3) then script include would return a json object containing choice values and labels you just now created

4) client script would then iterate over and use g_form.addOption()

I think drawback would be there would be lot of choices for this variable in backend

Mark ✅ Correct if this solves your issue and also mark 👍 Helpful if you find my response worthy based on the impact.
Thanks
Ankur

[Community Alums]

@Harshvardhan @Ankur Bawiskar @creativethinker 

Hi All,

Just want to add some more input here. Please refer the below screenshot.

So based on "SQL Server Name", "Instance Name" will populate (not an orchestration)..!!

Based on both server name field and instance name field, "Database Name" we should set as a choices by getting response from orchestration.

So whatever field values i select on previous two fields, i will get different response for database as per the combination.

I don't want store in "question choices field" just need to use add option to set values for the field.

Regards,

Sirraj

[Harsh Vardhan · Accepted solution]

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]);

}
}

[Community Alums]

Thanks Harsh, It worked. 🙂

[Ankur Bawiskar]

Hi,

So database name is drop down but you don't want to store the question choices for that?

 I believe database names will come from API after you parse the json response

You will require something to hold the values

Since you said you will get different database as per combination below approach you can try

1) don't set database name variable as drop-down

2) create custom table and make database name variable of type reference to this table

3) create only 1 single field in that table as name and set it as display=true

4) whenever you get API response check if database name exists in that custom table field; if not then create new record; if yes then don't create

In this way user can then select the Database name from the lookup as well

This solution would be scalable since it won't require creating many question choices for variable

If my answer solved your issue, please mark my answer as ✅ Correct & 👍Helpful based on the Impact.

Regards
Ankur