API to Query for document by a Keyword containing an unknown number of leading zeros.

[Glenn_Engelbart]

I'm using the API to try to find a document based on a keyword that is stored  with an unknown and varying length and amount of leading zeros.   I tried DocumentQuery.AddKeyword adding a keyword value that was a regular expression  KeywordSearchValue= "^0*(" + KeywordSearchValue.TrimStart('0') + ")$".   That didn't work, returning 0 docs so I'm assuming that regular expressions can't be passed as keyword values into DocumentQueries.  My other option is to just search for "*"+KeywordSearchValue and then filter through the documents I get back.   I'm just wondering if there is a better way to do this by passing a better query back to OnBase, rather than filtering through documents using.NET code after getting the documents back from OnBase query.

[Alex_French · Accepted solution]

The basic behavior of a DocumentQuery is very similar to using Document Retrieval in the Unity Client GUI... so if you can't do something (as simple as finding docs with*out* a keyword value) in the GUI, you'll have to go past the basic DocumentQuery and inspect the results.

You might be able to make your life a little easier using Display Columns.  This is a lot like setting up a Custom Query with columsn to be displayed.  It lets you get a list of information about docs back, *without* using the API to dig into Keywords in the usual way.

You can add Keywords:

myDocQuery.AddDisplayColumn(myKeywordType);

And some other document metadata, like:

myDocQuery.AddDisplayColumn(DisplayColumnType.DocumentID);

Like in a Custom Query, information with multiple values (regular keywords or MIKGs) will cross-multiply into multiple rows.  If a document has two [Name] keyword values and two [Account Number] keyword values, and you setup both as DisplayColumns, your results will now include four rows for one document.

But using the DisplayColumns may make it easier (and maybe more efficient) to inspect the results.  Instead of looking over keywords, you can do something like:

DisplayColumn myDisplayColumn = myQueryResultItem.DisplayColumns.Find("My Display Column Name");

if (!myDisplayColumn.IsBlank)

{

//do something with...

myDisplayColumn.AlphaNumericValue;

}

I like to use a handy extension method like this:

public static string GetString(this DisplayColumnList thisDisplayColumnList, string displayColumnName, string defaultValue = "")
        {
            DisplayColumn thisColumn = thisDisplayColumnList.Find(displayColumnName);
            return thisColumn.IsBlank ? defaultValue : thisColumn.AlphaNumericValue;
        }

So my main business logic can just be:

myQueryResultItem.DisplayColumns.GetString("My Display Column Name")

Your API class teacher might say the .Find() in the extension should be followed by a null check, but I'd claim that isn't really a run-time error, and if I get it wrong I'll accept a less helpful exception message about a null reference, in exchange for not cluttering my code with one more null check.

If you have a large number of Documents, and a large number returned in the DocumentQuery before sorting in-memory, then using DocumentQuery this way may get very inefficient.  If it gets inefficient enough that it is a practical concern, your next option is to identify Docs of interest using a direct SQL query and then retrieve just what you need by Document ID with the API.

...

Depending on the number of leading zeroes you might have and how consistent the formatting is, you might also solve your immediate need much more simply:

myDocumentQuery.AddKeyword("KWName", "12345", KeywordOperator.Equal, KeywordRelation.Or);

myDocumentQuery.AddKeyword("KWName", "012345", KeywordOperator.Equal, KeywordRelation.Or);

myDocumentQuery.AddKeyword("KWName", "0012345", KeywordOperator.Equal, KeywordRelation.Or);

myDocumentQuery.AddKeyword("KWName", "00012345", KeywordOperator.Equal, KeywordRelation.Or);

myDocumentQuery.AddKeyword("KWName", "00012345", KeywordOperator.Equal, KeywordRelation.Or);

...

[Glenn_Engelbart]

Thanks for the response, and the detailed code.  I thought about adding a keyword like you did for each possible number of 0's. But because I didn't know how many 0's there could be Instead I went with parsing the results.:
List filteredDocuments = new List();
DocumentList unfilteredDocuments = docQuery.Execute(resultSetSizeToPass);

//Throw out any documents that match *AccountNum where * isn't equal to 0 padding.
string docAccountNum;
AccountNum=AccountNum.TrimStart('*'); //remove wildcard used for doc query.
foreach (var document in unfilteredDocuments)
{
foreach (var keyRec in document.KeywordRecords)
{
foreach (Keyword key in keyRec.Keywords)
{
if (key.KeywordType.ID == keywordIdAccountNumber)
{
docAccountNum = key.AlphaNumericValue;
if (Convert.ToInt64(docAccountNum) == Convert.ToInt64(AccountNum))
{
filteredDocuments.Add(document);
}
}
}
}
}
The trick was I had to add the true matching documents to a List instead of a DocumentList.