DocumentQuery search criteria with multiple KeywordTypes and relations using Unity API

[Harish_Kamal_Me]

Hello All,

I am trying to come up with some logic to do a simple search search for Onbase documents with certain criteria using the Unity API.

Lets say I want to return documents that match the criteria, KewyordType1 = "A" and and KeywordType2 != "B". How would I do that?

I tried the following and it doesn't work as expected, the results show same documents twice for some reason.

documentQuery.AddKeyword(KeywordType1(A), KeywordOperator.Equal, KeywordRelation.And); //Criterion 1

documentQuery.AddKeyword(KeywordType2(B), KeywordOperator.NotEqual, KeywordRelation.And);//Criterion 2

Does the keywordTypes have to be the same when using Keyword Relations?

Another thing I tried to do is to use QueryKeywordRecordType

QueryKeywordRecord qryKw = onbaseApp.getCore().getKeywordRecordTypes().Find("Standard Keyword Types").CreateQueryKeywordRecord();

queryKw.AddKeyword(KeywordType1(A), KeywordOperator.NotEqual);

queryKw.AddKeyword(KeywordType2(B).CreateKeyword(7807696), KeywordOperator.Equal);

But since I don't have the KeywordType1 and KeywordType2 mapped to "Standard Keyword Types" I get an error. In fact KeywordType1 and KeywordType2 are not mapped to any keywordRecordTypes.

Any help on this appreciated.

Thanks,

Harish

[Scott_McLean]

Hi Harish,

What you're trying should work.

I tested your exact example, and it works properly for me. Have you also added document type(s) or a custom query to the DocumentQuery?

Here is a snippet of code I used to test this:

DocumentQuery dq = app.Core.CreateDocumentQuery();dq.AddDocumentType(app.Core.DocumentTypes.Find(101));dq.AddKeyword(app.Core.KeywordTypes.Find(1).CreateKeyword("TEST SET"),    KeywordOperator.Equal, KeywordRelation.And);dq.AddKeyword(app.Core.KeywordTypes.Find(102).CreateKeyword("TEST SET"),    KeywordOperator.NotEqual, KeywordRelation.And);DocumentList dl = dq.Execute(100);app.Diagnostics.Write(dl.Count.ToString());

This accurately found the documents I was searching for and wrote the count of retrieved documents to the Diagnostics Console.

[Harish_Kamal_Me]

Thanks for your response Scott. Yes, I've added DocumentType(s) to the query.

I think I now understand what my issue is. Our KeywordRecordTypes is set to "Stand Alone", this way we can have multiple instances of a KeywordType on the document. Now, when I do a search using the API, the results have documents, both that meet the criteria and also does not meet the criteria. I think I would need to use an example to explain this.

Lets say I have 4 documents with the following information.

DocumentID 1 :
KWType1 : A
KWType1 : B
KWType1 : C
KWType2 : Test

DocumentID 2:
KWType1 : B
KWType1 : C
KWType2 : Test

DocumentID 3:
KWType1 : A
KWType1 : C
KWType2 : Test

DocumentID 4:
KWType1 : B
KWType1 : C
KWType2 : Test

Now when I create a query
documentQuery.AddKeyword(app.core.KeywordTypes.Find("KWType1").createKeyword("A"), KeywordOperator.NotEqual, KeywordRelation.And);
documentQuery.AddKeyword(app.core.KeywordTypes.Find("KWType2").createKeyword("Test"), KeywordOperator.Equal, KeywordRelation.And);

I get count 7 with the following results
DocumentID 1
DocumentID 1
DocumentID 2
DocumentID 2
DocumentID 3
DocumentID 4
DocumentID 4

From the results, it looks like the API finds DocumentID 1 doesn't meet the criteria twice with multiple instances of KeywordType 'KWType1' on the document where it has values 'B' and 'C', and same with the other Documents. I understand I can use documentQuery.setDistinct(true) to get distinct results, the result count would still be all the documents, '4'.

I am trying to come up with something that only DocumentID 2 and DocumentID 4 are returned from my Query (Expected Result). Still trying to figure out how would I do that using the API.

[Harish_Kamal_Me · Accepted solution]

Rephrased question: 

Could you please advise/suggest how would I do a search on Onbase using DocumentQuery with certain criteria.

I think I understand how DocumentQuery works currently, but I’m trying to do something which I couldn’t figure how to do it with the Unity API. The easiet way to explain this is with an example (below).

I want to search Onbase to return documents with results matching criteria, KWType1 != ‘A’ and KWType2 = ‘Test’. Our KeywordRecordTypes is set to "Stand Alone", this way we can have multiple instances of a KeywordType on the document. Now, when I do a search using the API, the results have documents, both that meet the criteria and also does not meet the criteria

Let’s say I have 4 documents with the following information.

DocumentID 1 :

KWType1 : A

KWType1 : B

KWType1 : C

KWType2 : Test

DocumentID 2:

KWType1 : B

KWType1 : C

KWType2 : Test

DocumentID 3:

KWType1 : A

KWType1 : C

KWType2 : Test

DocumentID 4:

KWType1 : B

KWType1 : C

KWType2 : Test

Now when I create a query

documentQuery.AddKeyword(app.core.KeywordTypes.Find("KWType1").createKeyword("A"), KeywordOperator.NotEqual, KeywordRelation.And);

documentQuery.AddKeyword(app.core.KeywordTypes.Find("KWType2").createKeyword("Test"), KeywordOperator.Equal, KeywordRelation.And);

I get count 7 with the following results

DocumentID 1

DocumentID 1

DocumentID 2

DocumentID 2

DocumentID 3

DocumentID 4

DocumentID 4

From the results, it looks like the API finds DocumentID 1 doesn't meet the criteria twice with multiple instances of KeywordType 'KWType1' on the document where it has values 'B' and 'C', and same with the other Documents. I understand I can use documentQuery.setDistinct(true) to get distinct results, the result count would still be all the documents, '4'.

I am trying to come up with something that only DocumentID 2 and DocumentID 4 are returned from my Query (Expected Result). Still trying to figure out how would I do that using the API. Any advice/help on this appreciated.

I reached out to the API support on this and the answer there is not a way currently in the Unity API where we can filter results according the example I had above. We just have to check keywords on each document from the results.

I am posting the answer from API support here in case if it helps anyone else.

Unfortunately with the way the API works we are unable to generate a query that says exclude all documents that contain a keyword type with this value. This will work if that calue excluded is the only instance of that keyword on that document. But if there are multiple instances of that keyword type, you will still get back that document.

The way to get your result set that you are seeking is to perform your current query, and then check the keywords on each document to make sure that you limit the correct result set. Foe example:

List<Document> docList = newList<Document>();

                DocumentQuery documentQuery = app.Core.CreateDocumentQuery();

                documentQuery.AddDocumentType(app.Core.DocumentTypes.Find("docType"));

                documentQuery.AddKeyword(app.Core.KeywordTypes.Find("KWType1").CreateKeyword("A"), KeywordOperator.NotEqual, KeywordRelation.And);

                documentQuery.AddKeyword(app.Core.KeywordTypes.Find("KWType2").CreateKeyword("Test"), KeywordOperator.Equal, KeywordRelation.And);

                documentQuery.RetrievalOptions = DocumentRetrievalOptions.LoadKeywords;

                documentQuery.Distinct = true;

                QueryResult queryResult = documentQuery.ExecuteQueryResults(long.MaxValue);

                foreach (QueryResultItem item in queryResult.QueryResultItems)

                {

                    KeywordRecordList keyRecList = item.Document.KeywordRecords;

                    foreach (KeywordRecord keyRec in keyRecList)

                    {

                        Keyword testKeyword = keyRec.Keywords.Find("KWType1");

                        if(testKeyword!= null)

                        {

                            if(testKeyword.Value.ToString() != "A")

                            {

                                docList.Add(item.Document);

                            }

                        }

                    }

                }

This will return document two and four with your test items. 

[Tharon_Rivera]

I'm not sure how much you tested this for other examples, but it seems like it may not work correctly beyond your test example. Based on how the client shows multiple stand-alone keywords in ascending order, the Find function is only returning the first result it finds ("A" if ascending order is preferred). For example, if one of the documents you didn't want had another keyword value of "1", it could potentially be added to the docList. I'm not sure what the order is of the Find function is, but since it will only return 1 result, it seems like a crapshoot as to whether or not the Find result is going to be the value you are specifically trying to exclude.