how to use WFA webservices with SQL queries for input selection?

[mhofstet]

Hi Team

IHAC which has some workflows with SQL queries based on input fields. In the WFA gui, the SQL queries are running, based on the input. But, when the webservice front end is used, the SQL query is not executet?

How to leverage theses SQL queries for selection of array, aggr, vol, based on the input fields running as webservice workflows?

Thanks a lot

Marc

[mgoddard]

Hi Marc,

Are the SQL queries modifying the input typed in by the user? Or are they just to pre-fill a list to choose from? They should be the later..

The SQL in the GUI should only be used to make it easier to enter values, for instance by searching based on other fields.

For Web Services, I would recommend creating Filters/Finders for the selections you want to make. These can be called directly.

Each field would require its own Filter/Finder, and this is how you can handle inter-dependencies between fields.

Retrieving the workflow base object via web services will evaulate each UI SQL, but because there is no interactive input here, the dependancies cannot be evaulated. ie. If the pick list keys Volume by Array, then Arrays will be listed but Volume will be blank because the Array name will be empty string.

Hope that helps explain it. It short, Filters with Finders returning the values you want are the way to go to provide pick-lists to up-stream portals. You can set the sorting order if you need and other options with finders.

Note: Many of these filters and finders you need are probably already there. For rest calls take a look at <wfa>/rest/filters or <wfa>/rest/finders, and give the /test method a go.

Kind Regards,

Michael.

[mhofstet]

It's really just for the pre-filling of the next input field for easier selection of the values. That's why SQL queries are used for in this example.

The problem with the finders/filters is more with the accuracy of the data. Without any preview/testing of the workflow, you are not sure, that the process will execute based on the filter. With the SQL queries, you already know, that this input is valid.

Is it possible to validate the filter/finder input prior to run the workflow with some return codes?

Thanks a lot, this helped me a lot.

Marc

[sinhaa]

Marc,

     The parameter values can be refilled with the same SQL queries that you have for User-Input fields if you are going to call the WFA webservice for workflow execution from some Programmatic interface that can connect to WFA DB and run those queries and use the values to pass in as parameters values for the workflow and then execute it. Now you can do it easily using Powershell, I can give you an cmdlet/API that I use. But again you need to remember that the return of the a query execution will be a list of objects, just as you see in GUI. So as in GUI, you need to decide out of the available inputs, which one will you pick for your parameter, you would also need to build your logic in the code that out of the available list of objects return by the query, which one do you want to use in as your parameter value. This logic can as simple as "Always use the first one".

I can post an example script if you want.

[mhofstet]

this sound just awesome. Yeas please, post some samples... we will look into that and report feedback

[sinhaa]

Marc,

     Ignore my previous response. you don't have to do that. getAllWorkflows returns validation for user-inputs which are of type SQL query. Save the attached powershell script ( Credit to Mirko for the original script )

Modify the Username, password and uri fields according to your setup.

Execute it.It shows the list of valid values for the command parameter. Run it, its self explanatory.

sinhaa

[sinhaa]

Ahh.. my bad. the above attached script will display validations only if Your user-inputs are independent SQL queries. I'll post the  a general working example as soon as I finish it, working on it.