Distinct setting on DocumentQuery with MIKGs

[Kyle_Kranda2]

I've seen this before and had hoped it would be fixed by now, but is there a way to prevent a document query based on a value in a MIKG from returning every possible iteration of the keyword record for each document as a result.

Unity API v12.0.1.128

For example:
   Document 1
        MIKG1 KW1 = FALSE
        MIKG1 KW2 = FALSE
   Document 2
        MIKG1 KW1 = FALSE
        MIKG1 KW2 = FALSE
        MIKG1 KW3 = FALSE
        MIKG1 KW4 = TRUE

My Code:
QueueQueryOptions oQueueQuery = oQueue.CreateQueueQueryOptions();
oQueueQuery.Filter = app.Core.CreateDocumentQuery();
oQueueQuery.Filter.AddKeyword(oProcessedKwt.CreateKeyword("FALSE"));
oQueueQuery.Filter.Distinct = true;

DocumentList oQueueDocList = oQueue.GetDocumentList(oQueueQuery, oQueue.GetDocumentCount(oQueueQuery), DocumentRetrievalOptions.LoadKeywords);

The oQueueDocList will have 5 instances of those 2 documents returned. This seems illogical to me. Now i have to check that I've actually looped over each document. This also compounds when I have the potential for having up to 15 MIKG records that could match per document and upwards of 1000 documents that could be in this queue.

[Nathan_Kossover]

Kyle -

DISTINCT does not guarantee that you will only get a single instance of each document returned. Duplicate records are only removed from the results if every single column returned by the SQL query matches for two records. This happens at the database layer; all setting .Distinct = TRUE does in OnBase is insert DISTINCT into the SQL.

Depending on the SQL generated for the query for this particular workflow queue filter it may be that your SQL is not going to be able to eliminate duplicates effectively. You can confirm this by grabbing the query SQL from the diagnostics console and executing it in your query analyzer of choice. What do your results look like in the query analyzer? If you are getting multiple instances of the same document (with or without DISTINCT), those will be returned to the API as well.

Thank you -

Nathan Kossover
Manager, Development

[Kyle_Kranda2]

My question was how do i make this not return all those extra document results.

[Nathan_Kossover]

There is not currently a "force OnBase to return only a single instance of a given document" option availble from the Unity API. If Distinct does not do the job you will need to manually check the results after they are returned.

Nathan Kossover
Manager, Development