Custom Query + Definable parameters

[Ryan_Evoniuk]

Hello,

I did a forum search but I couldn't find anything pertaining to my question specifically. I'm trying to build a custom query that will allow a user to query a doc type for a keyword that does NOT exist. This can done by using the Custom Written SQL option and the following statement:

hsi.itemdata.itemtypenum IN (1924)

AND NOT EXISTS (SELECT * FROM hsi.keyitem110 WHERE hsi.itemdata.itemnum = hsi.keyitem110.itemnum)

Unfortunately, it doesn't look like custom SQL accepts user definable parameters. I want the user to be able to select the doc type to run the query against. If I select the "By Document Type" option, OnBase can't query a keyword that doesn't exist with the stock logic.

I can easily create a custom report in Report Services with a selectable doc type parameter, but the user loses direct access to the document for quick action. Any ideas?

[AdamShaneHyland]

Hi Ryan,

Thanks for the post.

At this time, OnBase does not have a method to allow inputs/variables in a SQL Custom Query.  An option to work around this would be to create an HTML Custom Query where the Document Type select field is populated via a script onLoad.  This could then limit the values poplulated into the drop down which could in turn be used in your Custom Query along with the other keywords configured.

Take care.

[Tiffany_Nead]

Good morning,

I see that this post is from 2012. I am wondering if there has been any changes to custom queries to allow for a parameter/input/variable.

Thanks,

Tiff

[Ryan_Evoniuk]

Hi Adam,

Thanks for responding.

By HTML Custom Query, do you mean using an HTML e-form template in conjunction with the custom query options? The "Use HTML Form" option is greyed out when Custom Written SQL is selected.

If I use the "By Document Type" query type with an e-form, I still lose the capability to query a keyword that doesn't exist because I can't manipulate the logic behind that keyword query. Are you implying that I need to inject a SQL query into the HTML form itself for the NOT EXISTS logic?

[AdamShaneHyland]

Hi Ryan,

Originally, I was thinking that you could use an HTML Custom Query with scripting logic behind it, but this is not going to work since you are correct that it is not possible to use an HTML form to perform a Custom SQL Query.

Unfortunately I can't think of a way to do this so that you have access to an OnBase document hitlist.  I'm sure that it is possible through a custom HTML page or application using the API, but it sounds like you are looking to do this within the Client interface without having to create your own hitlist.

Maybe someone else can jump in with a good suggestion.

Take care.