| Register | FAQ | Calendar | Search | Today's Posts | Mark Forums Read |
|
#1
|
| 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 |
![]() |
| Thread Tools | |
| Display Modes | |