cannot find duplicate record in view

This is a discussion on cannot find duplicate record in view within the lotus-notes-programmer forums in Other Technologies category; i face a weird problem. In my excel report, i can see two duplicate invoice record, but i cant find any duplicate in the view.And now i don't know how to delete the duplicate record in the excel report. Below are my script: Sub Initialize 'constant declaration and initialization Const CONST_CURTYPE = RM Const CONST_EXCRM = 1.00 Const CONST_INVVW = ($ByProjectTitle) Const CONST_VWBYDEPT = ($ReportByDept) Const CONST_VWBYTOP = ($ReportByTopManagement) 'variable declaration Dim doc As notesDocument Dim vw As notesView Dim vwNav As notesViewNavigator Dim vwEntry As notesViewEntry Dim empName As notesName Dim strPaymentPath As String Dim strDept As String Dim strManagement As ...

Go Back   Database Forum > Other Technologies > lotus-notes-programmer

Database Forums

Register FAQ Calendar Search Today's Posts Mark Forums Read
  #1  
Old 08-22-2005, 08:14 AM
Default cannot find duplicate record in view

i face a weird problem. In my excel report, i can see two duplicate
invoice record, but i cant find any duplicate in the view.And now i
don't know how to delete the duplicate record in the excel report.
Below are my script:

Sub Initialize

'constant declaration and initialization
Const CONST_CURTYPE = "RM"
Const CONST_EXCRM = 1.00
Const CONST_INVVW = "($ByProjectTitle)"
Const CONST_VWBYDEPT = "($ReportByDept)"
Const CONST_VWBYTOP = "($ReportByTopManagement)"

'variable declaration
Dim doc As notesDocument

Dim vw As notesView
Dim vwNav As notesViewNavigator
Dim vwEntry As notesViewEntry

Dim empName As notesName

Dim strPaymentPath As String
Dim strDept As String
Dim strManagement As String
Dim strCustomer As String

Dim invOPE As Double
Dim invFee As Double
Dim invTax As Double

Dim paymentDb As notesDatabase
Dim invoiceVw As notesView
Dim invoiceDoc As notesDocument
Dim invoiceCol As notesDocumentCollection
Dim invoiceNo As Integer
Dim invoiceAmount As Double
Dim invoiceExchangeRate As Double

Dim row As Integer
Dim strRow As String

Dim dateDiff As String
Dim ageCat As String

Dim key As String
Dim keyInv As String
Dim strTeam As String
Dim projMgr As String
Dim ctr As Integer

Dim invoiceAmt As Double
Dim paymentAmt As Double
Dim invoiceOutstanding As Double

Dim itmInv As notesItem

'variable initialization
'get current database and document context
Set session = New notesSession
Set Database = session.currentDatabase
Set curDoc = session.DocumentContext

On Error Goto errHandler

'get the query string as passed in the url
strPaymentPath = Strleft(Strright(curDoc.Query_String_Decoded(0),
"&uid="), "&uid1=")
strDept = Strleft(Strright(curDoc.Query_String_Decoded(0), "&uid1="),
"&uid2=")
strManagement = Strright(curDoc.Query_String_Decoded(0), "&uid3=")

If strDept = "" Then
Print "Report not Found"
Exit Sub
End If

'get the appropriate department based on the user's management level
If strDept = "All" Then 'TOP MANAGEMENT - get all document
If Not GetView(Database , CONST_VWBYTOP, vw) Then Exit Sub
Set vwNav = vw.createViewNav
Else 'DEPARTMENT - get related department's document only
If Not GetView(Database , CONST_VWBYDEPT, vw) Then Exit Sub
Set vwNav = vw.createViewNavFromCategory(strDept)
End If

' loop thru the entry if document is found
Set vwEntry = vwNav.getFirst
If Not vwEntry Is Nothing Then
If Not GetDatabase("", strPaymentPath, paymentDb ) Then Exit Sub
If Not GetView(paymentDb , CONST_INVVW, InvoiceVw ) Then Exit Sub

If Not InstantiateExcelObject Then
If Datatype(xlApp) > 0 Then
Call xlApp.Quit
Set xlApp = Nothing
End If
Print "Error in instantiating Excel Object"
Exit Sub
End If

row = 2
While Not (vwEntry Is Nothing )
Set doc = vwEntry.document

strRow = Cstr(row)

key = doc.ProjectTitle_tx(0)
strCustomer = doc.Customer_tx(0)
If strDept = "CFD" Then
strTeam = doc.CFTeam_kw(0)
Else
strTeam = ""
End If
projMgr = doc.ProjMgr_kw(0)

'get invoice document from payment database
Set invoiceCol = invoiceVw.getAllDocumentsByKey(key, True)
invoiceNo = invoiceCol.count

If invoiceNo > 0 Then
'project information
xlsheet.Range("A" & strRow).value = strCustomer
xlsheet.Range("B" & strRow).value = key
xlsheet.Range("C" & strRow).value = strTeam
xlsheet.Range("K" & strRow).value = projMgr
Set invoiceDoc = invoiceCol.getFirstDocument

ctr = 0
While Not (invoiceDoc Is Nothing)
feeAmt = 0
opeAmt = 0
taxAmt = 0
paidAmt = 0

invFee = 0
invOPE = 0
invTax = 0

strRow = Cstr(row)
If invoiceDoc.currencyType_tx(0) = "" Then
invoiceExchangeRate = CONST_EXCRM
Else
If invoiceDoc.CurrencyType_tx(0) = CONST_CURTYPE Then
invoiceExchangeRate = CONST_EXCRM
Else
If invoiceDoc.ExchangeRate_tx(0) = 0 Then
invoiceExchangeRate = CONST_EXCRM
Else
invoiceExchangeRate = Cdbl(invoiceDoc.ExchangeRate_tx(0))
End If
End If
End If

If ctr > 0 Then
xlsheet.Range("A" & strRow).value = strCustomer
xlsheet.Range("B" & strRow).value = key
xlsheet.Range("C" & strRow).value = strTeam
xlsheet.Range("K" & strRow).value = projMgr
End If
Set itmInv = invoiceDoc.getFirstItem("InvoiceNumber_tx")
'Msgbox Cdbl(invoiceDoc.InvoiceNumber_tx(0))
If itmInv Is Nothing Then
xlsheet.Range("D" & strRow).value = "NA"
Else
If invoiceDoc.InvoiceNumber_tx(0) = "" Then
xlsheet.Range("D" & strRow).value = "NA"
Else
xlsheet.Range("D" & strRow).NumberFormat = "@"
xlsheet.Range("D" & strRow).value =
invoiceDoc.InvoiceNumber_tx(0)
End If
End If
invFee = Cdbl(invoiceDoc.InvoiceFeeAmount_tx(0))
invOPE = Cdbl(invoiceDoc.InvoiceOPEAmount_tx(0))
invTax = Cdbl(invoiceDoc.ServiceTax_tx(0))
invoiceAmount = (Cdbl(invoiceDoc.InvoiceFeeAmount_tx(0)) +
Cdbl(invoiceDoc.InvoiceOPEAmount_tx(0)) +
Cdbl(invoiceDoc.ServiceTax_tx(0))) * Cdbl(invoiceExchangeRate)
xlsheet.Range("E" & strRow).value = Cstr(invoiceAmount)
xlsheet.Range("G" & strRow).value =
Cstr(getOutStandingAmt(paymentDb, Cstr(invoiceDoc.invoiceNumber_tx(0)),
invoiceAmount, invoiceExchangeRate, invFee, invTax, invOPE))
xlsheet.Range("H" & strRow).value = feeAmt
xlsheet.Range("J" & strRow).value = OPEAmt
xlsheet.Range("I" & strRow).value = taxAmt
xlsheet.Range("F" & strRow).value = paidAmt
xlsheet.Range("L" & strRow).value = invoiceDoc.invoiceDate_dt(0)
xlsheet.Range("M" & strRow).value = Cstr(Format(Today(),
"mm/dd/yyyy"))
xlsheet.Range("N" & strRow).Formula = "=M" & strRow & "-L" &
strRow
dateDiff = xlsheet.Range("N" & strRow).value
xlsheet.Range("N" & strRow).value = dateDiff

Select Case Cint(dateDiff)
Case Is < 0:
ageCat = "30"
Case 0 To 30:
ageCat = "30"
Case 31 To 60:
ageCat = "60"
Case 61 To 90:
ageCat = "90"
Case 91 To 120:
ageCat = "120"
Case 121 To 150:
ageCat = "150"
Case 151 To 180:
ageCat = "180"
Case Is > 180:
ageCat = ">180"
End Select
xlsheet.Range("O" & strRow).value = ageCat
Set invoiceDoc = invoiceCol.getNextDocument(invoiceDoc)
ctr = ctr + 1
row = row + 1
Wend
End If
Set vwEntry = vwNav.getNext(vwEntry)
Wend

If xlsheet.Range("A2").value = "" Then
'print error msg if there is no document found
Call xlApp.Quit
Set xlApp =Nothing
Print "Report not Found"
Exit Sub
End If

Call PopulateReport

Else
'print error msg if there is no document found
Print "Report not Found"
Exit Sub
End If
Exit Sub

errHandler:
Print Error
Print "Error in generating report. Please contact Administrator!"
If Datatype(xlApp) > 0 Then
Call xlApp.Quit
Set xlApp = Nothing
End If
Exit Sub
End Sub

Reply With Quote
Reply


Thread Tools
Display Modes



All times are GMT -4. The time now is 09:19 PM.


Powered by vBulletin® Version 3.6.8
Copyright ©2000 - 2009, Jelsoft Enterprises Ltd.
Integrated by bbpixel2009 :: jvbPlugin R1013.368.1

Search Engine Friendly URLs by vBSEO 3.1.0
vB Ad Management by =RedTyger=
In an effort to better serve ads to our visitors, cookies are used on Mydatabasesupport.com. For more information, check out our Privacy Policy.