Scan Batches -> Total Documents and Page Counts need to be properly updated and stored during document maintenan

[Jacque_Jackson]

- Problem: Total Documents count in Scan Batches -> Committed queue is sometimes incorrect.  Navigate to Scan Queue -> Committed to check the right-hand column for the Total Documents count (for Thick or Unity Client).    Also, we’re unable to locate the Page Counts Total for a batch, we’ve had to check Page Counts one document at a time. 

- Hyland solutions didn’t completely work:  Several attempts of report setup to count documents and pages would return sometimes incorrect values.  We attempted several different report data source types: Document filtering, Custom SQL, and built-in Report Types for both Document Imaging Query – Time in Queue and Document Imaging Query – Time to Process.  These built in methods will count documents and pages of documents with a Deleted status (still recoverable).  These documents aren’t visible in Scan batches, but they are still being counted by these built-in methods.  This has led to much unnecessary confusion when performing Document QA Control.  Also having to cross-check Page Counts for an entire batch can be a bit tedious.  Also when a report data pull passes the timeout interval, it just stops and returns nothing.  Not even an error.  Finally, suggestions to use Total Documents and Total Pages directly from hsi.archivedcommitq proved incorrect as these values are not completely accurate (especially Page Counts which has 4 unique values in the entire table)

- Reasons for failure or need to redesign: Single document deletions cause incorrect Scan Batch - Total Documents count until batch is opened directly in UI.  When changing a document status by deleting it, the Total Documents value in hsi.archivedcommitq also needs to be properly updated.  This will allow the UI list to display all normal documents (which it does) and the Total Documents count in this list (which it doesn’t)

- Places I had to look: Scan Queue -> Committed, Document Properties and Document Navigation, Reporting Dashboards, SQL Server

- How it finally worked: Joined hsi.itemdatapage, itemdata, and archivedcommitq for most accurate numbers in Custom SQL routine for the Reporting Dashboard, but small caveat still exists with single document deletions. I suggest updating Total Documents value after every document maintenance task for both Total Documents and Total Pages, but at minimum for Total Documents.

 - Tickets opened for this request but were closed after a Resolution was delivered (no chance for follow-up):     01613551,  01631994, 01653896, 01679898, 01685259, 01696105, 01649792

Custom SQL used to count deleted but not purged RICOH docs, over a user-selected date range:

--paste Directly in Reporting Dashboard Data Source -> Custom SQL Field, WITH {} included for user date selection parameters

SELECT Doc.batchnum, doc.usernum, format(doc.itemdate,'MM/dd/yy') as [docDate], q.totaldocuments, count(P.batchnum) as TotalPages

FROM hsi.itemdata Doc WITH (nolock) INNER JOIN hsi.itemdatapage P WITH (nolock) on p.itemnum = doc.itemnum

INNER JOIN hsi.archivedcommitq Q WITH (nolock) on Q.batchnum = Doc.batchnum

WHERE Doc.batchnum > 0 AND Doc.itemtypegroupnum > 1 AND q.queuename ='RICOH1'  AND     (itemdate >= @{startdate} AND

                 itemdate <= @{enddate})

group by Doc.batchnum, doc.usernum, format(doc.itemdate,'MM/dd/yy'), q.totaldocuments

--TEST THIS IN SSMS (Param

DECLARE @StartDate as datetime, @EndDate as datetime, @BatchNum as int

SET @StartDate = '1/1/2023'

SET @EndDate = '1/31/2023'

SET @BatchNum = 1541891

SELECT Doc.batchnum, doc.usernum, format(doc.itemdate,'MM/dd/yy') as [docDate], q.totaldocuments, count(P.batchnum) as TotalPages

FROM hsi.itemdata Doc WITH (nolock) INNER JOIN hsi.itemdatapage P WITH (nolock) on p.itemnum = doc.itemnum

INNER JOIN hsi.archivedcommitq Q WITH (nolock) on Q.batchnum = Doc.batchnum

WHERE Doc.batchnum > 0 AND Doc.itemtypegroupnum > 1 AND q.queuename ='RICOH1'  AND     (itemdate >= @startdate AND

                 itemdate <= @enddate)

group by Doc.batchnum, doc.usernum, format(doc.itemdate,'MM/dd/yy'), q.totaldocuments

[Kathleen_Hughes]

Thanks, Jacque, I have just started down this journey and appreciate you posting the SQL.  I also appreciate the number of tickets you have had to open on this with support and applaud you for finding a solution. 

I am going to test this SQL and  see if I can incorporate it into a report I redesigned for scan/indexing productivity.  

Like you, I also have found that the dashboard reports are not accurate for scan/indexing and productivity reports.

 Have you found a solution for when the user locks the batch and that lock is not released 

 The time indexing continues and can be very misleading for a user's productivity. 

[Jacque_Jackson]

I'm unable to unlock documents, but I've put together an API routine that can check for a locked document and push them to a special "Locked" queue so they are not processed and cause errors.

As for the Total Documents and Total Pages values, we were advised by a Hyland Tech that we can use table hsi.archivedcommitq.  However this is not accurate for Total Pages.  There are only 4 possible distinct values for Total Pages in our client's table:

(TOTALPAGES

0

65536

262144)

In fact, I don't see a valid storage value in all of the databases that looks legit for Total Pages.  The only way I know to get a true page count is to count the pages in hsi.itemdatapage.  That's why I wanted to go SQL, but we had issues with the SQL timing out and not returning an error.  It just does nothing with it times out.  This can be very difficult to trace.  Hence all the support tickets for this issue.  I was able to get that SQL working and am pulling the Document details using a DocType query that selects valid DocTypes and links them using Batch Number (Exact Spelling).J

First off can you make this correction in Hylands Tech Notes that hsi.archivedcommitq should NOT be used to count Total Pages.  And we need a routine to update Total Docouments count after Documents are deleted/restored/purged.

Quick note:  Total Documents in hsi.archivedcommitq does seem to be the value tied directly to Scan-> Committed -> Total Documents in both Unity Client and Thick Client UI.  This value is reflected the same and when updated with deletions and opening a batch, that number also gets updated in the UI.  This number remains inaccurate until the batch is opened, the Total Documents number is recounted/updated, and the new value is stored in archivedcommitq.  But until the batch is opened, there is nothing set to refresh Total Documents.  Deleting a bunch of single documents will skew batch counts.  So this IS the value that needs more frequent updating in order to remain valid.  Otherwise, deleting and not purging documents will affect this Total Documents value.

Why is this important?  Because most companies use Total Documents and Page Counts for billing purposes.  It's one of the few metrics we have to measure volume of scanned documents.  These numbers are tied directly to revenue for many companies.  We use these counts to bill our customers.  I can now teach a class at Hyland on Page counts because of this importance.

[Jacque_Jackson]

Latest Reporting Dashboard => Data Provider => Custom SQL that runs best but still has issues with Total Document counts being accurate.  For batches that have docs that are cherry-pick deleted, the deletion but pending purge of these docs doesn't reflect in the Total Documents count until the batch is opened in the Scan/Unity client.  There is indeed a bug with Total Document count, verified several times over.  If we would like to meet up for me to share my screen for a few of Hyland's experts, I would be happy to do so.

Thanks for all your help in hopefully fixing this.

SELECT Doc.batchnum, DT.itemtypename, doc.usernum, format(doc.itemdate,'MM/dd/yy') as [docDate], q.totaldocuments, count(P.batchnum) as TotalPages
FROM hsi.itemdata Doc WITH (nolock) INNER JOIN hsi.itemdatapage P WITH (nolock) on p.itemnum = doc.itemnum
INNER JOIN hsi.archivedcommitq Q WITH (nolock) on Q.batchnum = Doc.batchnum
LEFT JOIN hsi.doctype DT WITH (nolock) on DT.itemtypenum = doc.itemtypenum
WHERE Doc.batchnum > 0 AND Doc.itemtypegroupnum > 1 AND q.queuename ='RICOH1'

AND (itemdate >= @{startdate} AND itemdate <= @{enddate})
GROUP BY Doc.batchnum, DT.itemtypename, doc.usernum, format(doc.itemdate,'MM/dd/yy'), q.totaldocuments