Urgent..need help

HCL Domino Domino Forum
1

Hello,

Sorry for posting it as urgent as this is very urgent for me. I am working on this from two days and i am nowhere and the deadline is coming up. Please help.

I am creating monthly reports in excel.

Based on the search criteria I am getting the docuemnt collection and i am gettign the unique user&company name and storing this info in to templist but after that i don’t know how to iterate through each document based on this unique value to find each document field(3) values for each unique user and do the total and export it to excel?

Please provide some code to solve this.

here is the scenerio…

I have two forms A, B

user 1 created 10 documents with company X using form A (If same user+company name+form then I want just one company name but I need 10 document totals)

user 1 created 3 documents with company Y using Form B (three seperate entites in excel)

User 2 created 4 documents with company abc using Form A

user2 created 2 documents with company ghi using form B

I want to show the result in excel in this format

User Company check check1 check 2 Total

User1 X 5 10 6 21

user1 Y 1 1 1 3

user1 y 1 0 0 1

user1 y 0 1 1 2

user1 ToTal 7 12 8 27

user2 abc 4 2 4 10

user2 ghi 1 0 1 2

user2 ghi 1 1 1 3

user2 Total 6 3 6 15

Thanks

Sri

2

Subject: Urgent…need help

Try using as below zeudo code

Declare as below …

Type TotRecord

Opentot As integer

Closedtot as integer

Grandtot as integer

End Type

Dim UserEmployeeFormList List as TotRecord

Then try using a code similar to below

if doc.form(0) = “X” then

key = doc.Company(0) + doc.User(0) + doc.Form(0)

If not IsElement(UserEmployeeFormList(key)) Then

 UserEmployeeFormList.(key).Opentot = UserEmployeeFormList.(key).Opentot  + 1

UserEmployeeFormList.(key).Closedtot = UserEmployeeFormList.(key).Closedtot + 1

…

end if

end if

3

Subject: Urgent…need help

Hello,

I don’t really understand what you are trying to do, but have you thought about using ViewNavigators and ViewEntries? If you can get a view to display what you are trying to export, you can then just cycle through the view entries.

HTH

Katherine

4

Subject: RE: Urgent…need help

Hi Katherine,

Thanks for the response.

I have a search form and it has date fields and based on this search criteria i have to find the documents and then export it to excel. I can do this is if this has to appear an individual entry in excel.

I have created a view and sorted it but when i create a search string in which i am using relaitional operators forr dates and running this against the view its returning 0 docuemtns. If I use db.search then its finding the documents.

Dim ws As New NotesUIWorkspace

Dim uidoc As NotesUIDocument

Dim doc As NotesDocument

Dim doc1 As NotesDocument

Dim session As New NotesSession

Dim db As NotesDatabase

Dim filename As String

Dim j As Long

Dim xlApp As Variant

Dim xlsheet As Variant

Dim dc As NotesDocumentCollection

Dim CurrDoc As NotesDocument

Dim dt As New NotesDateTime(“”)

Set uidoc = ws.CurrentDocument

Set doc1 = uidoc.document

Set db = session.CurrentDatabase

Rtitle = uidoc.FieldGetText(“RType”)

Rev = uidoc.fieldgettext(“Rev”)

Cname = uidoc.fieldgettext(“Cnam”)

SearchString$ = {Form = “FollowUp”} & {& DCDate >=[} & doc1.FDate(0) & {]} & {& DCDate <=[} & doc1.TDate(0) &{]}

SearchString$ = SearchString$ & {|} & {(} & {BeginDate >= [} & doc1.FDate(0) & {]} & {& BeginDate <=[} & doc1.TDate(0) & {]} & {)}

Set dc = db.Search(SearchString$,dt, 0)

5

Subject: RE: Urgent…need help

Ok, so you have the documents you need, so what is it you are stuck on?

You can cycle through the documents using Collection.GetFirstDocument and Collection.GetNextDocument and transfer the values into an xls.

Is it the OLE side of things you are stuck on? There are quite a few dbs in the Sandtray that can help you on that.

HTH

Katherine

6

Subject: RE: Urgent…need help

Hi Katherine,

I got the document collection and i can loop through all the documents and can export it to excel as individual entries with no problem. But my problem is how can i export the unique user&Company documents as one document.

for ex:

5 documents has same user(user1) and company name(abc).

I want to export this as one document and also i want to capture the status value of the documents.

If 2 documents has status value “Open” and 3 documetns status value “Closed”, then i have to export like this

user company open close total

user1 abc 2 3 5

I just want to know the logic here on how to get this.

Thanks

sri