VMware Cloud Community
MarkCrossley
Enthusiast
Enthusiast
Jump to solution

vCAC 6.0.1 - Advanced Service Request Form

I want to create an advanced service that calls out to a vCO workflow. Simple enough, it works fine.

However I now want to add some validation to the blueprint forms before the user hits 'submit'. This falls into two areas...

1. There are a number of boolean form fields, I need to make sure these are 'ticked' before the form can be submitted (the user agrees to some conditions etc)

Now the form field constraints only seem to be able to make fields mandatory (and that doesn't apply to booleans anyway), not be able to check for a mandatory value ('Yes' in this case).

I can obviously check them as part of the workflow, but then it has to be failed and the user re-input the request again.

2. I have another field that I want to make searchable in a SQL database, I'm thinking I want to call out to a powershell script to achieve this, but if I make the search field of entity type Powershell:xx there is no obvious way of specifying what you want to call? The help files aren't much help either. Or can I create a custom resource, link to the db with a custom query? Any pointers please?

Cheers

Tags (3)
1 Solution

Accepted Solutions
MarkCrossley
Enthusiast
Enthusiast
Jump to solution

I hope this gives you view of what I am doing...

The two actions called are pretty standard SQL code...

Action Definition: Array/string findRecord1ByColumnQuery(string query, SQL:Table table, string column)

if (query === "") {

  return ["Please enter search text!"];

}

var select = "SELECT DISTINCT " + column +

  " FROM " + table.name +

  " WHERE " + column + " LIKE '%" + query + "%'" +

  " AND STATUS = 'Active'";

var actionResult =  table.database.readCustomQuery(select);

var myArray = [];

var i = 0;

if (actionResult.length > 0) {

  for (; i< actionResult.length; i++){

  myArray.push(actionResult[i].getProperty(column));

  }

} else {

  myArray = ["No records found!"];

}

return myArray;

and

Action definition: Array/string findRecord2ByColumnQuery(SQL:Table table, string column, string conditionCol, string conditionVal)

if (conditionVal === "") {

  return ["No project code!"];

}

var select = "SELECT DISTINCT " + column +

  " FROM " + table.name +

  " WHERE " + conditionCol + " = '" + conditionVal + "'";

var actionResult =  table.database.readCustomQuery(select);

var i = 0;

var myArray = [];

if (actionResult.length > 0) {

  for (; i < actionResult.length; i++) {

  myArray.push(actionResult[i].getProperty(column));

  }

} else {

  myArray = ["No records found!"];

}

return myArray;

View solution in original post

9 Replies
rszymczak
Hot Shot
Hot Shot
Jump to solution

For part 2 of your question: you're getting it wrong.

The search field will search within your vCO inventory for the given entity. So, you probably would be able to add a PS Host to vCO and search for PowerShell:Host entities. However - with the current version of the PS Plugin it would not be able to "search" a PowerShell Script which you want to run. I don't think it will be added to future versions since the methology is different: you're supposed to run workflows which then may call PowerShell scripts. At the current version however the search field seems only to search for inventory elements, so you'll not be able to search for "workflows" (which wouldn't make much sense for a number or reasons anyway).


The easiest way would be creating a dynamic type  which would basicly be a string representing the ps-script name to run and which you could search from within vcac since it would be a inventory object. then you would create a workflow which takes the input inventory object and useses the string property to select the ps script to run. the result would always have to be of the same type, so I'm not sure if thats fine for you.


however, I don't think that's what you really need. do you really want the vcac user to dynamicly pick the PS script to run? Reading what you say the goal is to make a searchable SQL database. The default sql plugin won't do what you need since it dosn't list table entries. but you may build your own plugin or use dynamic types to build something that would query your sql database and create inventory entries from it which you can search from vcac. i'm not sure how big your sql database is, but you should know that if you have alot of entries it will be quite slow so you should implement some caching (thinking about it, dynamic types may not be powerfull enough yet for this).


also you would need to use the new resourceelement class to save your custom objects since the "old fashioned" way of doing this, parsing from and to xml, would probably be way to slow for your needs.

Reply
0 Kudos
MarkCrossley
Enthusiast
Enthusiast
Jump to solution

Hi I think you mis-understood my requirement slightly.

Anyhow, I managed to achieve what I wanted, albeit with the addition of another field to the form.

The new field is just for the user to enter a 'search term'.

Next the data field I want populated has in vCO presentation a "Predefined list of elements" defined to call an action using OGNL, this uses the 'search term' as a parameter to the action.

This action then performs a query on the database, and returns an array of items from the database table that match the 'search term', these populate the predefined items on the data field dropdown list so the user can select from them.

Sightly clunkier than the in-built search feature, but it works OK.

----

Actually I then use the selected data item to perform further lookups in the database and populate other fields on the form as well. It all works well, but is a little slow displaying updates on the vCAC form.

Message was edited by: MarkCrossley Clarification of wording.

Reply
0 Kudos
rszymczak
Hot Shot
Hot Shot
Jump to solution

Huh? I was thinking OGNL is deprecated?

However, I find it quite interesting how you managed to re-define the search field in vCAC. I think it would be quite interesting for many people if you would share how you did it exacly (maybe with a screenshot for better understanding?). I'm still not sure if I fully understood what you did there, but it sounds like a evil hack so it has my attention 🙂

Reply
0 Kudos
MarkCrossley
Enthusiast
Enthusiast
Jump to solution

I hope this gives you view of what I am doing...

The two actions called are pretty standard SQL code...

Action Definition: Array/string findRecord1ByColumnQuery(string query, SQL:Table table, string column)

if (query === "") {

  return ["Please enter search text!"];

}

var select = "SELECT DISTINCT " + column +

  " FROM " + table.name +

  " WHERE " + column + " LIKE '%" + query + "%'" +

  " AND STATUS = 'Active'";

var actionResult =  table.database.readCustomQuery(select);

var myArray = [];

var i = 0;

if (actionResult.length > 0) {

  for (; i< actionResult.length; i++){

  myArray.push(actionResult[i].getProperty(column));

  }

} else {

  myArray = ["No records found!"];

}

return myArray;

and

Action definition: Array/string findRecord2ByColumnQuery(SQL:Table table, string column, string conditionCol, string conditionVal)

if (conditionVal === "") {

  return ["No project code!"];

}

var select = "SELECT DISTINCT " + column +

  " FROM " + table.name +

  " WHERE " + conditionCol + " = '" + conditionVal + "'";

var actionResult =  table.database.readCustomQuery(select);

var i = 0;

var myArray = [];

if (actionResult.length > 0) {

  for (; i < actionResult.length; i++) {

  myArray.push(actionResult[i].getProperty(column));

  }

} else {

  myArray = ["No records found!"];

}

return myArray;

rszymczak
Hot Shot
Hot Shot
Jump to solution

Really nice - didn't know you could use validation like that and pass it over to vCAC.

Reply
0 Kudos
MarkCrossley
Enthusiast
Enthusiast
Jump to solution

Glad you like it, it may be useful one day.

And to answer myself for part 1 of my original question. I ended up making the input field a string, making the field type a radio button group in vCAC, and creating a single predefined answer of 'Yes'.

This shows the field on the form with a label, an unchecked radio button, and the text "Yes".

Capture.PNG

This can then be made mandatory, the only downside is that once the radio button is checked it cannot be unchecked, but this is for a user declaration that they have read the T&Cs, if they change their mind after checking it they can always cancel the whole request Smiley Wink

SeanKohler
Expert
Expert
Jump to solution

Hi Mark.

Where are these actions or custom queries defined?  I am missing something, but we are also looking for a method to have an ASD field with searchable data (from an MS SQL Database column).

Sean

Message was edited by: SeanKohler So I did find where you were implementing it through a custom action.   Thanks a lot for providing this lead. Trying to reproduce your results.

Reply
0 Kudos
SeanKohler
Expert
Expert
Jump to solution

I was able to fully duplicate your test for item 2.  Thanks again for sharing what you found.

Reply
0 Kudos
MarkCrossley
Enthusiast
Enthusiast
Jump to solution

Glad you found it useful, looking back maybe my description of the solution could be a bit clearer.

Reply
0 Kudos