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?

[Nick_McElheny1]

Hi Ryan,

Do you own the Unity Client?  If so, Unity lets you dynamically filter results, even from a SQL Custom Query, by document type.  The only thing is you'd have to refine your hsi.itemdata.itemtypenum IN statement to account for all the document types that you'd want returned and users would filter after the fact.  Depending on how many document types contain this keyword, the overhead might be too much to be worthwhile.

Take care,

Nick McElheny

Trover Solutions, Inc.

[Ryan_Evoniuk]

Hi Nickolas,

Thanks for the suggestion.

We do own the Unity Client and I previously wandered down that path to see if it would work. Unfortunately, the initial result set is just too large due to all of the document types that need to be included.

I'm trying to think of a creative way to accomplish this feat, until(if) Hyland creates functionality for user input on custom written queries. The tricky part is combining a UI with a query that utilizes the values from the UI. I wonder if an e-form coupled with a Unity Script would work? That would leverage the API within the Unity Client environment, but I'm not sure if a DocumentQuery object can be built in such a way that it would work for keywords that do NOT exist. I also haven't had much (other than the basics in API class) experience with using unity scripts, so I don't know if there's a way to pass the result set back to the Unity Client session (like it was run through a regular retrieval) that the script was run in.

[#4]

Hi Ryan,

I had a similar situation where I wanted to display a user selected document type from many document types in a workflow queue to allow the user to work only those specific document types.  It also can be used to search all documents in OnBase.

1. Create a keyword (I used DocumentType) and populate that with the Document Type name set to allow drop-down lists.
2. Create a Keyword as an External Data Set using a similiar SQL stametnt where XXX is the keyitem of the original DocumentType keyword: 

select distinct(k.keyvaluechar) from hsi.keyitem251 k join hsi.wflog q
on k.itemnum = q.itemnum
order by k.keyvaluechar

3. Add both keywords created in Steps 1 & 2 to all document types that are to be searchable

4. Create an HTML form with the External Data Set keyword configured as OBDataSet to allow your users to select the document type from a drop down

5. Add the appropriate user groups to the Custom Query Configuration

6. Create a Custom Query by Keyword using the HTML form created in Step 4.  Select 'Exclude Duplicate Documents' in the Adanced Options

7. In the Custom Query configuration select the 'Keyword' button and choose the name of the dataset keyword that was referenced in Step 4

8. There must be some way to add the Document Type to the DocumentType keyword as documents are created/imported into OnBase.

If this is to run against the whole OnBase database it could be a HUGE drag on performance.  You may consider restricting the query in Step 2 to documents in a specific workflow queue if that can be made to work in your environment. 

[Ryan_Evoniuk]

Hi Dave,

Thanks for your input, but that solution still can't account for keywords that don't exist. For standalone keywords, if there's no value associated to the field, there is no database entry, so it requires a custom written SQL statement because OnBase doesn't have that logic built into the other CQ methods. If custom written CQs allowed dynamic input by the user, this wouldn't be an issue.