Running Notes

Category LotusScript Symphony
In this month's Lotus Developer Tips Newsletter, I wrote about using the Lotus Symphony Toolkit extract data from Lotus Notes to Symphony (Spreadsheets in Part I) via LotusScript.

I've been working a lot lately with the toolkit and just started wondering who else (if anyone) is using this?

Why or why not?

Thanks for your input!

Posted by Kathy Brown At 10:42 AM Location Portsmouth, NH | Permalink | Comments (5)

Category Lotus Domino Lotusscript Folders
Follow up to my prior post where Denny commented that he wants the @WhichFolders available in Lotusscript. Well, back in March 2008, Bob Balaban blogged about a routine to find what folder a document is in. He blogged a code snippet that was written in C#. An alternative or two, and the timing of the various methods were blogged by Steve McDonagh, so check that out.

Here is my little snippet of the same basic idea as Bob's, but in Lotusscript. The v.IsFolder bit is a little slow. And obviously, the more folders you have the longer this will take. @WhichFolders is much faster, but doesn't work outside of view columns!

Dim ses As New NotesSession
	Dim db As NotesDatabase
	Dim myViews As Variant
	Dim myFolders As Variant
	Set db = ses.CurrentDatabase
	Dim doccol As NotesDocumentCollection
	Dim doc As NotesDocument	
	Set doccol = db.UnprocessedDocuments
	Set doc = doccol.GetFirstDocument()
	myViews = db.Views
	Dim viewnav As NotesViewNavigator
	
	While Not (doc Is Nothing)
		ForAll v In myViews
			If v.IsFolder Then
				v.AutoUpdate = False
				Set viewnav = v.CreateViewNavFrom(doc)
				If viewnav.Count > 0 Then
					MessageBox v.name
				End If
			End If
		End ForAll
		Set doc = doccol.Getnextdocument(doc)
	Wend

---

ls2html was brought to you by nsftools.com

Posted by Kathy Brown At 11:32 AM Location Portsmouth, NH | Permalink | Comments (3)

Category Lotusscript Formula Embedded View
Two semi-related gotchas in Domino development. I *was* planning on calling this post "Gotcha for Newbie Devs" until last night, when I got gotcha'd.

Originally I wanted to write about GetAllEntriesByKey (and similarly, GetAllDocumentsByKey and GetDocumentByKey). A commonly used piece of LotusScript. It often looks something like this...

set myEntries = myView.GetAllEntriesByKey(myKey)

That works just fine, giving you a NotesViewEntryCollection with entries selected by a key. The gotcha here is that this does not include the optional parameter for exactMatch and the default is FALSE. So, if your key is ABC, you will get all entries that equate to ABC and those that equal ABCD and ABCDE, etc. While this MAY be desirable, it may NOT be desirable and you need to know what you want and understand what you are going to get. I see this mistake made frequently. If you need ONLY those entries that match ABC, you need to add the exactMatch parameter and set it to True.

set myEntries = myView.GetAllEntriesByKey(myKey, True)

So, what "gotcha" happened to me last night? The application had an embedded view. 95% of the time the embedded view worked exactly as designed. The code took advantage of "Show Single Category" and used a simple formula consisting of a field name. Whatever the value of the field, that was the value used to show only the applicable documents in the embedded view. Pretty simple, right? Except when it didn't work. For a few documents, the embedded view was showing the wrong docs. Whatever could be the problem? Well, it was similar to the problem above. When ABC category was empty, the embedded view was showing the ABCD category. Now my problem was that there isn't a way to set "True" for an exact match anywhere on the embedded view properties or in the Show Single Category formula.

Enter Twitter and the fabulous Lotus community. I tweeted my dilemma. @DavidMTaylor had the answer. On both the Show Single Category AND embedded view's categorized column formula, I needed to append + "~" after my field name, so ABC became ABC~. Beautifully, fantastically (if not somewhat mysteriously, at least to me), this worked!

Ah, and this morning @jhoetzl sends me this link, which would have also answered my question. Twitter-2, Google-0 in this round.

Posted by Kathy Brown At 9:50 AM Location Portsmouth, NH | Permalink | Comments (3)

Category DXL Lotusscript
Today I tried my hand at a DXL Import. Guess what? It didn’t flipping work. Surprising, eh?

So, what I was trying to accomplish: I needed to generate a folder for a user, if the user did not already have a particular folder in this application. The folder needed to be able to be updated by the designer whenever requested changes were made (if a change was requested, each folder needed to have the new design), hence I could not use a shared-to-private view. DXL seems all the rage, so why not? Export the folder to DXL, edit and replace folder name to a new folder name, import the DXL and voila! Happy new folder. Added bonus, anytime the folder design changes, I could do the very same thing for all the folders and have the design updated. Extra added bonus, I could learn something new! [Note: a button automagically updates the documents in these folders, so no need to be concerned that I would be zapping meticulously moved documents when I replaced the folders.]

It all seemed too easy. For this exercise, I manually exported a folder using Tools, DXL Utilities, Exporter. Whew. Okay, halfway there, right? Ha. Well, first I tried to get fancy. I wanted to store the exported DXL in a profile doc in the database, and use the NotesDXLImporter (the help says you can use a rich text item) to import the DXL into a domino folder. A little help file here, a little help file there and I had this:

Dim session As New NotesSession
	Dim db As NotesDatabase
	Dim importer As NotesDXLImporter
	Dim doc As notesdocument
	Set db = session.CurrentDatabase
	Dim rtitem As notesrichtextitem
	Set doc = db.GetProfileDocument("adminprofile")
	Set rtitem = doc.getfirstitem("prodoc_body")
	
	Set importer = session.CreateDXLImporter
	importer.ReplaceDBProperties = False
	importer.ReplicaRequiredForReplaceOrUpdate = False
	importer.ACLImportOption = DXLIMPORTOPTION_IGNORE
	importer.DesignImportOption = DXLIMPORTOPTION_CREATE
	Call importer.Import(rtitem, db)

ls2html was brought to you by nsftools.com

And … no go. It didn’t blow up, but I got an error, ever helpful as always.

Yes, thanks. Wonderful. Well, fine. Maybe for my first try, I should simplify a bit. Let’s get the DXL from a file.

Dim session As New NotesSession
	Dim db As NotesDatabase
	Dim importer As NotesDXLImporter
	Dim doc As notesdocument
	Set db = session.CurrentDatabase
	Dim stream As NotesStream
	Set stream = session.CreateStream
	If Not stream.Open("C:\Documents and Settings\kathy\Desktop\folder.dxl") Then
		Messagebox "Cannot open " ,, "Error"
		Exit Sub
	End If
	If stream.Bytes = 0 Then
		Messagebox "File did not exist or was empty"
		Exit Sub
	End If
	Set importer = session.CreateDXLImporter
	importer.ReplaceDBProperties = False
	importer.ReplicaRequiredForReplaceOrUpdate = False
	importer.ACLImportOption = DXLIMPORTOPTION_IGNORE
	importer.DesignImportOption = DXLIMPORTOPTION_CREATE
	Call importer.Import(stream, db)

ls2html was brought to you by nsftools.com

Hey, look at that! I just successfully created a folder from DXL.

Being stubborn, I’ve tried to get the rich text item on the profile doc to work, and it…won’t. Stepping through the debugger, all looks right with the world. The DXL is correctly stored in the profile doc and captured in my code as a rich text item. It just doesn’t work.

So, being really stubborn, I still wanted this to work from a document within the database, rather than a file. Further investigation showed I could get plain text from a profile doc and send that in to a NotesStream. Success! I could now import my folder from a profile document.

Dim session As New NotesSession
	Dim db As NotesDatabase
	Dim importer As NotesDXLImporter
	Dim stream As notesstream
	Dim doc As notesdocument
	Set db = session.CurrentDatabase
	Set doc = db.GetProfileDocument("adminproftext")
	Set stream = session.CreateStream
	
	Call stream.WriteText(doc.GetItemValue("prodoc_body")(0))
	
	Call stream.WriteText(doc.GetItemValue("Body")(0))
	
	stream.Position = 0
	
	Set importer = session.CreateDXLImporter
	importer.ReplaceDBProperties = False
	importer.ReplicaRequiredForReplaceOrUpdate = False
	importer.ACLImportOption = DXLIMPORTOPTION_IGNORE
	importer.DesignImportOption = DXLIMPORTOPTION_CREATE
	Call importer.Import(stream, db)

ls2html was brought to you by nsftools.com

So this isn't the most exciting or advanced post in the world. We'll say it's DXL for Dummies. Meaning me.

Oh, and I'm very excited to try ls2html tool from nsftools.com in this post. Pretty cool, huh?

Posted by Kathy Brown At 2:23 PM Location Portsmouth, NH | Permalink | Comments (4)

Category Lotusscript
While trying to diagnose a problem within an agent, I realized (with a little help-Thanks Bob!) that I needed to set the AutoUpdate flag on the view to False. The application is large and used by many different users concurrently. The view is changing almost constantly, even while the agent is running. Therefore running an agent on the view, particularly an agent that walks the view (GetFirstDocument, GetNextDocument), really needed to have the AutoUpdate turned off.

Okay, so somewhat obvious, eh? Well, maybe. I have a very poor memory, and constantly refer to the help files whenever coding in order to get the correct syntax on the first try. NOWHERE in the help files (at least in 8 or probably in 7, since that is what this app was created in) is the AutoUpdate flag referenced with regard to walking a view. Help for GetNextDocument? Nope, not mentioned there. Not even a mention in the help document titled, "Locating documents within a view or folder in LotusScript classes".

At least in the small amount of other dev's code that I have reviewed, I do not see AutoUpdate being set to false very frequently. Further investigation provides this best practice setting from Andre Guirard. Which, if you don't feel like going to read it, basically states that it's a good idea to set AutoUpdate to false for a couple of reasons. There are also a couple of reader comments on the lack of documentation.

So, I post this in the hopes that some other newbie out there reads this and starts using notesview.AutoUpdate = False in their agents. It'll help performance, and hopefully avoid errors as well.

Posted by Kathy Brown At 11:49 AM Location Portsmouth, NH | Permalink | Comments (7)

Category Excel Lotusscript VB
[Originally posted on my prior blog]

I was asked to take some data from a Notes application and make it into a pretty report in Excel. Okay, no problem. A little lotusscript here, a little lotusscript there, a dash of VB. And VOILA, lovely report in Excel with shaded cells, borders, date formatting. Except for one little problem. I couldn’t get the Page Setup.FitToPagesWide to work.

I checked and re-checked my code. Went home and came back this morning and checked it again. It was correct and yet it didn’t work. Huh? A quick Google search gave me this…

“In Microsoft Excel, you cannot use the FitToPagesWide or FitToPagesTall property in Microsoft Visual Basic for Applications code to change the Adjust To option in page setup.

To change the scaling from Adjust To to Fit To in page setup, set the Zoom property to false as in the following example:

With ActiveSheet.PageSetup
.Zoom = False
.FitToPagesTall = 1
.FitToPagesWide = 1
End With”

SERIOUSLY?! THAT makes sense. Of course! Why didn’t I think of that? [end sarcasm] I have to explicitly turn off the zoom in order to set the Adjust To option, even though every other parameter that Excel spits out for PageSetup can be ignored.

Sigh. Deep breath.

Anyway, I know other blogs have covered this, but I thought I would share a bit of the code for creating a “pretty” Excel sheet from Lotus.

Use Lotusscript in the normal manner to get some data. When you’re ready…

‘We need to launch Excel from Lotus
Dim excelApp As Variant
Set excelApp = CreateObject ("Excel.Application")
‘We need to create a new Workbook
Dim excelWB As Variant
Set excelWB = excelApp.Workbooks.Add()
‘And of course, get a handle on a Worksheet
Dim excelS As Variant
Set excelS = excelWB.ActiveSheet

Now given the handle to the Excel Worksheet we can use a little VB to simply populate fields…

‘Either with direct text
excelS.Range("A1").Value = "Hello World"
‘Or with Lotusscript
excelS.Range("A1").Value = udoc.FieldGetText("greetingField")

We can have some fun with the VB, by inserting Lotusscript. I used the following to increment the row in the sheet each time I looped through a NotesViewEntryCollection.

excelS.Range("A"+Cstr(y)).Value = viewentry.ColumnValues(2)

Now let’s get our Format groove on. One thing I found is that code like this won’t work…

.Orientation = xlLandscape

You need the code for “xlLandscape”. Lucky for me, I found Joe Litton’s old post on a quick and easy way to get the code. I was already recording macros in Excel and poaching the VB to use in my Lotusscript, but he showed this gem…

MsgBox "The value of xlPaperLetter is <" & xlPaperLetter & ">"

Ahhh. A thing of beauty. By putting that in an Excel macro, I could get the code for anything I needed for my formatting. So this:

With excelS.Rows(“2:2”)
.Font.Bold = True
.WrapText = True
.HorizontalAlignment = xlRight
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeLeft).ColorIndex = 17
End With

Is now this…

With excelS.Rows("2:2")
.Font.Bold = True
.WrapText = True
.HorizontalAlignment = -4152
.Borders(7).LineStyle = 1
.Borders(7).Weight = 2
.Borders(7).ColorIndex = 17
End with

And it works! So pretty.

One last tip, it’s a good idea to make Excel visible at the beginning of your code while developing, so you can see what’s happening and what’s not happening and when. But when you are done, you should probably move “excelApp.Visible = True” down to the end of your code so users aren’t watching the screen make every little format change. While magical to the user, it is slower.


[Comments posted on my prior blog]

Anonymous said...
Check out some samples of XML/XSL transforms to do your exports. I used VB way back in the day, but XSL is much more flexible.
April 2, 2009 11:27 AM


Kathy said...
That's on my list of things to do! :) This was just supposed to be a quick and dirty for me, and didn't require me to learn anything new! But you're right, I gotta get on the XML train.
April 2, 2009 11:40 AM


Kevin Pettitt said...
Nice post Kathy. Your frustration with the excel constants made me remember there is a "Microsoft Office Constants" database on OpenNTF that would probably have alleviated most of that frustration.
http://www.openntf.org/Projects/pmt.nsf/0/1F6C5C7B16317E218625726D004A31A9
What it doesn't have, unfortunately, is a ready-to-use script library with all of them listed. It's just a reference db essentially. However, I suspect a little more looking (http://www.google.com/search?q=microsoft+office+constants+library&ie=utf-8&oe=utf-8&aq=t&rls=org.mozilla:en-US:official&client=firefox-a) would uncover something like that.
You could also create a custom view in that reference db with one column with a formula like:
"Const " + ConConstant + " = " + ConValue
You'll need to fiddle with the application and maybe the version in the view selection (i.e. Excel 2000 as opposed to all versions). To avoid duplicate constant names you could categorize the column with the above formula.
Then just select all the *categories* in the view (not the documents themselves), do an "Edit - Copy Selected as Table", and paste into excel, remove the blank first column, and paste what's left into Notepad. Then Copy that into a script library.
The reason I know you *could* do this is that I already did part of it and will post it on OpenNTF shortly . Link to follow in later comment...
April 2, 2009 12:43 PM


Kathy said...
Ah yes. Joe Litton's post had a link that didn't work, and I was too lazy to go look. :) I look forward to seeing what you post!
April 2, 2009 12:48 PM


J E Carter II said...
Hat's off to you for your resourcefulness. I've run into things with MS and LN product objects that just seem to have arbitrary restrictions like that - really annoying, as you clearly know. It's a side effect of closed source... oftentimes we know exactly what we need to do, it's just that the person responsible for designing the public interfaces to the closed objects never thought of that or, as in this case, had a conflict with other available controls that had to be resolved in some (usually unintuitive) way.
Count it as another notch in your lipstick case or whatever contemporary equivalent to a Pat Benatar reference there could be - you're earning your chops!
April 2, 2009 12:52 PM


Kathy said...
Oh there is NO equivalent to Pat Benatar, contemporary or otherwise! :)
April 2, 2009 1:01 PM


Kevin Pettitt said...
Microsoft Office Constants Database (Enhanced Version) now in the OpenNTF code bin: http://www.openntf.org/projects/codebin/codebin.nsf/CodeByDate/D9D3B6E9590AE80D8625758C006215BF?OpenDocument
April 2, 2009 2:46 PM


Devin Olson said...
Hey there Kathy.
I realize this is too little too late for you (considering you've already got your export working); but I posted an "Export any Notes View to MS Excel" template on OpenNTF a while back. http://www.openntf.org/Projects/pmt.nsf/ProjectLookup/Export%20any%20View%20to%20MS%20Excel
It pulls everything from the view into Excel for you -except alternating row colors. I wasn't able to grab that via LotusScript. However, there is an Export Options dialog wherein the user can select any alternating row color they choose.
Hope this helps!
April 2, 2009 7:07 PM


Kathy said...
Thanks Devin! I actually saw the template on OpenNTF this morning. It wouldn't have worked for me anyway, since Notes is where the user inputs the data into a form and we use the Excel output for "pretty" client-ready reporting. It has Title Bars and the client name and all kinds of "pretty" stuff I haven't done in Notes. The output isn't even in a view in Notes!
Also, even if it was, I was doing some extra with lookups and filtering on the data. :) We don't have 8.5, so I couldn't wait for the DBLookups in Xpages! :)
April 2, 2009 7:50 PM

Posted by Kathy Brown At 7:34 PM Location Portsmouth, NH | Permalink | Comments (0)