Checking for duplicate invoices-1547050162

To anyone who has the time to consider this;

What would be a valid WHERE clause to use in a Free SQL Statement in an AutoIndex job that uses a file cabinet for for both the iterated File Cabinet and External data source where the match code is the Inoice Number, Vendor Name and Amount but does NOT match on DocID?

I'm filtering the File Cabinet for any invoice status 'New' and assuming I need the SQL Statement for the filter on the External Source. I cannot use the Status field as a filter on the External side (Status NOT New) as some invoices may still have that status until they are processed.

What I'm trying to do is catch any newly stored invoices that might be duplicates of some invoice that has already been stored, but make sure that I don't catch the one that was just stored. AI job runs every 10 seconds.

Thanks in advance for any suggestions,

Steve,
Check out this Blog post.
https://blog.docuware.com/product-news/automatically-identify-duplicate-...

Phil Robson
Senior Director Support Americas

Thanks Phil for the quick response. I have seen that article and Workflow Manager would absolutely be the way to go. Unfortunately we are using version 6.6 in this instance and from what I can see in the designer the ability to query an external data source was not added until a more recent version. If I'm wrong about this please let me know.

Thanks again,

Steve,

The first part is about using AIX to find and flag the duplicates. Workflow Manager is another option.

Phil Robson
Senior Director Support Americas

Phil,

Right and I have a AIX process configured. My worry is, and we have discussed this before I think, that since I don't have an external data source I need to filter the iteration to exclude the document that has just been stored. So I check the "New" value in the status field against any invoice that is already stored and has a status field value of "NOT New". The worry being that I don't want to find this invoice since it meets the criteria, which would, I'm afraid, tag every new invoice as a duplicate. Since I don't know what's happening exactly at the DB level I'm not sure if this is a valid worry or not. But if I could check against all invoices and exclude this docID the problem is solved. Long story short, some duplicate invoices slip through due to timing issues.

Thanks again for taking the time to look at this,

Well  the idea is to tag them as Potential Duplicates. A list is then used to verify if the record is a duplicate or not and processed accordingly, by deleting one of the duplicates and removing the flag from the other.

I don't think  you can do it with a WHERE Clause because you cannot parse the current DWDOCID as variable as you can in a Workflow external query. So I cannot think of a way of precluding the current DWDOCID from the selection. Maybe someone else can. Anyway, at least you can set up a manual process which is a start.

Phil Robson
Senior Director Support Americas

Hey Steve, check this forum out. I just installed nodejs and copied the files from github and this works perfect. Doesn't take long at all to get setup.

https://www.docuware.com/forum/english-forums/docuware-questions-about-u...

Casey,

Thanks for the info but not being a coder I'm not sure what I'm looking at. How does this relate to checking for duplicate records in a v6.6 system exactly?

Steve, i am not a coder either. It all comes precoded just need to adjust the DB field names with what yours are called. This basically validates on the store of the document whether its a duplicate or not. Just to let you know, I have it working with Workflow manager as well, but I ran into a problem. When the AP clerks store the document, they don't know if its a duplicate right away. We have an invoice approval process that starts right on the storage of the document. So, if it is a duplicate, the workflow would still kickoff for the approval process. It just wasn't working for us. We would then need to delete the duplicate, go in and stop the workflows for approval . By doing the validation before storage this would allow the notification of duplicate before it is even stored.

Whether this works on 6.6 not sure. I was using on v7.

Casey,

OK I'll give it a try. So I use node JS to compile this code after modification and follow the instructions for placing the exe in the right place for a validation. Is that right? Where do I get this code?

Steve,

if you have 6.6 system, neither of the methods described in responses below are for you, as the new 'on-demand' (or trigger-like) Autoindexing is only available in DW 7.0 and the JSON type validation - since 6,11 (or 6.12 - not sure).

For me the best way to detect a duplicate in an premise system always been a sql trigger (must be disabled for any Docuware upgrades per Docuware) - with the trigger you can either set an index value to say "Potential Duplicate" or use a RAISERROR command to alert user that a duplicate being stored..

If a SQL trigger is not an option you like and you have AutoIndex module, see a method I described in another chain on how to detect duplicates in a cloud system using AutoIndex. Here is a link https://www.docuware.com/forum/english-forums/docuware-questions-about-usage-and-configuration/detect-duplicates-cloud

Steve, let me know if you need help with either of the methods.