Jump to content

Data Provider - SQL Query support to provide dynamic list of values


Martyn Houghton
 Share

Recommended Posts

Linked to our previous post (link at the bottom of this one), we want to implement a progressive capture process which one of the fields is a dynamic list of values which we want to create using a database direct SQL query on the Hornbill Instance, rather than having to duplicate data in simple lists which is not practical.

We are wanting to query a subset of organisations which have a certain industry type, to be used as the source for the dynamic drop down selection. This is in support of our previous post (link at the bottom of this one), where we are trying to implement Internal Services Requests that would be raised by our Project Management Team to manage subscription of services through Hornbill itself automatically, rather than having this to be done manually which is both a overhead and can be prone to errors.

I can see that the ability to create dynamic list from a database direct sql query would be useful for other users and organisations as well. It would need to support the returning of two columns, one as the Raw Value, i.e. organisation ID, and a display value, i.e. organisation name.

Cheers

Martyn

 

 

Link to comment
Share on other sites

+1 - though this may go against what Hornbill are attempting to offer in their product (no code where possible etc.)... however if the option is there for advance users I would most definitely use it.

  • Thanks 1
Link to comment
Share on other sites

  • 2 years later...
  • 2 weeks later...

Wow it's been a while for this one.  Hopefully, it's on the roadmap in some form or another.

I think a solution to making this as user-friendly as possible but still allowing advance users to use, is having a way to enable users to be able to define Custom Views, much like how these can be configured in SQL, then being able to use the Data Provider to select from a list of Custom Views... this would help immensely achieving this request.

It would need to have a set of rules/validation in place to make sure that it doesn't cause system-wide performance issues... plus points that Martyn made in the original post:

  1. Adhere to row limits or enable pagination
  2. Only SELECT statements can be used
  3. Only accept two columns in the SELECT statement, a Value column and Display column and these must be named appropriately (or the query can be generated from populated fields).
  4. A timeout would need to be defined to a reasonable limit
  5. A "Test" button to check the outcome of the statement before making it live.
  6. Version Control (like in the ICF/BPM)

What are your thoughts?

  • Like 1
Link to comment
Share on other sites

@samwoo

Having the above controls would allow a 'managed' means of allowing us to derive simple lists from the data held within the Hornbill Platform. For example we trying to use the the Update Request > Service node to change the service and catalog item of a request to correct when it got logged under the wrong one, so being able to present a dynamic list of the services the customer is subscribed too would make this much easier.

We would need the ability to insert variables to make it context aware.

Cheers

Martyn

 

 

  • Like 1
Link to comment
Share on other sites

  • 6 months later...

@Paul Alexander I don't think Harry Hornbill can respond :unsure: he's busy flying around the world popping up on Hornbill instances.

This topic has been tagged as enhancement so we can only have our fingers crossed there has been discussions around this.

Link to comment
Share on other sites

@Paul Alexander @samwoo

I too have started referencing @Harry Hornbill with the change in role of @James Ainsworth and @Steven Boardman away from product management, given we do not seem to have a replacement contact to chase or escalate on the forum to updates on enhancement requests, but as Sam says he seems to out of the office.

Cheers

Martyn

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...