DocumentQuery search a keyword value that may be blank.

[Stephen_Wanhell]

I'm having some trouble wrapping my head around blank or null keywords in onbase.

For my query, i have a legacy keyword that indicates a legacy document.

I need to search for documents where legacy keyword <> 'LASERFICHE'

so i create a keyword of type legacy with the value 'LASERFICHE' and then do:

legacyKeywordType.createKeyword("LASERFICHE");

docQuery.AddKeyword(legacyKeyword, ,KeywordOperator.NotEqual,KeywordRelation.And)

This doesn't seem to work AND i've notice that attempting to perform the same search through my unity client (ie setting the operator to <> and the text to "LASERFICHE" also doesn't work.

by "Doesn't work" i mean it will NOT return the documents with null legacyKeywords.

Do i need to also create legacyKeywordType.createBlankKeyword() and add it to the doc query -> which i have tried and that doesn't work either?

HELP!  currently my workaround is to leave that keyword OUT of the query and filter the returned documents afterwards, which of course is crazily inneficient. 

[Daniel_Quill · Accepted solution]

Stephen,

In OnBase there is no concept of NULL keyword values unless you are referring to KeywordGroups. This is because if a value does not exist on a document then there is no row in the database which means there is nothing to return. Using the <> will not work because there is nothing to compare your criteria value to. The <> only works if there is a value already assigned to the keyword type on document.

So the keyword is not blank or NULL, it simply does not exists. You will need to return the documents and loop through but instead of looking for the value you would want to look for the keyword type to see if it exists on the document. When looping through the keywords you will notice that any keyword that does not have a value associated with it will not be returned.

Regards,

[Stephen_Wanhell]

I think my solution might be to use a customquery with SQL.  that way i can exclude my empty rows in the custom query:

select * from hsi.itemdata where
hsi.itemdata.itemnum not in (select itemnum from hsi.keyitem146 where keyvaluechar == 'LASERFICHE')

pull in the custom query:

CustomQuery customQuery = app.Core.CustomQueries.Find("Keyword filtering custom query");
if (customQuery != null)
{
docQuery.AddCustomQuery(customQuery);
}

and then continue to add keywords or document types for the 'required' fields.

[Daniel_Quill]

Stephen,

Not sure why I did not think of this in the first place but it dawned on my when I saw the mention of the of the document query. You can use the document query to return your list of documents but if you use the DocumentQuery.ExecuteQueryResults() method you can return the Displaycolumn object which would be null if empty.

The Displaycolumn object mimics the Custom Query function of the "Display Column". In the Unity API you can add the Displaycolumns to the document query via the DocumentQuery.AddDisplayColumn() method.

Now, when you get the results back from the document query you will still need to loop through the document but from the Displaycolumn you will know if the item is null or not and can process accordingly.

This may be a cleaner solution than maintaining a SQL query and it would be using all native OnBase functionality.

Regards,

[Thomas_Reu]

if you create a view you can get the nulls to use for exlusion or inclusion, depending on what you want.

for example:

select i.itemnum, i.itemname, a.keyvaluechar

from hsi.itemdata as i

left join hsi.keyitem101 as a

on i.itemnum = a.itemnum

where a.keyvaluechar is null (or not null if that is what you want)