I don't do SNTT very often. When I do, its because I think there's some trick or other that you may actually find really useful.

This week, a client needed a report out of a Domino application. There were a lot of ways I could have done this, but the one I chose ended up being very fast and made the client particularly happy. I thought I'd describe it here.

The application is one in which files are posted for users of the site to download. Once made active, the system emails the users a special link. The link is keyed in such a way that the file isn't available unless the user goes through the link to get it (that's done with hash indexing, by the way). A log entry is made when the email goes out, and when a user downloads a file. These log entries are keyed on the unique ID of the user, list the file name and the time of the event.

The report they needed, had to list the user, the file, the date the mail was sent, and the date the file was downloaded (if it was). That's data from three different records. Since we're talking about hundreds -- potentially thousands -- of records, it could take a while to look-up, open, and read all those documents.

I didn't do it that way.

First, I created two views. The first view contains all the uploaded file documents. The first column contains the unique userid, a separator, and the file name -- as a single string, then hashed with @Password. In other words: @Password( uniqueId + "," + @attachmentnames ). The other columns are things I need for the report, including the user's email address, company, and the date the file was posted to the system. The second view contains all the log entries. The first column contains a hash made exactly the same way as the file document one, but with a "1_" prepended if it is a download log, and a "2_" if the file is a mailing log. In otherwords, a mailing log entry would be: "2_" + @Password( uniqueId + "," + @attachmentnames ).

Next, I created an agent, which could be called from a browser. The agent performed these steps:

1. Created a custom class called a "record" like this:

	public class record

		name as string

		email as string

		filename as string

		filedldate as string

		msgsent as string

		....(etc)

	end class

2. Created a "LIST" of type "record" to hold the data:

	dim recordList list as record

3. Created a "LIST" of type "string" to hold the logging data

	dim logList list as string

4. Opened the log data view, used the "allEntries" property to get the view entries, and then iterated them. For each entry in the view, I created a matching list entry with the same key. The value of the item was the date that log entry corresponded to. This allowed me to read that view data in one pass into memory without opening a single document. That's extremely fast.

5. Opened the view containing the file upload records, access an entrycollection via the "allEntries" property of the view class, and then iterated through the entries.

6. For each entry, I create a new "record" object and assign it to my RecordList set. I set the properties of that record which are contained in the file records directly from their corresponding view entry columns -- without having to open the actual documents.

7. Next, using the key of that entry, I checked for a "mailing" log entry by adding a "2_" to the key, and checking the logList set to see if one existed. If it did, I could simply assign its value to the record.msgsent property.

8. I did the same as in step 7, only with a "1_" to look for file download records.

9. On finishing that iteration, I have a complete list set of all the data I need without ever actually opening a document.

10. I simply print out the data to the user.

How did I print out the data?

First, as the first lines in the agent, I used this syntax:

	Print |Content-Type:application/vnd.ms-excel|

	Print |Content-Disposition: Attachment; filename="|+filename+|"| 

This overrides Domino sending out html. Once that's done, I created a table:

	print "<table><tr>"

And iterated through my recordList, pushing out values:

	print "<td>" & record.email & "</td><td>" & record.downloadDate & "</td>"

Etc etc. then after the loop, I closed the table:

	print "</tr></table>"

I even used a style tag to make the cells look pretty.

	print "<style type='text/css'>"

	print "	td { border:dotted 1px silver; margin:.5em; font-size:8pt; } "

	print "</style>"

When the user clicks the link, they get a nicely formated document launches to Excel in their workstation. They can sort and work with the data any way they like. They're very happy.

Note: I could have done the output during the original loop, which would have been faster if there are a huge number of records -- but since its all in-memory for the second loop, its not really any slower. Its still faster than the data can transfer to the end client. I choose this way to keep it more clean and manageable.