sacha chua living an awesome life

It took me a few hours to figure out that I needed the SUMMARY field flag, so I thought I’d save you the struggle if you ever need to create a view with custom columns.

I was trying to create a view that showed the custom fields I’d added to an RSVP message: RSVPCampaign, RSVPCode, and other details. I had created a Lotus Notes button that sent me an e-mail with the information in hidden fields. I successfully processed the extra fields using a LotusScript agent. I wanted to create a view that showed the results so that I didn’t have to keep clicking on the button and checking my mail. Using a view would make it easier for me to share these tools with other people, because I figured out how to create a button that creates a view.

I couldn’t figure out how to get my custom fields to display, though. They showed up in @DocFields, but @IsAvailable(RSVPCampaign) was always false.

I read about all of the functions in the Formula language. I experimented with @GetField, field names, and other ways to access data. I dug through documentation and websites (most of which assumed people already knew things like this). Frustrated, I opened the properties dialog and started systematically going through the fields on my RSVP messages.

It took me several passes to notice that the regular fields had “Field Flags: SUMMARY” and my custom fields didn’t.

After some quick searching, I realized that I needed to set the summary field flag. So I rewrote my RSVP mailing function to include lines like this:

Dim item As NotesItem
Set item = New NotesItem(doc, "RSVPCode", code)
item.IsSummary = True

I tested my new RSVP mailing button, and my view worked!

So if you’re stumped because your custom fields don’t work in view selection formulas or in column formulas, set the summary flag on the field when you set the field, and you should be good to go.

I wonder how I could’ve learned that faster. Reading other people’s source code would help. Forums are useful, too. Part of it involves picking up the jargon so that I know how to phrase my searches. Anyway, I fixed it! =D

UPDATE: May 28, 2010 – fixed errors caused by default Option Declare. Thanks to Lisa Harnett for the feedback!
UPDATE: Oct 19, 2010 – fixed Quit(). Thanks to Vance for the feedback! Also, clarified tokens.

Based on the feedback on my Lotus Notes mail merge from a Microsoft Excel spreadsheet (2009), I’ve refined my merge script to make it more awesome. How is it more awesome?

• Prompts you for drafting or sending
• Saves sent messages
• Allows you to customize the subject
• Uses [ ] instead of < and > for built-in tokens ([to], [cc], [subject]) for less HTML confusion in blog posts and replies
• Displays number of sent messages and errors
• Closes the Microsoft Excel spreadsheet afterwards

The search-and-replace tokens are defined in the first row of your Microsoft Excel spreadsheet. The script searches for them in the message body, replaces them with the appropriate values from the current row, and either saves the message as the draft or sends the message. There are a few built-in tokens for this script ([to], [cc], [subject]) – these are case-sensitive, so enter them exactly like that. All the other tokens are up to you, so you could use FOO and BAR as search-and-replace tokens if you want.

Tokens are replaced only in the message body. If you want a variable subject line, use a formula to calculate the subject in a column with the [subject] header.

As always, test your mail merges with a small list before using it for your entire list. Create an agent and call it something like “Mail merge”. Edit the agent and set the type to LotusScript. In the (Declarations) section, add

%Include "lsconst.lss"

In the “Initialize” section, put in:

Sub Initialize
	'Mail merge script by Sacha Chua (sacha@sachachua.com)

	Dim ws As NotesUIWorkspace
	Set ws = New NotesUIWorkspace
	Dim sendTypes(1) As String
	Dim sendValue As String
	Dim errorCount As Integer
	errorCount = 0
	sendTypes(0) = "Draft messages without sending"
	sendTypes(1) = "Send messages"
	sendValue = ws.Prompt(PROMPT_OKCANCELLIST, "Sending options", "What would you like to do?", "", sendTypes)
	If (sendValue = "") Then
		Exit Sub
	End If

	Dim fileName As String
	Dim strXLFilename As String
	'Prompt for the filename - should be a Microsoft Excel file with columns, where the first row of each column
	'is a token that will be used when replacing text in the body of the message
	'Special tokens: [to], [cc], [subject] set the appropriate fields
	'Make sure the first column does not have any blank cells
	fileName$ = ws.Prompt(12, "Select file", "3")
	If fileName$ = "" Then
		Exit Sub   'Cancel was pressed
	End If
	strXLFilename = fileName$
	Dim s As New NotesSession
	Dim uidoc As NotesUIDocument
	Dim partno As String
	Dim db As NotesDatabase
	Dim view As NotesView
	Dim doc As NotesDocument
	Dim collection As NotesDocumentCollection
	Dim memo As NotesDocument
	Dim body As NotesRichTextItem
	Dim newBody As NotesRichTextItem
	Dim range As NotesRichTextRange
	Dim count As Integer

	Set db = s.CurrentDatabase
	Set collection = db.UnprocessedDocuments
	Set memo = collection.getFirstDocument()

	Dim varXLFile As variant
	'Get data from the spreadsheet
	Set varXLFile = CreateObject("Excel.Application")
	varXLFile.Visible = False
	Dim varXLWorkbook As variant
	Set varXLWorkbook = Nothing
	varXLFile.Workbooks.Open strXLFilename
	Set varXLWorkbook = varXLFile.ActiveWorkbook
	Dim varXLSheet As variant
	Set varXLSheet = varXLWorkbook.ActiveSheet

	Dim lngRow As Integer
	Dim columnNo As Integer
	Dim token As String
	Dim value As string
	lngRow = 2
	Dim maildoc As NotesDocument
	While (Not (varXLSheet.Cells(lngRow, 1).Value = ""))
		'Fill in the template
		Dim subject As string
		subject = memo.Subject(0)
		Set body = memo.GetFirstItem("Body")

		'Compose message
		
		Set maildoc = New NotesDocument(db)
		Set maildoc= db.CreateDocument()
		maildoc.Form = "Memo"
		maildoc.Subject = subject
		Set newBody = maildoc.CreateRichTextItem("Body")
		Call newBody.appendRTItem(body)
		Set range = newBody.CreateRange			

		'Count the number of fields
		'Look up tokens from the column headings and replace them
		columnNo = 1
		While Not(varXLSheet.Cells(1, columnNo).Value = "")
			token = varXLSheet.Cells(1, columnNo).Value
			value = varXLSheet.Cells(lngRow, columnNo).Value
			count = range.FindAndReplace(token, value, 16)
			If (token = "[to]") Then
				maildoc.SendTo = value
			End If
			If (token = "[cc]") Then
				maildoc.CopyTo = value
			End If
			If (token = "[subject]") Then
				maildoc.Subject = value
			End If
			columnNo = columnNo + 1
		Wend
		On Error GoTo save
		If (sendValue = sendTypes(0)) Then
			Call maildoc.Save(True, False)
		Else
			maildoc.SaveMessageOnSend = True
			maildoc.PostedDate = Now()
			Call maildoc.Send(False)
			Call maildoc.Save(True, True)
		End If
		GoTo nextrow
save:
		MessageBox("Error processing " + maildoc.sendTo)
		errorCount = errorCount + 1
		Resume Next
nextrow:
		lngRow = lngRow + 1
	Wend
	If (sendValue = sendTypes(0)) Then
		MsgBox "Drafted " & (lngRow - errorCount - 2) & " message(s). Errors: " & errorCount
	Else
		MsgBox "Sent " & (lngRow - errorCount - 2) & " message(s). Errors: " & errorCount
	End If
	Call varXLFile.Quit()
End Sub

I’ve promised to give a short talk on microblogging for the knowledge and collaboration community (KCBlue) at work. Might be a good time to practice animation, too. =)

5 minutes: 750 words, 20 minutes: 3,000 words (throw pauses in there too)

Creativity loves constraints. I want to fit the core of my message into 5 minutes (approximately 750 words), with each “part” being 140 characters or less.

This will be a launching pad for discussion, which will take up most of the allotted time. I’ll switch to Q&A with a summary slide that includes Why and Beyond the Basics so that it’s easy for people to remember what they want to ask questions about. I’ll use five minutes at the end to wrap up, and I’ll post links and follow-up material in a blog post. I’ll collect e-mail addresses so that I can notify people when I’ve posted an update.

I plan to make hand-drawn slides for each of the sections, and maybe even animation if I get around to it. =)

—-

The Whys and Hows of Microblogging

Why use Twitter? Why update your status on Facebook or Lotus Connections? Let’s talk about why people microblog and how you can get more value out of these tools.

Don’t know whom to e-mail? Don’t have the time to write a blog post? Post a short, quick update that people can read if they’re there.

What can you fit in 140 or so characters? A single thought. A question. Maybe a link.

What can you get? Broad, rapid, almost real-time conversations, if you’ve got a good network.

Here’s what you can do to build that network, and why you’d want to.

• Learning: Follow role models and learn from what they’re doing. Build the relationship by thanking them for tips and ideas.
• Updates: Do your favourite stores post updates? Find out what’s on sale and when the cookies have come out of the oven.
• Customer service: Good experience? Bad experience? Post an update and you might be surprised by who’s listening.
• Events: Interested in an event? Find out who’s going and what people think. Going there in person? Meet up at tweetups and get to know more people.
• Awareness: Miss those watercooler chats? Microblogging’s better. You can keep in touch with way more people, and you don’t even have to stand up.
• Passing things along: Like what someone shared? Share the good stuff by re-posting with credit. Look at how people do it, and follow their example.
• Sharing: Want to build your network? Make people happy and help them grow by sharing tips and answering questions.
• Questions: Need a quick answer but don’t know whom to ask? Post your question and you just might get a tip. You’ll need a good network for this.

NOTE: No one expects you to read everything. Don’t get addicted. It’s okay if you miss people’s updates.

How to get started:

Twitter: Sign up on twitter.com. Look for people. Follow them. Reply when you have something to say. Share what you’re doing and learning.

Lotus Connections Profiles: Log in. Look for people. Invite them to your network. Reply when you have something to say. Share what you’re doing and learning.

There are more microblogging services out there. Explore. Find out what works for you.

Beyond the basics:

• Apps: Use a microblogging client like Tweetdeck to make reading and posting easier. Explore and find out which tool fits you.
• Cross-posting: Synchronize automatically, or use a tool to post on multiple services. MicroBlogCentral can handle Twitter and Lotus Connections Profiles.
• Personas: Don’t want to mix work and life? Don’t want to overwhelm people with too many updates? Use multiple accounts to give people choices.
• Group posting: Corporate brand? Team account? You can use tools to make it easy for many people to post to the same account.
• Strategy: Where does microblogging fit into your strategy? Post quick updates and interact with people. Link to your main site in your profile.

Next steps:

Pick a reason why you want to microblog, and go for it. How can I help you make the most of these tools?

Keeping track of discussions in Lotus Connections Communities can be difficult, so I thought I’d use a feed reader to read new forum topics and replies. Instead of subscribing to each community by hand, I wrote a Ruby script that generated an OPML file, which I then imported into FeedDemon. Win!

Here’s the script:

#!/usr/bin/ruby

email = ARGV[0]
password = ARGV[1]

require 'rubygems'
require 'rexml/document'
require 'open-uri'
require 'cgi'
require 'net/https'
base_url = 'https://w3.ibm.com/connections/communities/service/atom/'
url = base_url + 'communities/my'
opml = REXML::Document.new('<opml version="1.0"><head></head><body></body></opml>')
body = opml.elements['opml/body']
while url
  # Fetch the page
  $stderr.puts "Fetching " + url
  begin
    my_communities = REXML::Document.new open(url)
  rescue OpenURI::HTTPError
    begin
      my_communities = REXML::Document.new open(url, 
                                                {:http_basic_authentication => [email, password]})

    rescue OpenURI::HTTPError
      url = nil
    end
  end  
  my_communities.elements.each('*/entry') { |x|
    # Add it to the OPML
    $stderr.puts "Found " + x.elements['title'].text
    if x.elements['id'].text =~ /communityUuid=([^&]+)/
      uuid = Regexp.last_match(1)
    end
    body.add_element 'outline', {'title' => x.elements['title'].text,
      'xmlUrl' => 'https://w3.ibm.com/connections/news/atom/stories/public?source=communities&container=' + uuid
    }
  }
  # Set the URL to the next one
  url = nil
  if my_communities.elements['feed/link[@rel="next"]']
    url = my_communities.elements['feed/link[@rel="next"]'].attributes['href']
  end
  sleep 5
end
puts opml.to_s

If you want just discussion topics and replies, use this instead of the xmlUrl line above:

'xmlUrl' => base_url + 'community/forum?communityUuid=' + uuid

I like the GTD way of managing mail, and I’ve created folders for this. The three folders I use the most are AA Next Action, AA Waiting, and Done. Dragging messages down to the right folder is more mouse work than I like, though. I created three buttons on a custom toolbar. For example, the action that moves the current message to my Done folder runs this:

@Command([Folder]; “Done”; “1″)

I’d love to associate these with keyboard shortcuts, or spend some time hacking my mail template. That would be even awesomer! =)

Taking a quick break from Javascript hacking to post this Lotus Notes tweak.

Following Susan Schreitmueller’s advice in the 28-hour Workday presentation she gave, I started replying in subject lines and using [EOM, NRN] to indicate the end of the message and that no response is necessary.

Not everyone’s familiar with this convention, so I always included a short explanation in the body of the message. After a number of these EOM/NRN messages, I created an AutoHotkey macro to save me a few keystrokes. I set up !eomnrn to expand to “EOM – end of message, NRN – no response needed”, and I used that in the body of the message.

I thought it still took too many keystrokes and mouse clicks to reply to a message, add my note to the subject line, add “[EOM, NRN]” to the end of the line, and type in the explanation in the body of the message. In fifteen minutes, I whipped up this little LotusScript agent that prompts you for a response, puts it in the subject line with an explanation, and sends the message off.

In Lotus Notes, use Create – Agent to create an agent called something like “1. EOM – NRN”. Edit the agent and put this in the Initialize sub.

	Dim workspace As New NotesUIWorkspace
	Dim session As New NotesSession
	Dim db As NotesDatabase
	Dim collection As NotesDocumentCollection
	Dim memo As NotesDocument
	Dim reply As NotesDocument
	Set db = session.CurrentDatabase
	Set collection = db.UnprocessedDocuments
	Set memo = collection.getFirstDocument()
	While Not(memo Is Nothing)
		Set reply = memo.CreateReplyMessage( False )
		response = Inputbox("Response to " + memo.Subject(0))
		If (response <> "") Then
			reply.Subject = response + " re: " + memo.Subject(0) + " [EOM, NRN]"
			reply.Body = "EOM - end of message, NRN - no response necessary"
			reply.IsSavedMessageOnSend = True
			reply.Send(False)
		End If
		Set memo = collection.GetNextDocument(memo)		
	Wend

Then you can select the message(s) you want to whiz through, type Alt-A 1 to call the action, and reply quickly. You can also call it while viewing a message, which is probably a safer place to start.

Enjoy!