Prohibit duplicate key on archiving

Hi

I have a DB which is live and an archive DB. I have created an agent called 'Archive' which runs through selected documents in view, copies them to the archive database and then removes them from the live db.

No probs so far.

But, each doc in the live DB has a unique reference number (say an invoice number). The live DB will not allow any 2 documents to have the same invoice number.
HOWEVER, when documents are archived, they no longer exist in the live DB and it will allow users to 'duplicate' reference numbers despite them existing in the archive DB

Question is this... how do I get the document save validation to check not only the documents in the current database for unique ref nums, but also to include the ref nums in the archive DB?

Thanks!

Crispian
A4eITAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SysExpertCommented:
All you would need to so is open the archive DB, and make sure you have a similar view as in the main DB, and do a search for the ref num.

I hope this helps !
madheeswarCommented:
where is the Aechive Db residing?

Create a view with the first column sorted and have the Invoice Number.-> in Archive db.
Now in the original db,create a Config form, where u can stoer the db path of Archive db and then use this path in LS to check the view created in the Archive db.

This is how u can do. Else when u archive doc's, store the Invoice numbers in a Text field for reference and then u can check the numbers with this Text field, so that u can avoid duplicates.

U can take ur choice from the above. I prefer to have the second option as it will be in the same db and will save performance issues.
If u feel difficulty appending Invoice numebrs to the text field, u can create new documents while archiving and compare the newly created documents with these for duplicate checkings.
This is very simple logic..
Hope this is clear...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Lotus IBM

From novice to tech pro — start learning today.