Document Query - Look for blank value Keywords

[Vandana_Gavini]

DocumentQuery dq = app.Core.CreateDocumentQuery();

dq.AddDocumentType(app.Core.DocumentTypes.Find("AP – Invoice Spreadsheet"));

// I'm trying to find documents that have a blank value for the Status Update field, and update it later
dq.AddKeyword("Status Update", "", KeywordOperator.Equal, KeywordRelation.Or);

DocumentList dl = dq.Execute(dq.ExecuteCount());

Question:  The above query returns all documents in the selected document type, which is not what I want.  I am now getting the DocumentList and iterating through it and filtering out, which I find very inefficient.  Is there any other way?

[Christie_Rasiah]

Hi Vandana,

You could try with a Custom Query type Custom Written SQL

DocumentQuery dq = app.Core.CreateDocumentQuery();
dq.AddCustomQuery(app.Core.CustomQueries.Find("AP – Invoice Spreadsheet - Empty Status"));
DocumentList dl = dq.Execute(dq.ExecuteCount());

Shankar.

[Vandana_Gavini]

Hi Shankar,

Thank you for your response.  I suppose there is no method to retrieve the documents with a blank value because the keyword table record is not created for a blank value?

I'm trying the custom query route, but how do you specify 2 tables and a join in the Customized Custom Query UI? 

The DocumentID is in itemdata table and the check for null comes from keyword table?

select itemnum
from itemdata
left outer join keyitem197 on keyitem197.itemnum = itemdata.itemnum
where itemdata.itemtypenum = 214
and keyitem197.itemnum is null

Thanks much!

Vandana