Duplicate check in Fortis-1532535585

Is there any way to configure a Workflow in Fortis to perform a duplicate check? Right now we have a client running a trigger on the table that performs the duplicate check and this has started causing tons of deadlocks becuase of how many invoices are in the system and how many invoices they process daily. 

Thanks,
Seth

Seth,

If the client has access to the database and does not mind checking for duplicates using direct-to-server queries, can you simply change the duplicate check to be a single batch job that runs at night (or when there is less server traffic)? 

Instead of checking for duplicates upon inserting a new record (document), just do a single query checking for duplicates by using a GROUP BY on the indexes providing the duplication criteria. I assume you are doing some sort of match on one or more index fields to determine a match. You could runs such a query and get all the duplicates in the database instead of just checking as you insert (might even catch some older dupes that got missed by the trigger-based methodology).

A custom SQL query has another advantage over a trigger -- a trigger (if I am thinking of the right thing) is something you need to add to the database that becomes a part of the database. If anything needs to be reset, that trigger would need to be rebuilt. If instead you use a query from an outside tool (e.g. a C# app), you can run that any time and not have to worry about updates to the server or the imaging software.

Of course, this all depends on the nature of the duplication check -- I may be over-simplifying...

Thanks,

Joe Kaufman

Yeah we asked if we could create a script that would check daily instead of every time an invoice hit the server and they did not like that idea. Right now it checks the invoice number and invoice amount against all of the invoices in the system ~500k. We tried to explain to them that we need to add another way to limit the amount of documents compared to the trigger since they have grown from the time they initially had this trigger applied (back when they first started with Fortis). They said they want to be able to verify against ALL invoices no questions. I was just hoping that there would be a way we have not thought of yet in order to get the same results. Almost seems like the documents need to first enter some type of holding folder before being released into the queue. 

Seth,

You could certainly write a program using the Platform SDK to poll a folder of documents, though the documents would need to have an index file with them (e.g. a "dwcontrol" file). The program could access the database and verify non-duplication, then upload the document.

If your client is OK with modifying the underlying database, what would happen if you declared the combination of the fields in question as a unique index at the SQL Server system level? I am not sure what would happen if SQL Server simply refused to allow the insert because of such a constraint, but it might perform fewer locks than a trigger (I am not a SQL Server expert).

If the client does not like the overnight check idea, could you run the check hourly? Even half-hourly? A read-only query of 500,000 records should run really quickly, and so could detect duplication issues often and efficiently (I assume the index fields you check for supes on are indexed).

EDIT: Here is a link to creating a unique, multi-column constraint, which is maybe what you are already doing:

https://stackoverflow.com/questions/1670708/how-to-create-composite-unique-constraint-in-sql-server-2005

Thanks,

Joe Kaufman

Does Fortis have an SDK and use dwcontrol files?

Seth,

Oh, sorry, this is Fortis... Well, Fortis uses COM automation. I used to have a document around here about all that (and have tons of Foxpro code), but I think you can upload files using the COM automation (but I believe I have only downloaded).

Shouldn't this client be upgrading by year end?

I will see if I can find that old COM handbook and then see if I can figure out how to contact you offline...

Thanks,

Joe Kaufman

Seth,

I have the Fortis documentation, and there are some methods that add documents -- not sure how to use them, and our Fortis server is no longer up.

But if I can get the document to you it might help. I see a Seth Jaco on LinkedIn, and it is a distinctive name -- are you in the Tennessee area? I could message you there and we could exchange emails so I can get you the document...

Thanks,

Joe Kaufman

That should be me, and yes that would be fine.

Thanks

I sent you an invite on LinkedIn with my work email address in it. Drop me a line and I will send the documentation back to you!

Thanks,

Joe Kaufman