Advanced Services: Using an Action to populate a dropdown?

[BScott66]

Hello all,

Hope everyone had a great Thanksgiving (If you celebrated!). Personally I think I gained about 18 pounds! :smileyblush:

I am trying to use the Advances Services to create a custom form that will allow me to take input and use it to create details about the workload to be deployed. Hopefully eliminating the need for many catalog items for a single flavor of an O/S to one where environment(Lifecycle), Server Tier (App, Web, DB, etc.) among other specifics can be selected and the appropriate machine prefix/hostname be assigned.

Where I am getting stuck is getting the Service Blueprint's form to use a vRO Action to query the database to get the unique Labels and Values for the dropdown.

I created a new action and in it I am doing the below:

This is a very generic query so I have named the Action QueryDatabase

As you can see what I am doing is returning the values from the database "corpa.cmdb" (The Database has been configured as a shared connection in Orchestrator.)

With a select statement "Select Designation from dbo.Env", I cut down returning both fields to see if that was the issue but both ways this has not worked.

The original select statement was "Select Designation, Description from dbo.Env", with the expectation for the dropdown the Designation would be used for the Value and Description would be used for the Label (So much for thinking logically!)...

While I can execute the query in SQLMGR and get the results I expect

What I am getting within the action is nothing (No results).

Do I have the wrong return type?

I have tried both Array/String and Array/SQL:ActiveRecord with the later not being accepted as a source for the dropdown.

Is my scripting incorrect?

Can someone help get me on the right track to getting this to work?

Assistance is greatly appreciated!

Thank you,

Bill S.

[sbeaver]

I do not have any in front of me to check or verify but at first glance in your action I am not seeing any inputs or anything pointing to your DB.  Now with that said if dev, lab, production ect are going to be the choices always used then why not present that list as predefined answers to populate the drop down?

[BScott66]

sbeaver,

Thanks for the reply...

The data I am looking to retrieve is fixed (At least for now!).

So the code contains the Database (corpa.cmdb) and Table (dbo.Env) to access:

var databaseParameter = "corpa.cmdb";

var query = "Select Designation, Description from dbo.Env";

return databaseParameter.readCustomQuery(query);

I have made an additional attempt since posted but I am still not getting any results!

Below is the latest code I am trying to use for the action:

var databases = SQLDatabaseManager.getDatabases();

//  a variable to hold our database

var database = null;

// loop through them until we find our database, using the friendly name we configured

for each (var db in databases) {

    if (db.name == "corpa.cmdb") {

        database = db;

        break;

    }

}

// a bit of checking to make sure our database was found

if (database == null) {

    throw "Unable to find database!";

}

var query = "";

query += "select";

query += "    Designation as Value,";

query += "    Description as Label";

query += " from";

query += "    dbo.Env";

query += " order by";

query += "    Value asc";

// execute the query

var result = database.readCustomQuery(query);

// a bit of info to log

System.debug("Database query for Environments returned " + result.length + " records");

var data = new Array();

for (var i = 0; i < result.length; i++) {

    System.debug("Found Environment: " + result[i].getProperty("Value"));

    data[i] = result[i].getProperty("Vale");

}

// return the data

return data;

Bill S.

[sbeaver]

Bill,

I took a look at one of my workflows that I use to create records in a SQL database and in my workflow I have a couple of inputs that may be of interest.

name = table --> type is SQL:Table

name = databaseResource --> type is ResourceElement

You are going to need to have inputs that point to your database.  That is the part I believe you are missing

[BScott66]

sbeaver.

Thank you for the reply..

You mention workflows which from my understanding workflows are not accessible from within a service blueprint, only actions.

So my first mistake was thinking that the drop-down was gong to be populated in the form developer as it is when you provide static values (Not correct!).

I published and ran the service blueprint and I got results! Not exactly the results I was looking for as only one column is being used instead of the 2, but that is due to my resultant return of one.

I guess I am unsure how I go about returning both columns to populate the Value and Label of the Drop-Down, Anyone?

The code I am using is listed below:

for each (var db in databases) {

    if (db.name == "corpa.cmdb") {

        database = db;

        break;

    }

}

// a bit of checking to make sure our database was found

if (database == null) {

    throw "Unable to find database!";

}

var query = "";

query += "select";

query += "    Designation as Value,";

query += "    Description as Label";

query += " from";

query += "    dbo.Env";

query += " order by";

query += "    Value asc";

// execute the query

var result = database.readCustomQuery(query);

// a bit of info to log

System.debug("Database query for Environments returned " + result.length + " records");

var data = new Array();

for (var i = 0; i < result.length; i++) {

    System.debug("Found Environment: " + result[i].getProperty("Label"));

    data[i] = result[i].getProperty("Label");

}

// return the data

return data;

I can change the data being returned to the Label or Value and it will return the list and populate the Drop-Down, but I am looking to have the Label be presented and the Value be used within the form.

Any help in getting this to work is welcome!

Thank you,

Bill S.

[kericmiles]

When you use a custom action to populate a field, the values and the labels on the form are both equal to what is returned. I have been using custom actions to populate these fields but have not found a way to change the values to be different from the labels using this method. You may have to settle with having your workflow translate the labels into the actual values once it is submitted.

[BScott66]

Kericmiles,

Thank you for responding...

Is you use have you found a way to have more than one value return in you array?

The only way I have been able to get the Drop-Down to accept the return is using an Array or Strings, anything else and I have not been able to get the function to accept it?

Thanks,

Bill S.

[kericmiles]

I'm not entirely sure what you are asking in your first question, I have only been able to get the dropdown control to accept a single array of numbers or strings to populate it and have it make sense. There are a few other things the dropdown will accept (such as an array of arrays) but the dropdown only displays the vRO object reference and not any of the elements in the array so anything besides strings and numbers probably won't be shown correctly.