Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 583
  • Last Modified:

Problem with code that compares records

Please help me figure out what is wrong here.  The code below is supposed to increment through about 80,000 records and compare each record to the previous one.  It looks at record 1 and record 2.  If the KeyforDups from record 1 equals the KeyforDups in record 2, a second test must occur comparing the report dates in each record.  If the RptDate for record 1 is not the same as RptDate in record 2, DupFound sets to Yes.  If the report dates are the same, it leaves it alone.  

My problem is that it does not seem to be starting over again correctly.  When I look at results, DupFound is set to True on single records - makes me think it's comparing records to themselves.  It should compare record 1 to record 2, then record 2 to record 3, record 3 to record 4, and so on, since in this database there are some single records and then some where there are from 2 to 20 of the same one.  I only need to flag duplicates that came in on different reports; that is, if two records come in on the same report they are valid and should stay there.

This will run in an agent.


      Dim s As New NotesSession
      Dim db As NotesDatabase
      Set db = s.currentdatabase
      Set view = db.getview("FindDups")
      Set doc1 = view.GetFirstDocument
      Set doc2 = view.GetNextDocument(doc1)
      While Not doc2 Is Nothing
            firstdocval = doc1.KeyforDups(0)
            seconddocval = doc2.KeyforDups(0)
            firstreptdate = doc1.RptDate(0)
            secondreptdate = doc2.RptDate(0)
            If firstdocval = seconddocval Then
                  If firstreptdate <> secondreptdate Then
                        doc2.DupFound = "true"
                        doc2.save True, True
                  End If
            End If
            Set doc1 = view.GetNextDocument(doc1)
            Set doc2 = view.GetNextDocument(doc1)
            
      Wend
0
jkee54
Asked:
jkee54
  • 6
  • 6
  • 3
  • +1
2 Solutions
 
Jean Marie GeeraertsCommented:
You should change the way you get the next document. Since now you get twice the same record with your GetNextDocument(doc1) method.

Here's what would be better :
Dim s As New NotesSession
     Dim db As NotesDatabase
     Set db = s.currentdatabase
     Set view = db.getview("FindDups")
     Set doc1 = view.GetFirstDocument
     Set doc2 = view.GetNextDocument(doc1)
     While Not doc2 Is Nothing
          firstdocval = doc1.KeyforDups(0)
          seconddocval = doc2.KeyforDups(0)
          firstreptdate = doc1.RptDate(0)
          secondreptdate = doc2.RptDate(0)
          If firstdocval = seconddocval Then
               If firstreptdate <> secondreptdate Then
                    doc2.DupFound = "true"
                    doc2.save True, True
               End If
          End If
          Set doc1 = doc2 'set the first document to be the second document
          Set doc2 = view.GetNextDocument(doc2) 'get a new second document to compare to the first document
         
     Wend

Kind regards,

JM
0
 
jkee54Author Commented:
It goes through the first five records, which are unique, and does not mark them.  It hits records six and seven, which are duplicates, and correctly marks record seven.  However, records eight and nine are duplicates and it markes them both true - needs to leave one alone.  Then record ten is unique and it marks that true also.  Further down there are other cases of all duplicates marking as true, etc.  I just can't see what it is comparing to each other to make the evaluation... although I know it has something to do with the way it is incrementing and selecting records, so I was hopeful your solution would work.
0
 
Jean Marie GeeraertsCommented:
Is this after you made changes as I suggested or before ?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
jkee54Author Commented:
Sorry, after.
0
 
Jean Marie GeeraertsCommented:
Strange,
You logic otherwise seems correct so I would have expected it to work as expected too.

To make sure the sort order of the view isn't changed while you loop through the documents in the view, you could add the following statements before and after the loop :

before loop : view.AutoUpdate = false
after loop : view.AutoUpdate = true

So the complete could would be :

     Dim s As New NotesSession
     Dim db As NotesDatabase
     Set db = s.currentdatabase
     Set view = db.getview("FindDups")
     Set doc1 = view.GetFirstDocument
     Set doc2 = view.GetNextDocument(doc1)
     view.AutoUpdate = False
     While Not doc2 Is Nothing
          firstdocval = doc1.KeyforDups(0)
          seconddocval = doc2.KeyforDups(0)
          firstreptdate = doc1.RptDate(0)
          secondreptdate = doc2.RptDate(0)
          If firstdocval = seconddocval Then
               If firstreptdate <> secondreptdate Then
                    doc2.DupFound = "true"
                    doc2.save True, True
               End If
          End If
          Set doc1 = doc2 'set the first document to be the second document
          Set doc2 = view.GetNextDocument(doc2) 'get a new second document to compare to the first document
         
     Wend
     view.AutoUpdate = True

Try this and see if it helps.
0
 
Sjef BosmanGroupware ConsultantCommented:
What if you change your view a bit, with the first column sorted/categorized with value KeyForDups, the second column sorted/categorized with value RptDate, and the third column a totals column with value 1. You could have a ViewNavigator in your agent, and you'd only have to go through categories with a total larger than 1, looking for sub-categories with a total larger than 1. Fast as hell, since you don't have to visit all documents because using ColumnValues will do.
0
 
riprowanCommented:
Can you post the design of the "FindDups" view?
0
 
jkee54Author Commented:
I haven't made Sjef's changes to the view yet, so right now it is has
KeyforDups (Ascending, Standard, with totals
DupFound (so I can see what marked True)
RptDate (Ascending)
0
 
jkee54Author Commented:
Jerrith, I put in those statements before and after the loop and it did not change the behavior at all.  Same results as I posted earlier.

I don't think the sort order is actually changing while the code is looping.  Is there a way to watch exactly what it's doing in a 'stepping' kind of way?
0
 
Sjef BosmanGroupware ConsultantCommented:
Using Jerrith's code, the changes to the view I described earlier and the NotesViewNavigator-class, the result could be:

     Dim s As New NotesSession
     Dim db As NotesDatabase
     Dim vn As NotesViewNavigator
     Dim view As NotesView
     Dim doc As NotesDocument
     Dim ve1 As NotesViewEntry ' categories
     Dim ve2 As NotesViewEntry ' individual documents

     Set db = s.currentdatabase
     Set view = db.getview("FindDups")
     Set vn= view.CreateViewNav
     Set ve1 = vn.GetFirst
     Do Until ve1 Is Nothing
          If ve1.ColumnIndentLevel=1 Then ' second column              
              If ve1.ColumnValues(2)>1 Then ' found dups
                  Set ve2= vn.GetNext(ve1)
                  Set ve2= vn.GetNext(ve2) ' skip the first
                  Do Until ve2 Is Nothing
                      If Not ve2.IsDocument Then Exit Do
                      Set doc= ve2.Document
                      doc.DupFound = "true"
                      doc.save True, True
                      Set ve2= vn.GetNext(ve2)
                  Loop
              End If
          End If
          Set ve1= vn.GetNextCategory(ve1)
     Loop ' I hate While ... Wend

Note: as usual, this code is NOT tested, but plain inspiration...
0
 
jkee54Author Commented:
Still not flagging right.  Flags unique items as well as all items in a group of duplicates, not leaving one unflagged the way it should.
0
 
Jean Marie GeeraertsCommented:
Hm,
Could you send a sample database to my email-adres? (located in my profile on EE)
Just make a copy of your database and include some sample documents, including unique and duplicate documents so I can have a look at it.
I will then send you back a database with working agent.
0
 
Sjef BosmanGroupware ConsultantCommented:
Ho, hey, waitasec! Send me the same please? Seems to be politically correct to upload the database to a public server for downloads by anybody who wants to have a go at it. Thanks!

You tried my view/agent yet?
0
 
Jean Marie GeeraertsCommented:
Sure, sjef didn't want to bypass you.
It's just that I don't see why either of our suggestions doesn't work. I would have expected my code to work, same for your code.
I'm guessing from jkee54's last comment that he did try, but got the same results.
0
 
jkee54Author Commented:
I figured it out last night.  The code was correct, it was stepping OK, but there was a problem with the way I assigned the value to the KeyforDups field and it was reading a null there in most cases.  Tightened it up and it worked fine.  Thanks to jerrif for the basic idea and he and sjef for sticking with me all day.  I will meet my deadline!
0
 
Jean Marie GeeraertsCommented:
Glad to have been of help.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 6
  • 6
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now