Tips Tricks Samples

After my decision to ditch the iPod I am still using iTunes as it is far and away the best music management software that I have seen. But I have new requirements for it – I only have 800mb of space for my daily music. The first thing to do is create smart playlist which is no problem as the image shows. However what wasn’t obvious, to me at least, was how to refresh the playlist.

As long as it is flagged as live updating, all you need to do is select all of the songs in the playlist, and delete them. The playlist will immediately re-populate itself. Nice and easy.

A very simple little LotusScript class which will open an existing spreadsheet and allow you to read and set individual cells, save and close the spreadsheet.

Class ExcelReport
  Private xlApp As Variant
  Private xlSheet1 As Variant
  Private xlSheet2 As Variant
  Private xlSheet3 As Variant
  Private strFilePath As String
  Sub new( xlFilename , isVisable )
  
    Set xlApp = CreateObject(“Excel.application”)
    xlApp.Workbooks.Open xlFilename
    xlApp.Visible = isVisable
    strFilePath = xlFilename
  End Sub
  Function saveFile
    xlApp.ActiveWorkbook.SaveAs( strFilePath )
  End Function
  Function insertData( intSheet As Integer , row As Integer , column As Integer , value As String )
    xlApp.Workbooks(1).Worksheets( intSheet ).Cells( row , column ).Value = value
  End Function
  Function getData( intSheet As Integer , row As Integer , column As Integer ) As String
    On Error Goto err_hdl
    getData = xlApp.Workbooks(1).Worksheets( intSheet ).Cells( row , column ).Value
    Exit Function
err_hdl:
    Print Error$ + “in cls: ExcelReport , method: getData , at line ” + Cstr( Erl )
    getData = “”
    Exit Function
  End Function
  Function doQuit
    xlApp.Quit
    Set xlApp = Nothing
  End Function
  Function makeVisable
    xlApp.Visible = True
  End Function
End Class

An example of how this can be used follows:

Sub Initialize
  Dim report as ExcelReport
  Dim iCol As Integer
  Dim iRow as Integer
  Dim strData as String

  Set report = New ExcelReport(“C:\mysheet.xls” , True )

  
  For iRow = 1 To 10
    strData = report.getData(1, iRow , 1)
    If strData = “” Then
      report.insertData(1, iRow, 2, “Null”)
    Else
      report.insertData(1, iRow, 2, “Not Null”)
    End If
  Next iRow
  report.saveFile
  report.doQuit
End Sub

Update Fixed thanks to Matthias.

Alan Lepofsky’s Notes Tips site has a real cracker today. It shows how to print your calendar to a document. I can think of several manager type people for whom (or at least for their secretaries) this will be an absolute lifesaver for.

An oldie but a goodie. I’m always surprised by the number of people who don’t know about this feature.

If you have a formula which you just want to test quickly without creating a new form, you can paste it into a text field on any new document (the Subject field on a new Memo for example) and simply press Shift-F9. This will evaluate the formula and overwrite the formula you just pasted with the results or any error messages. Great when you are trying to debug problems in production systems.

Simple example:

Create a new Memo
Type @UserName in the Subject
Press Shift-F9
@UserName will be replaced with your canonical name

When running session authentication, especially on a secure connection (SSL) it can be hugely annoying for a user if they try and submit a form only to be told that their session has expired. To combat this I wrote a bit of Javascript in conjunction with a couple of HTML pages which checks your session before trying to submit the data. If the session has expired then you’re prompted to log in again before being allowed to continue.

So step 1, before submitting your form use the following script:

//**************************************************************************************
//First check if the session is alive
 var xml = new ActiveXObject(“Microsoft.XMLHTTP”);
 var pos=0;
 currURL = (document.location.href).toLowerCase();
 pos = currURL.indexOf(‘://’);
 pos += 3;
 pos = currURL.indexOf(‘/’, pos);
 vurl = trim(currURL.substring(0, pos));
 xml.open(“GET”, vurl + “/authchecker.nsf/auth?readform”, false);
 xml.send(null);
 var bError = false;
 if (xml.responseText.indexOf(“Anonymous”) > -1) {
  //The session has expired so prompt user to login again before submitting
  var validSession = window.showModalDialog(“/authchecker.nsf/commonframeset?readform&bottomframe=/authchecker.nsf/QuickLogin.html”, “Login”, “dialogHeight: 270px; dialogWidth: 400px; dialogTop: px; dialogLeft: px; edge: Raised; center: Yes; help: No; resizable: No; status: No;”);
  if (validSession != “true”){
   bError = true;
   if (validSession != “”)
    alert(validSession);
  }
 }
//End Session Checker (Except for bError check around following chunk
//************************************************************************
 if (!bError)
  document.forms[0].submit();

What it does is open a URL using the XMLHTTP ActiveX control in Internet Explorer. I always have a form on the server which will return the current username and rights as an XML document (in this case accessed at “/authchecker/auth?readform”):

<?xml version=”1.0″ encoding=”utf-8″ ?>
<userinformation>
  <name>CN=Matthew White/O=FCL</name>
  <accesslist>
    <access>CN=Matthew White/O=FCL</access>
    <access>*</access>
    <access>*/O=FCL</access>
    <access>Administrators</access>
    <access>[FCLAdmin]</access>
  </accesslist>
  <cookie>DomAuthSessId=24A0C25E003E5F604B5A1CAC544D7C98</cookie>
</userinformation>

The XML returned is checked to see if your username is Anonymous, if it is then we know that your session has expired so we have to prompt for your username and password instead of submitting the form. So we open the quick login screen using the ShowModalDialog method in IE. If the user enters a correct username and password then the modal dialog closes down and the submit continues, otherwise the login screen with reappear until they have logged in or they cancel by closing the dialog box. Even if they can’t log in it means that they do not lose any work.

Download the sample NSF

One of the big pains in Domino is the limit of one embedded view on a form. This approach allows you full control over what is displayed on your forms by using a virtual iFrame technique.

The form just has a div and two JavaScript functions. The JavaScript will load another page (such as a view) into a virtual iframe. The external page which is loaded will call back to its opener and insert itself into the div which you specify.

Attached is a working, cross browser sample database. Every time you change the select list, the JavaScript is called to load a new URL (in this case simply changing the restricttocategory parameter) into the div on the page.

The JavaScript itself is fairly complex but only to handle the various foibles of the different browsers. It came from the Apple Developer Connection website and I have simply taken it a bit further to integrate more happily with Domino.

Download Sample Database

Not sure if this is common knowledge or not, but I didn’t know it so I’ll pass it on.

If, like me, you use a Mac laptop (PowerBook 17″ in my case) you probably won’t leave it turned on all the time. Eventually this will result in things beginning to slow down, especially when booting up and shutting down. This is partly due to the fact that all of the automated maintenance tasks run between 3 an 5 o’clock in the morning. So here are two quick tips:

1. Open the Disk Utility in the Utilities directory and select your hard disk from the menu on the left. Then, on the First Aid tab, click the “Repair Disk Permissions” button. Once this has run things will speed up quite a lot.

2. Download MacJanitor, a utility written by Brian Hill which allows you to run all of the automated tasks manually. It takes a few minutes but is well worth doing every few weeks.

There are huge numbers of functions for sorting arrays, this is particularly good for large lists. It is generally easiest to use a bubble sort for lists of less than 10 elements.

Sub QuickSort(vList As Variant, iLow As Integer, iHigh As Integer)
  ‘Local variables
  Dim vPivot As Variant
  Dim tmpSwap As Variant
  Dim tmpLow As Integer
  Dim tmpHigh As Integer

  tmpLow = iLow
  tmpHigh = iHigh

  vPivot = vList((tmpLow + tmpHigh) / 2)

  While tmpLow < tmpHigh
    While vList(tmpLow) < vPivot And tmpLow < iHigh
      tmpLow = tmpLow + 1
    Wend
    While vPivot < vList(tmpHigh) And tmpHigh > iLow
      tmpHigh = tmpHigh – 1
    Wend
    If tmpLow <= tmpHigh Then
      tmpSwap = vList(tmpLow)
      vList(tmpLow) = vList(tmpHigh)
      vList(tmpHigh) = tmpSwap
      tmpLow = tmpLow + 1
      tmpHigh = tmpHigh – 1
    End If
  Wend
  If iLow < tmphigh Then
    QuickSort vList, iLow, tmpHigh
  End If
  If tmpLow < iHigh Then
    QuickSort vList, tmpLow, iHigh
  End If
End Sub

A second CSS tip for you:

We all know the benefits of using CSS to format your web page. What is less well know is that you can load a second style sheet which can be used when printing the page. It means that you don’t need to worry about hiding navigation etc before the user wants to print as you can simply redefine all of your navigation layers to be invisible when printing. So a page with two stylesheet will have the following HTML:

<link rel=”stylesheet” href=”http://mysite.com/mydb.nsf/style.css” />
<link rel=”stylesheet” href=”http://mysite.com/mydb.nsf/printstyle.css” media=”print” />

Using CSS it’s possible to control when page breaks appear.

p {
    page-break-after : always;
}