tag:blogger.com,1999:blog-248633262024-03-07T22:27:10.858-05:00Don McNallyThoughts on technology, SharePoint, Lotus Notes/Domino, music and whatever else pops into my head.Don McNallyhttp://www.blogger.com/profile/17995406659448494074noreply@blogger.comBlogger24125tag:blogger.com,1999:blog-24863326.post-17435090102779957562011-03-31T15:45:00.005-04:002011-03-31T16:00:24.255-04:00Barcodes in Lotus Notes Applications<p><img src="http://webpages.charter.net/dtmc/images/SNTTbtn.png" align="right" />If you have ever wanted to implement barcoding in a Notes application, it may be easier to do so than you think. You really need four things to make it work: </p><br /><ol><br /><li>A barcode font that is deployed to machines that will be printing the things (eg. paper or labels) that have the barcode on them. </li><br /><li>LotusScript code to generate the field value for the barcode. Simple barcodes, like Code 39, just need start and stop characters before and after the number you are putting on a barcode, but they have a limited character set. More complex barcodes, like Code 128, require start and stop characters as well as check digits but are able to include the alphanumeric character set. </li><br /><li>A barcode scanner. </li><br /><li>An input form to handle the scanner’s output and cause processing to happen. </li></ol><br /><p>Though the application I am working on is encoding a 12-digit number, we are deploying a Code 128 font so that we will be ready if we expand the use of barcodes into an application that requires alphabetic characters as well. Scanner set up is pretty easy now, with most being able to plug and play with your computer hardware and able to be configured by scanning barcodes. They function similarly to keyboards, in that if you are some sort of input area (eg. a field or even the body of a mail message), the scanner will feed the data from the barcode into the field as though you typed it on a keyboard. </p><br /><p>The vendor we used for the font also supplies code for many different applications, including LotusScript. However, to get the number I encoded to print as part of the barcode, I had to take their VBA script and modify it for LotusScript because their LotusScript code does not support human-readable codes. It was a pretty simple code change though. I configured the scanner to append a tab to each scan that it completed (explanation of why is below). I just plugged it in and then scanned seven codes on a sheet that came with the scanner and it was ready to go. </p><br /><p><strong>The Application</strong></p><br /><p>The processing form is displayed as a dialog box that allows the user to set some processing options before the input field is displayed. This is done with a programmable table with the options on one tab and the processing actions on a second tab, with buttons to toggle between the two tabs. This helps to ensure the cursor is in the input field when ready to scan by making it the only editable field visible. </p><br /><p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhNSPA0aVji5fC7anW_X0IJIoUMUVpqdyHRO1TS_SK5mae5XmSWnJZSOVvMg-R8ne-oREMda-UEYb7yCD1fmPuku9Pqb1y9_LZrx6m6ek61Kt3hyYx8NGA7JwEdFQL15i1nY8_TxA/s1600/barcode-designer.JPG"><img id="BLOGGER_PHOTO_ID_5590333855596358626" style="DISPLAY: block; MARGIN: 0px auto 10px; WIDTH: 320px; CURSOR: hand; HEIGHT: 268px; TEXT-ALIGN: center" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhNSPA0aVji5fC7anW_X0IJIoUMUVpqdyHRO1TS_SK5mae5XmSWnJZSOVvMg-R8ne-oREMda-UEYb7yCD1fmPuku9Pqb1y9_LZrx6m6ek61Kt3hyYx8NGA7JwEdFQL15i1nY8_TxA/s320/barcode-designer.JPG" border="0" /></a> The form has the main input field (TrackingNum) and a second, unmarked input field (ActionField) on it. By unmarked, I mean that it is a simple text field that has the Show Field Delimiters option unchecked. The scanner sends the decoded data from the barcode plus a tab character. The tab moves the cursor from the TrackingNum field to the ActionField field. The processing code is in the OnBlur event of the TrackingNum field; that code takes the number from the barcode, finds the document and updates it as necessary. The ActionField field has code in the OnFocus event to move the cursor back to the TrackingNum field, which makes the dialog ready for the next scan. </p><br /><p>The form needed to be functional manually as well as with the scanner so the TrackingNum field is defined as a combobox and the form has a button on it that, when clicked, moves the cursor out of the TrackingNum field. The combobox lists items that are available for the user to process and can also be used to type in any number manually. The button just puts the cursor into the ActionField field, which then moves the cursor back to the TrackingNum field. </p><br /><p>This is what the dialog looks like. Processing Messages is a reverse-chronological list of the actions taken each time an entry in the TrackingNum field is processed. <a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhQTovHhnyooGjQOYcS0hqraJROLUofPTs1pfEbais_ORFUmoltVCV5OLXgwJzkKL70T1ipuRMzshM7sxU9hs7vNewYagb4JxAGwdvCH2ed3dFLiCz4K3PEIaxs-q20F65M_tEtWQ/s1600/barcode-client.JPG"><img id="BLOGGER_PHOTO_ID_5590334116114696386" style="DISPLAY: block; MARGIN: 0px auto 10px; WIDTH: 320px; CURSOR: hand; HEIGHT: 226px; TEXT-ALIGN: center" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhQTovHhnyooGjQOYcS0hqraJROLUofPTs1pfEbais_ORFUmoltVCV5OLXgwJzkKL70T1ipuRMzshM7sxU9hs7vNewYagb4JxAGwdvCH2ed3dFLiCz4K3PEIaxs-q20F65M_tEtWQ/s320/barcode-client.JPG" border="0" /></a> I couldn’t find a lot about barcoding in Notes, and found nothing about how to actually code an application to interact with a scanner, but I did receive a hints from a couple of Lotus peeps that really helped. In the end, it was a lot easier than I expected. </p><br /><p>Technorati: <a href="http://technorati.com/tag/show-n-tell" rel="tag">Show-n-Tell Thursday</a> </p>Don McNallyhttp://www.blogger.com/profile/17995406659448494074noreply@blogger.com2tag:blogger.com,1999:blog-24863326.post-81259858135318607712011-01-27T13:37:00.002-05:002011-01-27T13:59:42.656-05:00Get Database Information for Configuration Documents<img src="http://webpages.charter.net/dtmc/images/SNTTbtn.png" align="right" />I've done a lot of work lately to make our applications more configurable. Most of it is related to having separate servers for development, QA and production, and needing to be able to change reference databases without requiring code changes at each stop in the change control process.<br /><br />As part of that, I wanted to make it easier to enter the information we required for the reference databases. Sure, you could find the database and then type in the server name and path name (or replica ID), but we had so many that it became a burden to do it that way.<br /><br />So I created the script below. This could be a function in a script library or could be added in the form globals.<br /><br /><pre><div style="FONT-SIZE: 9pt; COLOR: black; FONT-FAMILY: sans-serif"><br /><span style="COLOR: blue">Sub</span> SetDatabaseInfo<span style="COLOR: blue">(</span>actionID <span style="COLOR: blue">As</span> <span style="COLOR: blue">String</span><span style="COLOR: blue">,</span> docObj <span style="COLOR: blue">As</span> <span style="COLOR: blue">Variant</span><span style="COLOR: blue">,</span> titleField <span style="COLOR: blue">As</span> <span style="COLOR: blue">String</span><span style="COLOR: blue">,</span> serverField <span style="COLOR: blue">As</span> <span style="COLOR: blue">String</span><span style="COLOR: blue">,</span> pathField <span style="COLOR: blue">As</span> <span style="COLOR: blue">String</span><span style="COLOR: blue">,</span> repIDField <span style="COLOR: blue">As</span> <span style="COLOR: blue">String</span><span style="COLOR: blue">)</span><br /><span style="COLOR: green">%REM<br /> This function allows the user to select a database and set up to four fields with information from the selected database:<br />(0) Database Title<br />(1) Server<br />(2) File path<br />(3) Replica ID<br /><br />Parameters are:<br /> actionID = 'Set' to set the field values, 'Clear' to clear the field values<br /> docObj = the document to be modified. Can be a NotesDocument or NotesUIDocument<br /> titleField = item name for the database title<br /> serverField = item name for the database server name<br /> pathField = item name for the database path<br /> repIDField = item name for the database replica ID<br /><br />If Cancel is chosen in the dialog, the document passed in is not changed.<br />The user receives an error if the file they chose cannot be opened.<br />%END REM</span><br /> <br /> <span style="COLOR: blue">Dim</span> ws <span style="COLOR: blue">As</span> <span style="COLOR: blue">New</span> <span style="COLOR: black">NotesUIWorkspace</span><br /> <span style="COLOR: blue">Dim</span> nname <span style="COLOR: blue">As</span> <span style="COLOR: black">NotesName</span><br /> <span style="COLOR: blue">Dim</span> session <span style="COLOR: blue">As</span> <span style="COLOR: blue">New</span> <span style="COLOR: black">NotesSession</span><br /> <span style="COLOR: blue">Dim</span> db <span style="COLOR: blue">As</span> <span style="COLOR: black">NotesDatabase</span><br /> <span style="COLOR: blue">Dim</span> notesDataPath <span style="COLOR: blue">As</span> <span style="COLOR: blue">String</span><br /> <span style="COLOR: blue">Dim</span> filePath <span style="COLOR: blue">As</span> <span style="COLOR: blue">Variant</span><br /> <span style="COLOR: blue">Dim</span> result <span style="COLOR: blue">As</span> <span style="COLOR: blue">Variant</span><br /> <span style="COLOR: blue">Const</span> PROMPT_CHOOSEDATABASE <span style="COLOR: blue">=</span> 13<br /> <span style="COLOR: blue">Dim</span> fileInfo<span style="COLOR: blue">(</span>3<span style="COLOR: blue">)</span> <span style="COLOR: blue">As</span> <span style="COLOR: blue">String</span><br /> <span style="COLOR: blue">Dim</span> docObjType <span style="COLOR: blue">As</span> <span style="COLOR: blue">String</span><br /> <br /> docObjType <span style="COLOR: blue">=</span> <span style="COLOR: blue">Typename</span><span style="COLOR: blue">(</span>docObj<span style="COLOR: blue">)</span><br /> <span style="COLOR: blue">If</span> docObjType <span style="COLOR: blue"><</span><span style="COLOR: blue">></span> <span style="COLOR: black">"NOTESDOCUMENT"</span> <span style="COLOR: blue">And</span> docObjType <span style="COLOR: blue"><</span><span style="COLOR: blue">></span> <span style="COLOR: black">"NOTESUIDOCUMENT"</span> <span style="COLOR: blue">Then</span> <span style="COLOR: blue">Exit</span> <span style="COLOR: blue">Sub</span><br /> <br /> <span style="COLOR: blue">Select</span> <span style="COLOR: blue">Case</span> <span style="COLOR: blue">Ucase</span><span style="COLOR: blue">(</span>actionID<span style="COLOR: blue">)</span><br /> <span style="COLOR: blue">Case</span> <span style="COLOR: black">"SET"</span><br /> notesDataPath <span style="COLOR: blue">=</span> <span style="COLOR: blue">Ucase</span><span style="COLOR: blue">(</span>session<span style="COLOR: blue">.</span>GetEnvironmentString<span style="COLOR: blue">(</span><span style="COLOR: black">"Directory"</span><span style="COLOR: blue">,</span><span style="COLOR: purple">True</span><span style="COLOR: blue">)</span><span style="COLOR: blue">)</span> <span style="COLOR: blue">&</span> <span style="COLOR: black">"\"</span><br /> result <span style="COLOR: blue">=</span> ws<span style="COLOR: blue">.</span>Prompt<span style="COLOR: blue">(</span>PROMPT_CHOOSEDATABASE<span style="COLOR: blue">,</span><span style="COLOR: black">"Choose Database"</span><span style="COLOR: blue">,</span><span style="COLOR: black">"Choose the database for which you want information:"</span><span style="COLOR: blue">,</span><span style="COLOR: black">""</span><span style="COLOR: blue">,</span><span style="COLOR: black">""</span><span style="COLOR: blue">)</span><br /> <span style="COLOR: blue">If</span> <span style="COLOR: blue">Isempty</span><span style="COLOR: blue">(</span>result<span style="COLOR: blue">)</span> <span style="COLOR: blue">Then</span><br /> <span style="COLOR: blue">Exit</span> <span style="COLOR: blue">Sub</span><br /> <span style="COLOR: blue">End</span> <span style="COLOR: blue">If</span><br /> <br /> <span style="COLOR: blue">Set</span> db <span style="COLOR: blue">=</span> <span style="COLOR: blue">New</span> <span style="COLOR: black">NotesDatabase</span><span style="COLOR: blue">(</span><span style="COLOR: black">""</span><span style="COLOR: blue">,</span><span style="COLOR: black">""</span><span style="COLOR: blue">)</span><br /> <span style="COLOR: blue">If</span> <span style="COLOR: blue">Not</span> db<span style="COLOR: blue">.</span><span style="COLOR: blue">Open</span><span style="COLOR: blue">(</span>result<span style="COLOR: blue">(</span>0<span style="COLOR: blue">)</span><span style="COLOR: blue">,</span>result<span style="COLOR: blue">(</span>1<span style="COLOR: blue">)</span><span style="COLOR: blue">)</span> <span style="COLOR: blue">Then</span><br /> <span style="COLOR: blue">Messagebox</span> <span style="COLOR: black">"Error opening database; you may not have access to it"</span><span style="COLOR: blue">,</span>48<span style="COLOR: blue">,</span><span style="COLOR: black">"Error"</span><br /> <span style="COLOR: blue">End</span> <span style="COLOR: blue">If</span><br /> filePath <span style="COLOR: blue">=</span> <span style="COLOR: blue">Ucase</span><span style="COLOR: blue">(</span>db<span style="COLOR: blue">.</span>FilePath<span style="COLOR: blue">)</span><br /> <span style="COLOR: blue">If</span> result<span style="COLOR: blue">(</span>0<span style="COLOR: blue">)</span> <span style="COLOR: blue">=</span> <span style="COLOR: black">""</span> <span style="COLOR: blue">Then</span><br /> filePath <span style="COLOR: blue">=</span> <span style="COLOR: blue">Strright</span><span style="COLOR: blue">(</span>filePath<span style="COLOR: blue">,</span>notesDataPath<span style="COLOR: blue">)</span><br /> <span style="COLOR: blue">End</span> <span style="COLOR: blue">If</span><br /> fileInfo<span style="COLOR: blue">(</span>0<span style="COLOR: blue">)</span> <span style="COLOR: blue">=</span> db<span style="COLOR: blue">.</span>Title<br /> fileInfo<span style="COLOR: blue">(</span>1<span style="COLOR: blue">)</span> <span style="COLOR: blue">=</span> result<span style="COLOR: blue">(</span>0<span style="COLOR: blue">)</span><br /> fileInfo<span style="COLOR: blue">(</span>2<span style="COLOR: blue">)</span> <span style="COLOR: blue">=</span> filePath<br /> fileInfo<span style="COLOR: blue">(</span>3<span style="COLOR: blue">)</span> <span style="COLOR: blue">=</span> db<span style="COLOR: blue">.</span>ReplicaID<br /> <br /> <span style="COLOR: blue">If</span> fileInfo<span style="COLOR: blue">(</span>0<span style="COLOR: blue">)</span> <span style="COLOR: blue"><</span><span style="COLOR: blue">></span> <span style="COLOR: black">""</span> <span style="COLOR: blue">Then</span><br /> <span style="COLOR: blue">If</span> docObjType <span style="COLOR: blue">=</span> <span style="COLOR: black">"NOTESDOCUMENT"</span> <span style="COLOR: blue">Then</span><br /> <span style="COLOR: blue">If</span> titleField <span style="COLOR: blue"><</span><span style="COLOR: blue">></span> <span style="COLOR: black">""</span> <span style="COLOR: blue">Then</span><br /> <span style="COLOR: blue">If</span> docObj<span style="COLOR: blue">.</span>HasItem<span style="COLOR: blue">(</span>titleField<span style="COLOR: blue">)</span> <span style="COLOR: blue">Then</span> <span style="COLOR: blue">Call</span> docObj<span style="COLOR: blue">.</span>ReplaceItemValue<span style="COLOR: blue">(</span>titleField<span style="COLOR: blue">,</span>fileInfo<span style="COLOR: blue">(</span>0<span style="COLOR: blue">)</span><span style="COLOR: blue">)</span><br /> <span style="COLOR: blue">End</span> <span style="COLOR: blue">If</span><br /> <span style="COLOR: blue">If</span> pathField <span style="COLOR: blue"><</span><span style="COLOR: blue">></span> <span style="COLOR: black">""</span> <span style="COLOR: blue">Then</span><br /> <span style="COLOR: blue">If</span> docObj<span style="COLOR: blue">.</span>HasItem<span style="COLOR: blue">(</span>pathField<span style="COLOR: blue">)</span> <span style="COLOR: blue">Then</span> <span style="COLOR: blue">Call</span> docObj<span style="COLOR: blue">.</span>ReplaceItemValue<span style="COLOR: blue">(</span>pathField<span style="COLOR: blue">,</span>fileInfo<span style="COLOR: blue">(</span>2<span style="COLOR: blue">)</span><span style="COLOR: blue">)</span><br /> <span style="COLOR: blue">End</span> <span style="COLOR: blue">If</span><br /> <span style="COLOR: blue">If</span> repIDField <span style="COLOR: blue"><</span><span style="COLOR: blue">></span> <span style="COLOR: black">""</span> <span style="COLOR: blue">Then</span><br /> <span style="COLOR: blue">If</span> docObj<span style="COLOR: blue">.</span>HasItem<span style="COLOR: blue">(</span>repIDField<span style="COLOR: blue">)</span> <span style="COLOR: blue">Then</span> <span style="COLOR: blue">Call</span> docObj<span style="COLOR: blue">.</span>ReplaceItemValue<span style="COLOR: blue">(</span>repIDField<span style="COLOR: blue">,</span>fileInfo<span style="COLOR: blue">(</span>3<span style="COLOR: blue">)</span><span style="COLOR: blue">)</span><br /> <span style="COLOR: blue">End</span> <span style="COLOR: blue">If</span><br /> <span style="COLOR: blue">If</span> serverField <span style="COLOR: blue"><</span><span style="COLOR: blue">></span> <span style="COLOR: black">""</span> <span style="COLOR: blue">Then</span><br /> <span style="COLOR: blue">Set</span> nname <span style="COLOR: blue">=</span> <span style="COLOR: blue">New</span> <span style="COLOR: black">NotesName</span><span style="COLOR: blue">(</span>fileinfo<span style="COLOR: blue">(</span>1<span style="COLOR: blue">)</span><span style="COLOR: blue">)</span><br /> <span style="COLOR: blue">If</span> docObj<span style="COLOR: blue">.</span>HasItem<span style="COLOR: blue">(</span>serverField<span style="COLOR: blue">)</span> <span style="COLOR: blue">Then</span> <span style="COLOR: blue">Call</span> docObj<span style="COLOR: blue">.</span>ReplaceItemValue<span style="COLOR: blue">(</span>serverField<span style="COLOR: blue">,</span>nname<span style="COLOR: blue">.</span>Abbreviated<span style="COLOR: blue">)</span><br /> <span style="COLOR: blue">End</span> <span style="COLOR: blue">If</span> <br /> <span style="COLOR: blue">Elseif</span> docObjType <span style="COLOR: blue">=</span> <span style="COLOR: black">"NOTESUIDOCUMENT"</span> <span style="COLOR: blue">Then</span><br /> <span style="COLOR: blue">If</span> titleField <span style="COLOR: blue"><</span><span style="COLOR: blue">></span> <span style="COLOR: black">""</span> <span style="COLOR: blue">Then</span><br /> <span style="COLOR: blue">If</span> docObj<span style="COLOR: blue">.</span>Document<span style="COLOR: blue">.</span>HasItem<span style="COLOR: blue">(</span>titleField<span style="COLOR: blue">)</span> <span style="COLOR: blue">Then</span> <span style="COLOR: blue">Call</span> docObj<span style="COLOR: blue">.</span>FieldSetText<span style="COLOR: blue">(</span>titleField<span style="COLOR: blue">,</span>fileInfo<span style="COLOR: blue">(</span>0<span style="COLOR: blue">)</span><span style="COLOR: blue">)</span><br /> <span style="COLOR: blue">End</span> <span style="COLOR: blue">If</span><br /> <span style="COLOR: blue">If</span> pathField <span style="COLOR: blue"><</span><span style="COLOR: blue">></span> <span style="COLOR: black">""</span> <span style="COLOR: blue">Then</span><br /> <span style="COLOR: blue">If</span> docObj<span style="COLOR: blue">.</span>Document<span style="COLOR: blue">.</span>HasItem<span style="COLOR: blue">(</span>pathField<span style="COLOR: blue">)</span> <span style="COLOR: blue">Then</span> <span style="COLOR: blue">Call</span> docObj<span style="COLOR: blue">.</span>FieldSetText<span style="COLOR: blue">(</span>pathField<span style="COLOR: blue">,</span>fileInfo<span style="COLOR: blue">(</span>2<span style="COLOR: blue">)</span><span style="COLOR: blue">)</span><br /> <span style="COLOR: blue">End</span> <span style="COLOR: blue">If</span><br /> <span style="COLOR: blue">If</span> repIDField <span style="COLOR: blue"><</span><span style="COLOR: blue">></span> <span style="COLOR: black">""</span> <span style="COLOR: blue">Then</span><br /> <span style="COLOR: blue">If</span> docObj<span style="COLOR: blue">.</span>Document<span style="COLOR: blue">.</span>HasItem<span style="COLOR: blue">(</span>repIDField<span style="COLOR: blue">)</span> <span style="COLOR: blue">Then</span> <span style="COLOR: blue">Call</span> docObj<span style="COLOR: blue">.</span>FieldSetText<span style="COLOR: blue">(</span>repIDField<span style="COLOR: blue">,</span>fileInfo<span style="COLOR: blue">(</span>3<span style="COLOR: blue">)</span><span style="COLOR: blue">)</span><br /> <span style="COLOR: blue">End</span> <span style="COLOR: blue">If</span><br /> <span style="COLOR: blue">If</span> serverField <span style="COLOR: blue"><</span><span style="COLOR: blue">></span> <span style="COLOR: black">""</span> <span style="COLOR: blue">Then</span><br /> <span style="COLOR: blue">Set</span> nname <span style="COLOR: blue">=</span> <span style="COLOR: blue">New</span> <span style="COLOR: black">NotesName</span><span style="COLOR: blue">(</span>fileinfo<span style="COLOR: blue">(</span>1<span style="COLOR: blue">)</span><span style="COLOR: blue">)</span><br /> <span style="COLOR: blue">If</span> docObj<span style="COLOR: blue">.</span>Document<span style="COLOR: blue">.</span>HasItem<span style="COLOR: blue">(</span>serverField<span style="COLOR: blue">)</span> <span style="COLOR: blue">Then</span> <span style="COLOR: blue">Call</span> docObj<span style="COLOR: blue">.</span>FieldSetText<span style="COLOR: blue">(</span>serverField<span style="COLOR: blue">,</span>nname<span style="COLOR: blue">.</span>Abbreviated<span style="COLOR: blue">)</span><br /> <span style="COLOR: blue">End</span> <span style="COLOR: blue">If</span><br /> <span style="COLOR: blue">End</span> <span style="COLOR: blue">If</span><br /> <span style="COLOR: blue">End</span> <span style="COLOR: blue">If</span><br /> <br /> <span style="COLOR: blue">Case</span> <span style="COLOR: black">"CLEAR"</span><br /> <span style="COLOR: blue">If</span> docObjType <span style="COLOR: blue">=</span> <span style="COLOR: black">"NOTESDOCUMENT"</span> <span style="COLOR: blue">Then</span><br /> <span style="COLOR: blue">If</span> titleField <span style="COLOR: blue"><</span><span style="COLOR: blue">></span> <span style="COLOR: black">""</span> <span style="COLOR: blue">Then</span><br /> <span style="COLOR: blue">If</span> docObj<span style="COLOR: blue">.</span>HasItem<span style="COLOR: blue">(</span>titleField<span style="COLOR: blue">)</span> <span style="COLOR: blue">Then</span> <span style="COLOR: blue">Call</span> docObj<span style="COLOR: blue">.</span>ReplaceItemValue<span style="COLOR: blue">(</span>titleField<span style="COLOR: blue">,</span><span style="COLOR: black">""</span><span style="COLOR: blue">)</span><br /> <span style="COLOR: blue">End</span> <span style="COLOR: blue">If</span><br /> <span style="COLOR: blue">If</span> pathField <span style="COLOR: blue"><</span><span style="COLOR: blue">></span> <span style="COLOR: black">""</span> <span style="COLOR: blue">Then</span><br /> <span style="COLOR: blue">If</span> docObj<span style="COLOR: blue">.</span>HasItem<span style="COLOR: blue">(</span>pathField<span style="COLOR: blue">)</span> <span style="COLOR: blue">Then</span> <span style="COLOR: blue">Call</span> docObj<span style="COLOR: blue">.</span>ReplaceItemValue<span style="COLOR: blue">(</span>pathField<span style="COLOR: blue">,</span><span style="COLOR: black">""</span><span style="COLOR: blue">)</span><br /> <span style="COLOR: blue">End</span> <span style="COLOR: blue">If</span><br /> <span style="COLOR: blue">If</span> repIDField <span style="COLOR: blue"><</span><span style="COLOR: blue">></span> <span style="COLOR: black">""</span> <span style="COLOR: blue">Then</span><br /> <span style="COLOR: blue">If</span> docObj<span style="COLOR: blue">.</span>HasItem<span style="COLOR: blue">(</span>repIDField<span style="COLOR: blue">)</span> <span style="COLOR: blue">Then</span> <span style="COLOR: blue">Call</span> docObj<span style="COLOR: blue">.</span>ReplaceItemValue<span style="COLOR: blue">(</span>repIDField<span style="COLOR: blue">,</span><span style="COLOR: black">""</span><span style="COLOR: blue">)</span><br /> <span style="COLOR: blue">End</span> <span style="COLOR: blue">If</span><br /> <span style="COLOR: blue">If</span> serverField <span style="COLOR: blue"><</span><span style="COLOR: blue">></span> <span style="COLOR: black">""</span> <span style="COLOR: blue">Then</span><br /> <span style="COLOR: blue">If</span> docObj<span style="COLOR: blue">.</span>HasItem<span style="COLOR: blue">(</span>serverField<span style="COLOR: blue">)</span> <span style="COLOR: blue">Then</span> <span style="COLOR: blue">Call</span> docObj<span style="COLOR: blue">.</span>ReplaceItemValue<span style="COLOR: blue">(</span>serverField<span style="COLOR: blue">,</span><span style="COLOR: black">""</span><span style="COLOR: blue">)</span><br /> <span style="COLOR: blue">End</span> <span style="COLOR: blue">If</span> <br /> <span style="COLOR: blue">Elseif</span> docObjType <span style="COLOR: blue">=</span> <span style="COLOR: black">"NOTESUIDOCUMENT"</span> <span style="COLOR: blue">Then</span><br /> <span style="COLOR: blue">If</span> titleField <span style="COLOR: blue"><</span><span style="COLOR: blue">></span> <span style="COLOR: black">""</span> <span style="COLOR: blue">Then</span><br /> <span style="COLOR: blue">If</span> docObj<span style="COLOR: blue">.</span>Document<span style="COLOR: blue">.</span>HasItem<span style="COLOR: blue">(</span>titleField<span style="COLOR: blue">)</span> <span style="COLOR: blue">Then</span> <span style="COLOR: blue">Call</span> docObj<span style="COLOR: blue">.</span>FieldSetText<span style="COLOR: blue">(</span>titleField<span style="COLOR: blue">,</span><span style="COLOR: black">""</span><span style="COLOR: blue">)</span><br /> <span style="COLOR: blue">End</span> <span style="COLOR: blue">If</span><br /> <span style="COLOR: blue">If</span> pathField <span style="COLOR: blue"><</span><span style="COLOR: blue">></span> <span style="COLOR: black">""</span> <span style="COLOR: blue">Then</span><br /> <span style="COLOR: blue">If</span> docObj<span style="COLOR: blue">.</span>Document<span style="COLOR: blue">.</span>HasItem<span style="COLOR: blue">(</span>pathField<span style="COLOR: blue">)</span> <span style="COLOR: blue">Then</span> <span style="COLOR: blue">Call</span> docObj<span style="COLOR: blue">.</span>FieldSetText<span style="COLOR: blue">(</span>pathField<span style="COLOR: blue">,</span><span style="COLOR: black">""</span><span style="COLOR: blue">)</span><br /> <span style="COLOR: blue">End</span> <span style="COLOR: blue">If</span><br /> <span style="COLOR: blue">If</span> repIDField <span style="COLOR: blue"><</span><span style="COLOR: blue">></span> <span style="COLOR: black">""</span> <span style="COLOR: blue">Then</span><br /> <span style="COLOR: blue">If</span> docObj<span style="COLOR: blue">.</span>Document<span style="COLOR: blue">.</span>HasItem<span style="COLOR: blue">(</span>repIDField<span style="COLOR: blue">)</span> <span style="COLOR: blue">Then</span> <span style="COLOR: blue">Call</span> docObj<span style="COLOR: blue">.</span>FieldSetText<span style="COLOR: blue">(</span>repIDField<span style="COLOR: blue">,</span><span style="COLOR: black">""</span><span style="COLOR: blue">)</span><br /> <span style="COLOR: blue">End</span> <span style="COLOR: blue">If</span><br /> <span style="COLOR: blue">If</span> serverField <span style="COLOR: blue"><</span><span style="COLOR: blue">></span> <span style="COLOR: black">""</span> <span style="COLOR: blue">Then</span><br /> <span style="COLOR: blue">If</span> docObj<span style="COLOR: blue">.</span>Document<span style="COLOR: blue">.</span>HasItem<span style="COLOR: blue">(</span>serverField<span style="COLOR: blue">)</span> <span style="COLOR: blue">Then</span> <span style="COLOR: blue">Call</span> docObj<span style="COLOR: blue">.</span>FieldSetText<span style="COLOR: blue">(</span>serverField<span style="COLOR: blue">,</span><span style="COLOR: black">""</span><span style="COLOR: blue">)</span><br /> <span style="COLOR: blue">End</span> <span style="COLOR: blue">If</span><br /> <span style="COLOR: blue">End</span> <span style="COLOR: blue">If</span><br /> <br /> <span style="COLOR: blue">End</span> <span style="COLOR: blue">Select</span><br /><span style="COLOR: blue">End</span> <span style="COLOR: blue">Sub</span><br /></div></pre><br /><br /><div style="BORDER-RIGHT: 1px solid; BORDER-TOP: 1px solid; FONT-SIZE: 8pt; MARGIN: 5px 50px; BORDER-LEFT: 1px solid; COLOR: gray; BORDER-BOTTOM: 1px solid; FONT-FAMILY: sans-serif"><center>This LotusScript was converted to HTML using the <b><i>ls2html</i></b> routine,<br />provided by Julian Robichaux at <a href="http://www.nsftools.com/" target="_blank">nsftools.com</a>.</center></div><br /><br />The comments at the top of the code explain pretty well what is going on: pass it the action you want to take, the field names you want to update and the object containing those fields and it does the work. I honestly don't remember why I wrote it to be able to update either a NotesUIDocument or a NotesDocument, since I think I always pass it a NotesUIDocument, but it gives you the flexibility to pass either object type.<br /><br />Technorati: <a href="http://technorati.com/tag/show-n-tell" rel="tag">Show-n-Tell Thursday</a>Don McNallyhttp://www.blogger.com/profile/17995406659448494074noreply@blogger.com0tag:blogger.com,1999:blog-24863326.post-90210563126713819722011-01-20T13:11:00.009-05:002022-11-19T11:38:48.406-05:00Configurable Embedded Views (Single Category Views in External Notes Databases)<img align="right" src="http://webpages.charter.net/dtmc/images/SNTTbtn.png" />Have you ever had Notes application A that had related data in Notes application B where you wanted to show the application B data when you opened a document in application A? I have, and I found there are lots of ways to combine data like this. If it were all in one database, it would be pretty trivial to create an embedded view and show the single category that relates to the open document. It gets complicated when you want to leave the data in its source database rather than copying it to where you want it, and feels almost impossible if you then introduce multiple environments (eg. development, QA and production).<br />
<br />
Composite Applications, introduced in Notes 8, were one way to accomplish this, but they never really caught on, due in part to their own deployment issues. XPages is the latest silver bullet that can be used to slay this beast. But what if you don’t have Notes 8.5.1 and aren’t going to move to it in the near future? Or you don’t have the time to focus on learning XPages right now?<br />
<br />
There is a way to make these related applications work in a configurable way in the Notes 6 and 7 client. This is not original thinking. See Nathan Freeman’s blog (ntf.gbs.com) for the Sesame Street and Area 51 demos for the earlier documentation. I just thought it was worth bringing up again.<br />
<br />
The entire solution is based on three features of Domino Designer and Notes that haven’t gotten a lot of attention: computable frameset contents, embedded editors and forms that open in framesets. Nathan’s Sesame Street demo showed how you could embed a fully functional frameset on a form. That was great, unless you only wanted a single category of a view. That problem was solved in the Area 51 demo.<br />
<br />
For this demo, I have an application catalog. Not *the* application catalog that each Domino server has, but one that adds on to that catalog to add a description, an application owner and technical support staff. Rather than copying everything from the system catalog, we’ll reference the catalog from within our application. I’ll refer to the application catalog as the “source” database and the system catalog as the “target” database. The source database documents will contain the replica ID of the application database that will be our lookup key for the target database.<br />
<ol></ol>
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhypfBUB5KBxNpMn9kk-MIQdTkSPJBhyZ5gScKLmS7m3px__168zo8_tgU-cZhqql9liH-I_LkKYdDnbEWC3lEfwpd5VTtpLWaeVvF_sdi9-XQnwDJoeDvUerKldCHsD58hYg_WEA/s1600/AppTab1.JPG"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5564338063931007010" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhypfBUB5KBxNpMn9kk-MIQdTkSPJBhyZ5gScKLmS7m3px__168zo8_tgU-cZhqql9liH-I_LkKYdDnbEWC3lEfwpd5VTtpLWaeVvF_sdi9-XQnwDJoeDvUerKldCHsD58hYg_WEA/s320/AppTab1.JPG" style="cursor: hand; display: block; height: 158px; margin: 0px auto 10px; text-align: center; width: 320px;" /></a><br />
<br />
<b>Target Database</b><br />
1. Create a form in the system catalog with an embedded view on it, with the view being one categorized by replica ID. You can create a view specific to your needs or use one that is already in the system catalog.<br />
<br />
2. Set the form so that it does not appear on the Create menu.<br />
<br />
3. On the embedded view, the Show Single Category property should be set to @Word(@Environment(“Catalog_Ref”);”~” ;4). The environment variable will be set by the source application and will be explained in the next section.<br />
<br />
4. Save the form with the name OneReplica.<br />
<br />
This must be created in the target database so that you can choose the “Current Database” option as the source of the embedded view. Because there is not a way to programmatically select the source of an embedded view, this ensures the proper view will always open, whether you are looking at your Dev, QA or Production system catalog.<br />
<br />
<b>Source Database</b><br />
This database should already have a form in it for the application items that you want in addition to what is in the system catalog. To make this work, you need a field on the form for the replica ID of the application, and the value should be in the formula format (12345678:87654321), unless you created a new view in the target database that reformats the replica ID. You should also have a view that lists the application documents.<br />
<br />
There are two elements that need to be added in the source database and one change to the application form you already have. The new elements are a frameset that will display the form from the target database, and a wrapper form that is set to display in a frame of the new frameset. On the application form, you need to add an embedded editor that will display the wrapper form.<br />
<br />
1. Create a frameset with three frames: two side-by-side on top and one full width on the bottom. Set the top frames to a relative height of 1 and 50% width. The borders should be set to not be 3D and 2 pixels in width.<br />
<br />
2. Name the top left frame NotesView and set its target to NotesDocView. Set the Content Type to Named Element and click the ‘@’ icon to enter formulas to determine the element. The kind of element should have the formula @Word(@Environment(“Catalog_Ref”);”~” ;2), the database should be the formula @Word(@Environment(“Catalog_Ref”);”~” ;1) and the element name should be the formula @Word(@Environment(“Catalog_Ref”);”~” ;3). The kind and name of the element could actually be constants, but it is a better practice to compute them in case they need to change later or you want to use this frameset for more than one purpose.<br />
<ol></ol>
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEirTOR7ISwK3V81hGPiehhX8NLzFprMxVdPhc9iBH5rJMMwHghmy9WBYa7ZWj8fX57ZRAeW3M1hV7C_3-KoNkDPRjwVdT-juqD3zt9Of6f-jltGC88pmhKzs7Q4t0mz2IYcLQY1Ow/s1600/ComputedElementDialog.JPG"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5564338842416393682" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEirTOR7ISwK3V81hGPiehhX8NLzFprMxVdPhc9iBH5rJMMwHghmy9WBYa7ZWj8fX57ZRAeW3M1hV7C_3-KoNkDPRjwVdT-juqD3zt9Of6f-jltGC88pmhKzs7Q4t0mz2IYcLQY1Ow/s320/ComputedElementDialog.JPG" style="cursor: hand; display: block; height: 280px; margin: 0px auto 10px; text-align: center; width: 320px;" /></a><br />
<br />
3. Name the top right frame NotesDocView.<br />
<br />
4. Name the bottom frame InvisibleMan. The height of InvisibleMan should be 0 pixels, Scrolling should be Off and Allow Resizing should be No. We don’t want to see this frame; it is used only as the target for the form we want to open in a frameset.<br />
<br />
5. Save the frameset as CatalogView.<br />
<br />
6. Create a form. It should be set so that it does not appear on the Create menu. On the Launch properties, set it to open in the CatalogView frameset in the InvisibleMan frame. The only purpose of this form is to open in a frameset so the frameset can appear to be embedded on another form. This form could also set the environment variable, but we are not doing so in this example.<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhP92y2br6B0MGRTGEPrg6KtFIyG84HZ76Adhg_SDHqha32JVRtuQSzO6QtaeUwSU9EGz82fVln-206ZKvGMssUMcCmpMeda_ChJTmeZsqnZmG-o5qgmtnneL6HgBazPRKPPgW6fQ/s1600/FormProperties.JPG"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5564339160092068370" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhP92y2br6B0MGRTGEPrg6KtFIyG84HZ76Adhg_SDHqha32JVRtuQSzO6QtaeUwSU9EGz82fVln-206ZKvGMssUMcCmpMeda_ChJTmeZsqnZmG-o5qgmtnneL6HgBazPRKPPgW6fQ/s320/FormProperties.JPG" style="cursor: hand; display: block; height: 313px; margin: 0px auto 10px; text-align: center; width: 307px;" /></a><br />
<br />
7. Save the form with the name Wrapper.<br />
<br />
8. In the application documentation form (assumed to have been created previously), add an embedded editor that opens the Wrapper form. In the PostOpen of this form, you need code to set the Catalog_Ref environment variable. The variable should be in the format server!!filepath~Form~OneReplica~replicaID. The server could be computed based on the location of the source application and the filepath is probably constant across the environments, so you could probably enter it directly (remember to escape any ‘\’ in the path). The element type and name will be constant and the replica ID is the name of the field containing the replica ID. More likely, the server and filepath for the catalog would be read from a configuration document. Just keep in mind that the location of the target database (that is, both the server and file path) should not be hard coded, because that defeats the purpose of this solution: to make it possible to migrate from your dev to production environment without making programming changes as the moves are completed.<br />
<br />
9. Save the updated form.<br />
<br />
Once everything is saved, go to your source database and open or create a document using the application documentation form. If all goes well, you should see an embedded view that contains the documents from the system catalog for the replica ID of the database you are looking at, and a box to the right that should display the document you click in the view. See, a real embedded frameset!<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiPxJKFmMRyrr7AoygVC52weJe4dStFr5MYeI_sunxTNvdFJ_uRD1t3UThbhDojZpjFfOnhnsO-vz1NEt2qql0Om1K-p9IHULhZSBYVJxs4K7Rr4ICHybWR-5eD6JFgDgirgVMF7g/s1600/AppTab2.JPG"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5564340299124366994" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiPxJKFmMRyrr7AoygVC52weJe4dStFr5MYeI_sunxTNvdFJ_uRD1t3UThbhDojZpjFfOnhnsO-vz1NEt2qql0Om1K-p9IHULhZSBYVJxs4K7Rr4ICHybWR-5eD6JFgDgirgVMF7g/s320/AppTab2.JPG" style="cursor: hand; display: block; height: 220px; margin: 0px auto 10px; text-align: center; width: 320px;" /></a><br />
<br />
If you want to prove that it is configurable via the environment variable, set the environment variable to @Implode(@MailDbName;"!!")+"~Folder~($Inbox)" and see that it opens your mail box.<br />
<br />
Once you wrap your head around how this works, the possibilities become nearly endless. Multi-database applications can become far more informative.<br />
<br />
<b>Update:</b> Here is a link to a <a href="https://drive.google.com/file/d/0B9TSgj6TtNu8enR6cDlBQXJLR0E/view?usp=sharing&resourcekey=0-g4lWvHmSvcMe5wxAkn-r6Q" rel="nofollow" target="_blank">complete "Source" database</a> that also includes the form that needs to be copied into the "Target" database. Hope that helps flesh out the example a little more. <br />
<br />
<br />
Technorati: <a href="http://technorati.com/tag/show-n-tell" rel="tag">Show-n-Tell Thursday</a>Don McNallyhttp://www.blogger.com/profile/17995406659448494074noreply@blogger.com18tag:blogger.com,1999:blog-24863326.post-84832032080137819382009-10-29T14:30:00.003-04:002009-10-29T14:39:12.624-04:00Create Excel Lists Using LotusScript<img src="http://webpages.charter.net/dtmc/images/SNTTbtn.png" align="right" />I have created some code – as many others have over time – to export a Notes view to Excel. The code creates the Excel application object, a workbook and a worksheet and then populates the new worksheet with data from the view. The twist to this request was that once the export was done, they wanted to have some of the columns use the List function of Excel; that is, they wanted to be able to filter the columns based on the values in them.<br /><br />I recorded a macro in Excel to get the VBA code so I could translate it to LotusScript, and this is what I got:<br /> Range("I1").Select<br /> ActiveSheet.ListObjects.Add(xlSrcRange, Range("$I:$I"), , xlYes).Name = "List1"<br /><br />Uh oh. How do I pass parameters to the Add method and set the value for the Name property in the same line? I remembered from past experience, this type of code is tricky to do in LotusScript. Searching didn’t reveal where anyone had done this exact thing before but I found an example that led me to the solution. The code is (I found that xlSrcRange and xlYes both have a value of 1):<br /><style>.lotusscript { font-family: sans-serif; font-size: 9pt; color: black; }<br />.ls-comment { color: green; }<br />.ls-quote { color: black; }<br />.ls-datatype { color: black; }<br />.ls-operator { color: blue; }<br />.ls-keyword { color: blue; }<br />.ls-statement { color: blue; }<br />.ls-function { color: blue; }<br />.ls-class { color: black; }<br />.ls-constant { color: purple; }<br /></style><br /><pre><div class=lotusscript><br /> <font class=ls-statement>Set</font> xlApp <font class=ls-operator>=</font> <font class=ls-function>CreateObject</font><font class=ls-operator>(</font><font class=ls-quote>"Excel.Application"</font><font class=ls-operator>)</font><br /> <font class=ls-statement>Set</font> xlWkBook <font class=ls-operator>=</font> xlApp<font class=ls-operator>.</font>Workbooks<font class=ls-operator>.</font>Add<font class=ls-operator>(</font><font class=ls-operator>)</font><br /> <font class=ls-statement>Set</font> xlWkSheet <font class=ls-operator>=</font> xlWkBook<font class=ls-operator>.</font>Worksheets<font class=ls-operator>.</font>Add<font class=ls-operator>(</font><font class=ls-operator>)</font><br /><br /> <font class=ls-statement>With</font> xlWkSheet<font class=ls-operator>.</font>ListObjects<br /> xlWkSheet<font class=ls-operator>.</font>Range<font class=ls-operator>(</font><font class=ls-quote>"I1"</font><font class=ls-operator>)</font><font class=ls-operator>.</font><font class=ls-statement>Select</font><br /> <font class=ls-function>Call</font> <font class=ls-operator>.</font>Add<font class=ls-operator>(</font>1<font class=ls-operator>,</font>xlWkSheet<font class=ls-operator>.</font>Range<font class=ls-operator>(</font><font class=ls-quote>"$I:$I"</font><font class=ls-operator>)</font><font class=ls-operator>,</font><font class=ls-operator>,</font>1<font class=ls-operator>)</font><br /> <font class=ls-statement>End</font> <font class=ls-statement>With</font><br /> xlWkBook<font class=ls-operator>.</font>InactiveListBorderVisible <font class=ls-operator>=</font> <font class=ls-constant>False</font><br /></div></pre><br /><br /><div style="FONT-SIZE: 8pt; COLOR: gray; FONT-FAMILY: sans-serif; BORDER: 1 solid Grey; margin : 5px 50px 5px 50px;"><center>This LotusScript was converted to HTML using the <b><i>ls2html</i></b> routine,<br>provided by Julian Robichaux at <a href="http://www.nsftools.com" target="_blank">nsftools.com</a>.</center></div><br />The xl* objects are defined as variants. The last line turns off the borders for the inactive lists. Note that I didn’t worry about naming the list as the VBA script does. I’m not sure if that has any long-term impact or not. The actual code also duplicates the two lines in the With clause for three other columns.<br /><br />When the Excel file opens, the column headings are bolded and, when you click in the column, an arrowhead appears. Click on that and it gives you the unique values in the column plus a few default options (eg. All, Top 10, blanks, etc.).<br /><br />It isn’t perfect since I haven’t figured out how to keep the border hidden when they click on a filtered column nor how to make the arrowhead that indicates a filter is present be visible at all times. But, considering I wasn’t even sure how to do it in the first place, I’ll call it a success.<br /><br /><br />Technorati: <a href="http://technorati.com/tag/show-n-tell thursday" rel="tag">Show-n-Tell Thursday</a>Don McNallyhttp://www.blogger.com/profile/17995406659448494074noreply@blogger.com2tag:blogger.com,1999:blog-24863326.post-22738344431126799202009-10-29T14:27:00.003-04:002009-10-29T14:30:25.288-04:00SnTT: One Thing I've Learned<img src="http://webpages.charter.net/dtmc/images/SNTTbtn.png" align="right" />A few months ago, I developed a new interface to consolidate a number of our applications into what would look like a single-database application. The main part of the interface is a frameset in the “portal” database that consists of a top navigator frame and a bottom “main content” frame. The hotspot links in the top frame execute LotusScript code to write an environment variable to notes.ini that indicates the database server and path, element type and element name that should be opened, then targets the main content frame and opens a single-frame frameset whose content is computed using the environment variable.<br /><br />Still with me? Anyway, this has worked really well with just a few oddities. Object focus is an issue sometimes, and I can’t get everything that opens in that frameset to update the window title properly (they say “Untitled” instead of the name of the application and view).<br /><br />But the biggest problem I had was that some of the applications have buttons to create documents in the background and then display them when the build process is done. This is when the loss of focus becomes an issue. Notes seems to just lose track of where it is and does what appears to the user to be a random change of the view. Plus they don’t get the document (report) they are expecting. I can only attribute this to the nesting of objects (the code is in a button, which is in a view, which is in a frameset, which is in an application, and that application is contained in a frameset in another application).<br /><br />Still there? When the document is built, I use NotesUIWorkspace.EditDocument to open it so the user can see it. The solution to the random (incorrect) behavior is to add the line Call NotesUIWorkspace.SetTargetFrame(“_blank”) before the EditDocument command. This forces the new document to open in a new tab. Plus, it works whether the application containing the code is open within my portal database or in its own tab, as if it were opened from a bookmark or the workspace.<br /><br />I’d post a picture of the portal application, but I’m not sure I’m allowed yet. I will try to get a mockup of something I can post since that may help this make more sense.<br /><br />Technorati: <a href="http://technorati.com/tag/show-n-tell thursday" rel="tag">Show-n-Tell Thursday</a>Don McNallyhttp://www.blogger.com/profile/17995406659448494074noreply@blogger.com0tag:blogger.com,1999:blog-24863326.post-59851654820881309162008-04-04T15:50:00.004-04:002008-04-04T16:18:10.960-04:00SnTT: @Transform Goodness<img src="http://webpages.charter.net/dtmc/images/SNTTbtn.png" align="right" />If you went to <a href="http://www.lotusgeek.com" target="_blank">Rocky</a>'s session at Lotusphere 2008 on Formula Tricks, you heard about the @Transform function. It lets you take a list of values and run a formula against it to change some or all of the values in the list based on your criteria.<br /><br />I've used it in two places recently and it works far better than anything we could have done prior to the function becoming available (which was in Release 6). In my first example, I have a list of dates and a list of numbers. I want to sum the numbers only for the dates that are in the future. For example, say the fields are:<br />SchedDates is 3/24/08:3/31/08:4/7/08:4/14/08<br />SchedDays is 5:5:4:5<br /><br />If I look at this on 4/4/08, I want to end up with 9. This is the formula I run:<br /><br />@If(SchedDates="";0;@Sum(@Transform(@Text(SchedDates) + "~" + @Text(SchedDays);"x";@If(@TextToTime(@Left(x;"~"))<@Today;0;@TextToNumber(@Right(x;"~"))*8))))<br /><br />That takes the two fields and joins them (eg. 03/24/2008~5:03/31/2008~5:04/07/2008~4:04/14/2008:5), then replaces each one with 0 if the date portion is before today or the number portion if not. So you end up with 0:0:4:5. When you @Sum that, you get 9. Cool!<br /><br />The other time I used it recently was to make sure that a name was placed in a field as long as it wasn't already in that field or five other fields. That formula looks like this and is in the Input Translation of a field (in this case Level5):<br /><br />reqNames:=@Name([Canonicalize];"James Q Public/MyCo/US");<br />@If(reqNames*=Level0:Level1:Level2:Level3:Level4:Level5;@Trim(@Transform(reqNames;"x";@If(@IsMember(x;Level0:Level1:Level2:Level3:Level4:Level5);@Nothing;x)):@ThisValue);<br /> @Trim(reqNames:@ThisValue))<br /><br />The reqNames field can be multi-valued. If any of the values in the field are in any of the listed fields (Level0, etc.), it replaces that value in reqNames with @Nothing. When all values in reqNames are checked, it prepends the remaining names to the field where the formula lives. If not, reqNames is just prepended to the existing field value, making sure there are no blank entries in the list.<br /><br />If you haven't already, be sure you take a look at @Transform.<br /><br />Technorati: <a href="http://technorati.com/tag/show-n-tell thursday" rel="tag">Show-n-Tell Thursday</a>Don McNallyhttp://www.blogger.com/profile/17995406659448494074noreply@blogger.com0tag:blogger.com,1999:blog-24863326.post-38992361317302257952007-08-09T09:10:00.000-04:002007-08-09T09:11:07.694-04:00SnTT: Sametime 7.5.1 - Dual Repositories for User Information<img src="http://webpages.charter.net/dtmc/images/SNTTbtn.png" align="right" />A new feature in Sametime 7.5.1 allows you to pull user information displayed on the Sametime business card from two different sources (see this <a href="http://www-10.lotus.com/ldd/stfixlist.nsf/8ed1b46cfdba8957852570c90054623b/d03303d833a5cf0b852572d6006b9cec?OpenDocument">fix list item</a>). So you can get the user's name and location from, say, a Domino Directory and their job title and photo from another Domino database. We had that exact need so this was a nice feature add for us. Another positive is that this lets you include a photo on the business card without requiring changes to your Domino Directory. Note that this also works if one of your data sources is an LDAP directory; I am not discussing that because we don't have that need but the instructions are documented and are pretty similar.<br /><br />The instructions in the Sametime Information Center for <a href="http://publib.boulder.ibm.com/infocenter/sametime/v7r5m1/topic/com.ibm.help.sametime.admin.doc/st_adm_buscard_configdualdomcustom.html">configuring dual Domino repositories</a> are a little confusing in spots (why talk about Sametime using LDAP for authentication when the topic is using the Domino Directory and a custom database?) but they do work. The instructions do not include a detail tag for a photo but it looks like this: <Detail Id="Photo" FieldName="Photo" Type="image/jpeg" />. If you use GIFs instead of JPEGs, set the type to "image/gif". And the value of the FieldName item needs to be the name of the field containing your photo.<br /><br />One caveat: make sure you type the XML exactly as shown in the example. We spent way too long troubleshooting problems only to find we had two typos. Whatever the reason, the items in the XML tags must be entered exactly as they appear in the example. We had entered one item name as "DBName" instead of "DbName" and another as "Fieldname" instead of "FieldName". As you might imagine, these were a little hard to pick out after staring at the file for a while. Once we corrected the errors, the data and photo from the second database was included in the Sametime business card.<br /><br />As far as we can tell, this does NOT require 7.5.1 CF1 to work, although we are running CF1 on the box where this is enabled. And it does work if the secondary database is in a folder accessed via directory link. Thanks go to <a href="http://www.bingham.co.za">Grant Bingham</a> for the setup instructions for 7.5 (some of which may be made obsolete by this new function) and to <a href="http://www.lekkimworld.com">Mikkel Heisterberg</a> for the reverse engineering of the userInfo servlet calls, both of which were helpful as we worked our way through this setup.<br /><br />Ultimately, since the instructions published by Lotus do work, I guess this just serves as a warning to anyone who attempts this to be very careful in your typing.<br /><br />Technorati: <a href="http://technorati.com/tag/show-n-tell thursday" rel="tag">Show-n-Tell Thursday</a>Don McNallyhttp://www.blogger.com/profile/17995406659448494074noreply@blogger.com1tag:blogger.com,1999:blog-24863326.post-16784206809852401942007-06-15T08:02:00.000-04:002007-06-15T08:10:14.592-04:00SnTT: Open the Database Containing a Doc Link<img src="http://webpages.charter.net/dtmc/images/SNTTbtn.png" align="right" />This probably belongs on <a href="http://www.alanlepofsky.net">Alan Lepofsky</a>'s site - and maybe it's already there - but I thought it was pretty handy when I figured it out.<br /><br />I've had lots of occasions where I get a doc link, open the linked document and then want to open the database to see other related documents. I had always gone to my workspace, located the icon for the database I just opened and clicked it to open the database. I figured there had to be an easier way and I finally found it:<br /><br />Click View | Go up to Parent View.<br /><br />This opens the view that contains the linked document. Action buttons can execute @Command([GoUpLevel]) to achieve the same effect.<br /><br />This is probably not news to a lot of people but I just learned about it. Now, if only you could right click on the open document and see the option there...<br /><br />Technorati: <a href="http://technorati.com/tag/show-n-tell thursday" rel="tag">Show-n-Tell Thursday</a>Don McNallyhttp://www.blogger.com/profile/17995406659448494074noreply@blogger.com1tag:blogger.com,1999:blog-24863326.post-78240271842903126002007-06-01T14:30:00.000-04:002007-06-01T15:21:51.343-04:00SnTT: Update to Single-View User Interface<img src="http://webpages.charter.net/dtmc/images/SNTTbtn.png" align="right" />I know it's Friday but this was so cool I just couldn't wait until next week.<br /><br />About a month ago I posted <a href="http://dmcnally.blogspot.com/2007/05/sntt-single-view-user-interface.html">this</a> in which I explained how I got around some limitations with embedded views that are displayed in framesets. It is, admittedly, not the most elegant solution because it requires the user to click a button to update the embedded view. It also requires some inter-frame communication, for which I used an environment variable. I had thought about using JavaScript (and actually saw another post that might have been useful for that) but didn't get a chance to do anything about it.<br /><br />But that was before Slawek Rogulski posted to say he knew how to make this work using a single frame and form. Read the comments on the first post and you'll see I was a little skeptical. Well, I got it to work. And it's really pretty simple.<br /><br />1. In your form that contains the embedded view, set it so it has a Form Header with a height of Fit to Content. You may also want to set a border of 1 pixel to ensure that everything you want in the header is actually there (you can turn that off once you get the header content correct).<br /><br />2. Select everything on the form that is above the embedded view and cut it.<br /><br />3. Move the cursor to the top of the form and paste the cut content back in. That should place it in the header area.<br /><br />4. Save the form.<br /><br />Now you can just compose a document with that form or place it in the top frame of a two-frame frameset (if you want the bottom frame to be a preview frame). The scroll bars on the embedded view will appear when necessary and will display both the scroll up and scroll down buttons. Plus, there is no need for inter-frame communication or for the user to click a button after selecting field values - once you select a value in the "trigger" field, the embedded view can be updated automatically.<br /><br />Apparently the rendering engine can account for the height of a form header when displaying the embedded view. If you don't use a header, it does not know the height of form content above the embedded view; as a result, the scroll bar is not rendered correctly (documents in the view can exist below the bottom edge of the application window and there is no way to see the view entries, although you can press PAGE DOWN to move the highlight to them and press ENTER to open the documents).<br /><br />Thanks a lot, Slawek. This provides a great solution for simple interfaces and is a lot easier than what I initially came up with. He also said he's used this in cases where the embedded view is computed (I didn't need that capability). And it's another great example of the "power of community".<br /><br />Technorati: <a href="http://technorati.com/tag/show-n-tell thursday" rel="tag">Show-n-Tell Thursday</a>Don McNallyhttp://www.blogger.com/profile/17995406659448494074noreply@blogger.com1tag:blogger.com,1999:blog-24863326.post-47722004219880207372007-05-03T08:45:00.000-04:002007-05-03T08:51:43.310-04:00SnTT: Single-View User Interface Problems (and Solutions)<img src="http://webpages.charter.net/dtmc/images/SNTTbtn.png" align="right" />Lately, I've been doing a lot of user interface work. I've updated the interfaces on three of our applications and created a new interface for the Team Room (7) template. I also used a completely different (for us) style on a new database I developed, which is what I want to share today.<br /><br />This is a database for which we wanted to keep the interface simple. The primary users are more focused on mainframe programming and don't use a lot of Notes applications, so rather than giving them a list of views to choose from and hundreds of documents to wade through, I embedded a view on a form (called, very creatively, User Interface) and let them choose the category they want to see using a radio button and drop down list on the form. When it is opened, the database creates a document using the UI form and displays the category for the user who opened the database (as in "My Documents"). The embedded view is set to Fit to Window for both height and width.<br /><br />The original idea was to display the UI form in the top frame of a two-frame frameset. The bottom frame, collapsed by default, would be for previewing documents in the embedded view.<br /><br />All seemed to be going well until I ran into a category that had too many entries to fit on one screen. In that case, I could see a scroll bar at the right of the view but the last two documents could not be seen. After a while, I discovered that was because Notes does not include the height of text above the embedded view when it calculates the height of the embedded view to fit it to the window. In other words, my embedded view extended below the bottom of the screen by the amount of space I had above it on the form.<br /><br />Because this was my interface (not an individual document), I didn't want a scroll bar at the right side at all times. I didn't want the user to have to scroll both the interface form and the embedded view, and I didn't want the top part of the form to scroll off the screen so I could see the bottom of the view. So, what to do? I had a couple of ideas that involved tables but I couldn't get the table row height to automatically go to the window height. Setting the height of the embedded view also didn't work because it didn't scroll correctly if the preview pane opened up big enough to hide part of the view. I also thought of a little more traditional two-pane setup but liked the basic idea I had better. "A-HA!," I thought, "I'll create a frameset with three frames." The "A-HA" was easier than the implementation ended up being.<br /><br />The top frame contains a form with the radio button and drop down list in it, the middle frame contains the embedded view, and the bottom frame is the preview frame. Here is the frameset in Designer:<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgzLmmE1w2Gs5v9FiymjN6-HUdyk3PxerhBpqfQK7RZ1qkNideeTiIC0nlwmXcHAvsYfh7bHaPdpI6twI6whsu1ZVOfylj5AERvYY9cAv5O1dKexq5YuQKUv8dOSypn615SwjTw_g/s1600-h/SNAPTrackDesigner.JPG"><img style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgzLmmE1w2Gs5v9FiymjN6-HUdyk3PxerhBpqfQK7RZ1qkNideeTiIC0nlwmXcHAvsYfh7bHaPdpI6twI6whsu1ZVOfylj5AERvYY9cAv5O1dKexq5YuQKUv8dOSypn615SwjTw_g/s320/SNAPTrackDesigner.JPG" border="0" alt=""id="BLOGGER_PHOTO_ID_5060045651909073954" /></a><br /><br />Because the embedded view is the only thing on the form in the middle frame, setting its height to Fit to Window does not present any scrolling problems; the scroll bar appears when it is needed due to the number of documents in view or when the preview pane is open. The problem is communicating changes in the fields in the top frame to the view in the middle frame so that the view displays the proper category. I decided to use an environment variable for that communication, setting it in the top frame and reading it in the middle frame. I was going to use the OnChange event of the drop down field to trigger the update but @formulas can't be used there. I also found that a) I couldn't set the variable using @formulas and b) I couldn't update the middle frame using LotusScript. GRRRR! I still don't know why neither @Environment nor @SetEnvironment worked. Maybe I needed to restart my client or something.<br /><br />At any rate, I ended up adding a button next to the drop down with two @formulas: one that runs a small LotusScript agent to set the environment variable and one that refreshes the frame containing the view. I also put an @Environment command in the QueryOpen of the form in the top frame (it worked there!) to set the default value of the variable. After all that work, it looks the way I wanted it and scrolls properly:<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgXUpgQbjzqidcxBzk46qsKJgZ0pnMpd00t-GVcWHhSb5ghvuBy60y9JxMLBG5MZ0vAw9-dfa4UYlahLUPUMKSBJUdbGA5DwqX6P1Scr-7BhUAdc4kqw5Cmz15vb4897rKPx0eHxg/s1600-h/SNAPTrack.JPG"><img style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgXUpgQbjzqidcxBzk46qsKJgZ0pnMpd00t-GVcWHhSb5ghvuBy60y9JxMLBG5MZ0vAw9-dfa4UYlahLUPUMKSBJUdbGA5DwqX6P1Scr-7BhUAdc4kqw5Cmz15vb4897rKPx0eHxg/s320/SNAPTrack.JPG" border="0" alt=""id="BLOGGER_PHOTO_ID_5060045922492013618" /></a><br /><br />I don't love the 'Go' button I had to add but I couldn't come up with another way to trigger the updates I needed to refresh the embedded view. I thought later about using JavaScript to update the view frame and refresh it but I don't know JavaScript very well and couldn't find a good example for what I wanted to do. Maybe I'll revisit it later.<br /><br />But in the meantime, maybe this is something you can add to your own toolset as you think about your application interfaces.<br /><br />Technorati: <a href="http://technorati.com/tag/show-n-tell thursday" rel="tag">Show-n-Tell Thursday</a>Don McNallyhttp://www.blogger.com/profile/17995406659448494074noreply@blogger.com6tag:blogger.com,1999:blog-24863326.post-48798377818959898442007-04-12T12:37:00.000-04:002007-04-12T12:46:08.101-04:00SnTT: Toolbar Icon to Change Font Colors<img src="http://webpages.charter.net/dtmc/images/SNTTbtn.png" align="right" />This is a quick and dirty tip inspired by a request from a co-worker the other day. He wanted to know if there was a way to quickly change font colors in Notes, similar to the buttons available in Microsoft applications. I said I wasn't aware of one and couldn't locate one after some searching. So, since he's a good guy, and I like to keep people happy with Notes, I decided to write something quickly.<br /><br />This is the code:<br />colorchoices:="Black":"Gray":"Red":"DarkRed":"Green":"DarkGreen":"Blue":"DarkBlue":<br />"Magenta":"DarkMagenta":"Yellow":"Brown":"Cyan":"DarkCyan":"White";<br />colorcodes:=[Black]:[Gray]:[Red]:[DarkRed]:[Green]:[DarkGreen]:[Blue]:[DarkBlue]:[Magenta]:[DarkMagenta]:[Yellow]:[Brown]:[Cyan]:[DarkCyan]:[White];<br />newcolor:=@Prompt([OkCancelList];"Choose a Color";"Choose the color to apply to the text: ";"Black";colorchoices);<br />@If(newcolor="[]";@Return("");<br /> @Command([TextSetFontColor];@Subset(@Subset(colorcodes;@Member(newcolor;colorchoices));-1)))<br /><br />I set the button caption to 'Change Font Color' and popup help to 'Change the font color' and chose the toolbar_paintblu.gif icon from the list of available icons.<br /><br />Note that I said this was "quick and dirty". The code could probably be more efficient but it is functional. Note also that this button will work to set a font color at the insertion point or to change the color of selected text.<br /><br />Enjoy! <br /><br />Technorati: <a href="http://technorati.com/tag/show-n-tell thursday" rel="tag">Show-n-Tell Thursday</a>Don McNallyhttp://www.blogger.com/profile/17995406659448494074noreply@blogger.com1tag:blogger.com,1999:blog-24863326.post-11496039291210748442007-01-21T23:32:00.000-05:002007-01-22T16:50:14.902-05:00SnTT: Lookup Names in Your Active Directory Global Catalog<img src="http://webpages.charter.net/dtmc/images/SNTTbtn.png" align="right" />We are a small Domino island is a large Microsoft sea. Recently, we implemented a web service that required access by non-Notes users to one of our Notes databases. As such, we needed a way to determine which documents those users were allowed to see and we decided to use their unique corporate identifier. Rather than forcing the users to type that information in (as well as the user's name so it was easy to identify) and/or look the names up manually in some external system, we developed a dialog that uses the Active Directory Service Interface objects.<br /><br />The form enables a user to enter a full or partial last name plus (optionally) a full or partial first name. When the Search button is clicked, the code looks in the Global Catalog and returns the list of names and unique IDs. Due to the 64K limit on fields, the list will only return 1000 names and will inform the user that there are more names and that they should refine their search term to reduce the list.<br /><br />In the code, doc.SearchString is the field on the dialog where the user enters what they are searching for. You need to replace GlobalCatalog.url with the path to your global catalog. I did not include the code but we also implemented a sort routine to provide the returned list in alphabetical order.<br /><br /><style>.lotusscript { font-family: sans-serif; font-size: 9pt; color: black; }<br />.ls-comment { color: green; }<br />.ls-quote { color: black; }<br />.ls-datatype { color: black; }<br />.ls-operator { color: blue; }<br />.ls-keyword { color: blue; }<br />.ls-statement { color: blue; }<br />.ls-function { color: blue; }<br />.ls-class { color: black; }<br />.ls-constant { color: purple; }<br /></style><br /><pre><div class="lotusscript"><br /><span class="ls-statement">Sub</span> Click<span class="ls-operator">(</span>Source <span class="ls-keyword">As</span> <span class="ls-class">Button</span><span class="ls-operator">)</span><br /> <span class="ls-statement">Dim</span> ws <span class="ls-keyword">As</span> <span class="ls-keyword">New</span> <span class="ls-class">NotesUIWorkspace</span><br /> <span class="ls-statement">Dim</span> doc <span class="ls-keyword">As</span> <span class="ls-class">NotesDocument</span><br /> <span class="ls-statement">Dim</span> objConnection <span class="ls-keyword">As</span> <span class="ls-keyword">Variant</span><br /> <span class="ls-statement">Dim</span> objCommand <span class="ls-keyword">As</span> <span class="ls-keyword">Variant</span><br /> <span class="ls-statement">Dim</span> LastName <span class="ls-keyword">As</span> <span class="ls-keyword">String</span><br /> <span class="ls-statement">Dim</span> ID <span class="ls-keyword">As</span> <span class="ls-keyword">String</span><br /> <span class="ls-statement">Dim</span> FN <span class="ls-keyword">As</span> <span class="ls-keyword">String</span><br /> <span class="ls-statement">Dim</span> LN <span class="ls-keyword">As</span> <span class="ls-keyword">String</span><br /> <span class="ls-statement">Dim</span> nameSearch <span class="ls-keyword">As</span> <span class="ls-keyword">String</span><br /> <span class="ls-statement">Dim</span> NameList<span class="ls-operator">(</span><span class="ls-operator">)</span> <span class="ls-keyword">As</span> <span class="ls-keyword">String</span><br /><br /> <span class="ls-function">Call</span> NEMEnableWaitCursor<br /> <span class="ls-statement">Set</span> doc <span class="ls-operator">=</span> ws<span class="ls-operator">.</span>CurrentDocument<span class="ls-operator">.</span>Document<br /> <span class="ls-statement">Set</span> objConnection <span class="ls-operator">=</span> <span class="ls-function">CreateObject</span><span class="ls-operator">(</span><span class="ls-quote">"ADODB.Connection"</span><span class="ls-operator">)</span><br /> objConnection<span class="ls-operator">.</span><span class="ls-function">Open</span> <span class="ls-quote">"Provider=ADsDSOObject;"</span><br /><br /> <span class="ls-statement">Set</span> objCommand <span class="ls-operator">=</span> <span class="ls-function">CreateObject</span><span class="ls-operator">(</span><span class="ls-quote">"ADODB.Command"</span><span class="ls-operator">)</span><br /> <span class="ls-statement">Set</span> objCommand<span class="ls-operator">.</span>ActiveConnection <span class="ls-operator">=</span> objConnection<br /><br /> <span class="ls-statement">If</span> <span class="ls-function">Instr</span><span class="ls-operator">(</span>doc<span class="ls-operator">.</span>searchString<span class="ls-operator">(</span>0<span class="ls-operator">)</span><span class="ls-operator">,</span><span class="ls-quote">","</span><span class="ls-operator">)</span> <span class="ls-operator">=</span> 0 <span class="ls-statement">Then</span><br /> nameSearch <span class="ls-operator">=</span> <span class="ls-quote">"(sn="</span> <span class="ls-operator">&</span> doc<span class="ls-operator">.</span>searchString<span class="ls-operator">(</span>0<span class="ls-operator">)</span> <span class="ls-operator">&</span> <span class="ls-quote">"*));"</span><br /> <span class="ls-statement">Else</span><br /> nameSearch <span class="ls-operator">=</span> <span class="ls-quote">"(sn="</span> <span class="ls-operator">&</span> <span class="ls-function">Strleft</span><span class="ls-operator">(</span>doc<span class="ls-operator">.</span>searchString<span class="ls-operator">(</span>0<span class="ls-operator">)</span><span class="ls-operator">,</span><span class="ls-quote">","</span><span class="ls-operator">)</span> <span class="ls-operator">&_</span></div><div class="lotusscript"> <span class="ls-quote">"*)(givenName="</span> <span class="ls-operator">&</span> <span class="ls-function">Strright</span><span class="ls-operator">(</span>doc<span class="ls-operator">.</span>searchString<span class="ls-operator">(</span>0<span class="ls-operator">)</span><span class="ls-operator">,</span><span class="ls-quote">","</span><span class="ls-operator">)</span> <span class="ls-operator">&</span> <span class="ls-quote">"*));"</span><br /> <span class="ls-statement">End</span> <span class="ls-statement">If</span><br /><br /> objCommand<span class="ls-operator">.</span>CommandText <span class="ls-operator">=</span> <span class="ls-operator">_</span><br /> <span class="ls-quote">"<gc:>;"</span> <span class="ls-operator">&</span> <span class="ls-operator">_</span><br /> <span class="ls-quote">"(&(objectCategory=person)(objectClass=user)"</span> <span class="ls-operator">&</span> <span class="ls-operator">_</span><br /> nameSearch <span class="ls-operator">&</span> <span class="ls-operator">_</span><br /> <span class="ls-quote">"sAMAccountName, givenName, sn;subtree"</span><br /><br /> <span class="ls-statement">On</span> <span class="ls-function">Error</span> <span class="ls-keyword">Goto</span> LookupError<br /> <span class="ls-statement">Set</span> objRecordSet <span class="ls-operator">=</span> objCommand<span class="ls-operator">.</span><span class="ls-function">Execute</span><br /> <span class="ls-statement">On</span> <span class="ls-function">Error</span> <span class="ls-statement">Resume</span> <span class="ls-statement">Next</span><br /><br /> x <span class="ls-operator">=</span> 0<br /> <span class="ls-statement">Redim</span> <span class="ls-keyword">Preserve</span> NameList<span class="ls-operator">(</span>x<span class="ls-operator">)</span><br /> <span class="ls-statement">While</span> <span class="ls-operator">Not</span> objRecordset<span class="ls-operator">.</span><span class="ls-function">EOF</span><br /> ID <span class="ls-operator">=</span> objRecordset<span class="ls-operator">.</span>Fields<span class="ls-operator">(</span><span class="ls-quote">"sAMAccountName"</span><span class="ls-operator">)</span><span class="ls-operator">.</span>value<br /> <span class="ls-statement">Redim</span> <span class="ls-keyword">Preserve</span> NameList<span class="ls-operator">(</span>x<span class="ls-operator">)</span><br /> <span class="ls-statement">If</span> <span class="ls-function">Isnull</span><span class="ls-operator">(</span>objRecordset<span class="ls-operator">.</span>Fields<span class="ls-operator">(</span><span class="ls-quote">"sn"</span><span class="ls-operator">)</span><span class="ls-operator">.</span>Value<span class="ls-operator">)</span> <span class="ls-statement">Then</span><br /> LN <span class="ls-operator">=</span> <span class="ls-quote">""</span><br /> <span class="ls-statement">Else</span><br /> LN <span class="ls-operator">=</span> objRecordset<span class="ls-operator">.</span>Fields<span class="ls-operator">(</span><span class="ls-quote">"sn"</span><span class="ls-operator">)</span><span class="ls-operator">.</span>Value<br /> <span class="ls-statement">End</span> <span class="ls-statement">If</span><br /> <span class="ls-statement">If</span> <span class="ls-function">Isnull</span><span class="ls-operator">(</span>objRecordset<span class="ls-operator">.</span>Fields<span class="ls-operator">(</span><span class="ls-quote">"givenName"</span><span class="ls-operator">)</span><span class="ls-operator">.</span>Value<span class="ls-operator">)</span> <span class="ls-statement">Then</span><br /> FN <span class="ls-operator">=</span> <span class="ls-quote">""</span><br /> <span class="ls-statement">Else</span><br /> FN <span class="ls-operator">=</span> objRecordset<span class="ls-operator">.</span>Fields<span class="ls-operator">(</span><span class="ls-quote">"givenName"</span><span class="ls-operator">)</span><span class="ls-operator">.</span>Value<br /> <span class="ls-statement">End</span> <span class="ls-statement">If</span><br /> NameList<span class="ls-operator">(</span>x<span class="ls-operator">)</span> <span class="ls-operator">=</span> LN <span class="ls-operator">&</span> <span class="ls-quote">", "</span> <span class="ls-operator">&</span> FN <span class="ls-operator">&</span> nl <span class="ls-operator">&</span> <span class="ls-quote">" ("</span> <span class="ls-operator">&</span> ID <span class="ls-operator">&</span> <span class="ls-quote">")"</span><br /> <span class="ls-statement">If</span> x <span class="ls-operator">></span><span class="ls-operator">=</span> 1000 <span class="ls-statement">Then</span><br /> <span class="ls-function">Messagebox</span> <span class="ls-quote">"Your search produced a very large list of names so the list only "</span> <span class="ls-operator">&</span><span class="ls-operator">_</span><br /> <span class="ls-quote">"includes the first 1,000 found. If the name you are looking for is not in the list, " &_</span></div><div class="lotusscript"><span class="ls-quote"> "try searching again with more of the last name and/or first name."</span><span class="ls-operator">,</span>64<span class="ls-operator">,</span><span class="ls-quote">"Large Result"</span><br /> <span class="ls-keyword">Goto</span> DisplayIt<br /> <span class="ls-statement">End</span> <span class="ls-statement">If</span><br /> x <span class="ls-operator">=</span> x <span class="ls-operator">+</span>1<br /> <span class="ls-statement">End</span> <span class="ls-statement">If</span><br /> objRecordset<span class="ls-operator">.</span>MoveNext<br /> <span class="ls-statement">Wend</span><br /><br />DisplayIt<span class="ls-operator">:</span><br /> <span class="ls-function">Call</span> NEMDisableWaitCursor<br /> <span class="ls-statement">If</span> NameList<span class="ls-operator">(</span>0<span class="ls-operator">)</span> <span class="ls-operator">=</span> <span class="ls-quote">""</span> <span class="ls-statement">Then</span><br /> <span class="ls-function">Messagebox</span> <span class="ls-quote">"No users found meeting your criteria; please revise " &_</span></div><div class="lotusscript"><span class="ls-quote"> "and try again"</span><span class="ls-operator">,</span>64<span class="ls-operator">,</span><span class="ls-quote">"Name Lookup"</span><br /> <span class="ls-statement">Exit</span> <span class="ls-statement">Sub</span><br /> <span class="ls-statement">End</span> <span class="ls-statement">If</span><br /><br /> objConnection<span class="ls-operator">.</span><span class="ls-function">Close</span><br /><br /> doc<span class="ls-operator">.</span>searchResults <span class="ls-operator">=</span> sortValues<span class="ls-operator">(</span>NameList<span class="ls-operator">)</span><br /> <span class="ls-function">Call</span> ws<span class="ls-operator">.</span>CurrentDocument<span class="ls-operator">.</span>Reload<br /> <span class="ls-function">Call</span> ws<span class="ls-operator">.</span>CurrentDocument<span class="ls-operator">.</span>Refresh<br /> <span class="ls-statement">Exit</span> <span class="ls-statement">Sub</span><br /><br />LookupError<span class="ls-operator">:</span><br /> <span class="ls-function">Messagebox</span> <span class="ls-quote">"The search caused an error, most likely because you " &_</span></div><div class="lotusscript"><span class="ls-quote"> "are not on the network. Please try the lookup again later."</span><span class="ls-operator">,</span>48<span class="ls-operator">,</span><span class="ls-quote">"Error"</span><br /> <span class="ls-function">Call</span> NEMDisableWaitCursor<br /> objConnection<span class="ls-operator">.</span><span class="ls-function">Close</span><br /> <span class="ls-statement">End</span><br /><span class="ls-statement">End</span> <span class="ls-statement">Sub</span><br /></div></pre><br /><br /><div style="BORDER-RIGHT: 1px solid; BORDER-TOP: 1px solid; FONT-SIZE: 8pt; MARGIN: 5px 50px; BORDER-LEFT: 1px solid; COLOR: gray; BORDER-BOTTOM: 1px solid; FONT-FAMILY: sans-serif"><center>This LotusScript was converted to HTML using the <b><i>ls2html</i></b> routine,<br />provided by Julian Robichaux at <a href="http://www.nsftools.com" target="_blank">nsftools.com</a>.</center></div><br /><br />Technorati: <a href="http://technorati.com/tag/show-n-tell" rel="tag">Show-n-Tell Thursday</a>Don McNallyhttp://www.blogger.com/profile/17995406659448494074noreply@blogger.com0tag:blogger.com,1999:blog-24863326.post-52297813711370877782007-01-11T11:36:00.000-05:002023-05-24T14:12:16.602-04:00SnTT: Sorting a NotesDocumentCollection - Ascending *or* Descending<img src="http://webpages.charter.net/dtmc/images/SNTTbtn.png" align="right" />Finally I have time for another SNTT post! This one shows, I suppose, the power of the collaboration that goes on in the Notes/Domino community.<br /><br />I needed to be able to sort a collection of documents for my <a href="http://dmcnally.blogspot.com/2006/08/sntt-web-services-that-return-arrays.html">web service project</a> and was lucky enough to find pretty much the <a href="http://per.lausten.dk/blog/2006/10/sorting-notesdocumentcollection-by.html">exact code I needed</a> at Per Henrik Lausten's site. He had taken code from a couple of other guys and combined it together. The one thing it didn't do which I needed was to be able to sort in Ascending or Descending order.<br /><br />It was a pretty easy change to make. And here is the code. Note: I only include the sortCollection code because that is all I changed - you will need the sortValues code from Per Henrik's or <a href="http://www.joelitton.net">Joe Litton's</a> site for everything to work.<br /><style>.lotusscript { font-family: sans-serif; font-size: 9pt; color: black; }<br />.ls-comment { color: green; }<br />.ls-quote { color: black; }<br />.ls-datatype { color: black; }<br />.ls-operator { color: blue; }<br />.ls-keyword { color: blue; }<br />.ls-statement { color: blue; }<br />.ls-function { color: blue; }<br />.ls-class { color: black; }<br />.ls-constant { color: purple; }<br /></style><br /><br /><pre><div class="lotusscript"><br /><span class="ls-statement">Function</span> sortCollection<span class="ls-operator">(</span>coll <span class="ls-keyword">As</span> <span class="ls-class">NotesDocumentCollection</span><span class="ls-operator">,</span> fieldnames<span class="ls-operator">(</span><span class="ls-operator">)</span> <span class="ls-keyword">As</span> <span class="ls-keyword">String</span><span class="ls-operator">,</span> </div><div class="lotusscript">sortOrder <span class="ls-keyword">as</span> <span class="ls-keyword">String</span><span class="ls-operator">)</span> <span class="ls-keyword">As</span> <span class="ls-class">NotesDocumentCollection</span><br /><br /><span class="ls-comment">' Description:</span><br /><span class="ls-comment">' Sorts and returns a NotesDocumentCollection</span><br /><span class="ls-comment">' Fieldnames parameter is an array of strings</span><br /><span class="ls-comment">' with the field names to be sorted on</span><br /><span class="ls-comment">' </span><br /><span class="ls-comment">' Modified by Don McNally, December 2006 - http://dmcnally.blogspot.com/</span><br /><span class="ls-comment">' </span><br /><span class="ls-comment">' Based on code by:</span><br /><span class="ls-comment">' Max Flodén - </span></div><div class="lotusscript"><span class="ls-comment">http://www.tjitjing.com/blog/2006/05/how-to-sort-notesdocumentcollection-in.html</span><br /><span class="ls-comment">' Joe Litton - http://joelitton.net/A559B2/home.nsf/d6plinks/JLIN-5ZU3WH</span><br /><span class="ls-comment">' Peter von Stöckel - </span></div><div class="lotusscript"><span class="ls-comment">http://www.bananahome.com/users/bananahome/blog.nsf/d6plinks/PSTL-6UWC7K</span><br /><span class="ls-comment">' Per Henrik Lausten, November 2006 - </span></div><div class="lotusscript"><span class="ls-comment">http://per.lausten.dk/blog/2006/10/sorting-notesdocumentcollection-by.html </span><br /><span class="ls-comment">'</span><br /><span class="ls-comment">' Example of use</span><br /><span class="ls-comment">' Dim fieldnames(0 To 2) As String</span><br /><span class="ls-comment">' fieldnames(0) = "SKU"</span><br /><span class="ls-comment">' fieldnames(1) = "OrderDate"</span><br /><span class="ls-comment">' fieldnames(2) = "Client"</span><br /><span class="ls-comment">' Set collection = SortCollection (collection, fieldnames) </span><br /><br /> <span class="ls-statement">Dim</span> session <span class="ls-keyword">As</span> <span class="ls-keyword">New</span> <span class="ls-class">NotesSession</span><br /> <span class="ls-statement">Dim</span> db <span class="ls-keyword">As</span> <span class="ls-class">NotesDatabase</span><br /> <span class="ls-statement">Dim</span> collSorted <span class="ls-keyword">As</span> <span class="ls-class">NotesDocumentCollection</span><br /> <span class="ls-statement">Dim</span> doc <span class="ls-keyword">As</span> <span class="ls-class">NotesDocument</span><br /> <span class="ls-statement">Dim</span> i <span class="ls-keyword">As</span> <span class="ls-keyword">Integer</span><span class="ls-operator">,</span> n <span class="ls-keyword">As</span> <span class="ls-keyword">Integer</span><br /> <span class="ls-statement">Dim</span> arrFieldValueLength<span class="ls-operator">(</span><span class="ls-operator">)</span> <span class="ls-keyword">As</span> <span class="ls-keyword">Long</span><br /> <span class="ls-statement">Dim</span> arrSort<span class="ls-operator">,</span> strSort <span class="ls-keyword">As</span> <span class="ls-keyword">String</span><br /> <span class="ls-statement">Dim</span> loopCount <span class="ls-keyword">As</span> <span class="ls-keyword">Integer</span><span class="ls-operator">,</span> loopStart <span class="ls-keyword">As</span> <span class="ls-keyword">Integer</span><span class="ls-operator">,</span> loopEnd <span class="ls-keyword">As</span> <span class="ls-keyword">Integer</span><span class="ls-operator">,</span> loopStep <span class="ls-keyword">As</span> <span class="ls-keyword">Integer</span><br /><br /> <span class="ls-statement">Set</span> db <span class="ls-operator">=</span> session<span class="ls-operator">.</span>CurrentDatabase<br /><br /><span class="ls-comment">' ---</span><br /><span class="ls-comment">' --- 1) Build array to be sorted</span><br /><span class="ls-comment">' ---</span><br /><br /> <span class="ls-comment">'Fill array with fieldvalues and docid and get max field length</span><br /> <span class="ls-statement">Redim</span> arrSort<span class="ls-operator">(</span>0 <span class="ls-statement">To</span> coll<span class="ls-operator">.</span>Count <span class="ls-operator">-</span>1<span class="ls-operator">,</span> 0 <span class="ls-statement">To</span> <span class="ls-function">Ubound</span><span class="ls-operator">(</span>fieldnames<span class="ls-operator">)</span> <span class="ls-operator">+</span> 1<span class="ls-operator">)</span><br /> <span class="ls-statement">Redim</span> arrFieldValueLength<span class="ls-operator">(</span>0 <span class="ls-statement">To</span> <span class="ls-function">Ubound</span><span class="ls-operator">(</span>fieldnames<span class="ls-operator">)</span> <span class="ls-operator">+</span> 1<span class="ls-operator">)</span><br /> <span class="ls-statement">For</span> i <span class="ls-operator">=</span> 0 <span class="ls-statement">To</span> coll<span class="ls-operator">.</span>Count <span class="ls-operator">-</span> 1<br /> <span class="ls-statement">Set</span> doc <span class="ls-operator">=</span> coll<span class="ls-operator">.</span>GetNthDocument<span class="ls-operator">(</span>i <span class="ls-operator">+</span> 1<span class="ls-operator">)</span><br /> <span class="ls-statement">For</span> n <span class="ls-operator">=</span> 0 <span class="ls-statement">To</span> <span class="ls-function">Ubound</span><span class="ls-operator">(</span>fieldnames<span class="ls-operator">)</span> <span class="ls-operator">+</span> 1<br /> <br /> <span class="ls-statement">If</span> n <span class="ls-operator">=</span> <span class="ls-function">Ubound</span><span class="ls-operator">(</span>fieldnames<span class="ls-operator">)</span> <span class="ls-operator">+</span> 1 <span class="ls-statement">Then</span><br /> arrSort<span class="ls-operator">(</span>i<span class="ls-operator">,</span>n<span class="ls-operator">)</span> <span class="ls-operator">=</span> doc<span class="ls-operator">.</span>UniversalID<br /> arrFieldValueLength<span class="ls-operator">(</span>n<span class="ls-operator">)</span> <span class="ls-operator">=</span> 32<br /> <span class="ls-statement">Else</span><br /> arrSort<span class="ls-operator">(</span>i<span class="ls-operator">,</span>n<span class="ls-operator">)</span> <span class="ls-operator">=</span> <span class="ls-quote">""</span> <span class="ls-operator">&</span> doc<span class="ls-operator">.</span>GetItemValue<span class="ls-operator">(</span>fieldnames<span class="ls-operator">(</span>n<span class="ls-operator">)</span><span class="ls-operator">)</span><span class="ls-operator">(</span>0<span class="ls-operator">)</span><br /> <span class="ls-comment">' Check length of field value</span><br /> <span class="ls-statement">If</span> <span class="ls-keyword">Len</span><span class="ls-operator">(</span>arrSort<span class="ls-operator">(</span>i<span class="ls-operator">,</span>n<span class="ls-operator">)</span><span class="ls-operator">)</span> <span class="ls-operator">></span> arrFieldValueLength<span class="ls-operator">(</span>n<span class="ls-operator">)</span> <span class="ls-statement">Then</span><br /> arrFieldValueLength<span class="ls-operator">(</span>n<span class="ls-operator">)</span> <span class="ls-operator">=</span> <span class="ls-keyword">Len</span><span class="ls-operator">(</span>arrSort<span class="ls-operator">(</span>i<span class="ls-operator">,</span>n<span class="ls-operator">)</span><span class="ls-operator">)</span><br /> <span class="ls-statement">End</span> <span class="ls-statement">If</span><br /> <span class="ls-statement">End</span> <span class="ls-statement">If</span><br /> <br /> <span class="ls-statement">Next</span> n<br /> <span class="ls-statement">Next</span> i<br /><br /> <span class="ls-comment">'Merge fields into array that can be used for sorting using the sortValues function</span><br /> <span class="ls-statement">Dim</span> aryFieldValues<span class="ls-operator">(</span><span class="ls-operator">)</span> <span class="ls-keyword">As</span> <span class="ls-keyword">String</span><br /> <span class="ls-statement">For</span> i <span class="ls-operator">=</span> 0 <span class="ls-statement">To</span> coll<span class="ls-operator">.</span>Count <span class="ls-operator">-</span> 1 <br /> <span class="ls-statement">Redim</span> <span class="ls-keyword">Preserve</span> aryFieldValues<span class="ls-operator">(</span>1 <span class="ls-statement">To</span> i<span class="ls-operator">+</span>1<span class="ls-operator">)</span><br /> <br /> strSort <span class="ls-operator">=</span> <span class="ls-quote">""</span><br /> <span class="ls-statement">For</span> n <span class="ls-operator">=</span> <span class="ls-function">Lbound</span><span class="ls-operator">(</span>fieldnames<span class="ls-operator">)</span> <span class="ls-statement">To</span> <span class="ls-function">Ubound</span><span class="ls-operator">(</span>fieldnames<span class="ls-operator">)</span> <span class="ls-operator">+</span> 1<br /> strSort <span class="ls-operator">=</span> strSort <span class="ls-operator">&</span> <span class="ls-function">Left</span><span class="ls-operator">(</span>arrSort<span class="ls-operator">(</span>i<span class="ls-operator">,</span>n<span class="ls-operator">)</span> <span class="ls-operator">&</span> <span class="ls-function">Space</span><span class="ls-operator">(</span>arrFieldValueLength<span class="ls-operator">(</span>n<span class="ls-operator">)</span><span class="ls-operator">)</span><span class="ls-operator">,</span> arrFieldValueLength<span class="ls-operator">(</span>n<span class="ls-operator">)</span><span class="ls-operator">)</span><br /> <span class="ls-statement">Next</span> n<br /> <br /> aryFieldValues<span class="ls-operator">(</span>i<span class="ls-operator">+</span>1<span class="ls-operator">)</span> <span class="ls-operator">=</span> strSort<br /> <span class="ls-statement">Next</span> i<br /><br /><br /><span class="ls-comment">' ---</span><br /><span class="ls-comment">' --- 2) Sort array using sortValues function by Joe Litton</span><br /><span class="ls-comment">' ---</span><br /> arrSort <span class="ls-operator">=</span> sortValues<span class="ls-operator">(</span>aryFieldValues<span class="ls-operator">)</span><br /><br /><span class="ls-comment">' ---</span><br /><span class="ls-comment">' --- 3) Use sorted array to sort collection</span><br /><span class="ls-comment">' ---</span><br /> <span class="ls-statement">Set</span> collSorted <span class="ls-operator">=</span> db<span class="ls-operator">.</span>GetProfileDocCollection<span class="ls-operator">(</span><span class="ls-quote">"Foo"</span><span class="ls-operator">)</span> <span class="ls-comment">' create an empty NotesDocumentCollection</span><br /> <span class="ls-statement">If</span> <span class="ls-function">ucase</span><span class="ls-operator">(</span>sortOrder<span class="ls-operator">)</span> <span class="ls-operator">=</span> <span class="ls-quote">"D"</span> <span class="ls-statement">Then</span><br /> loopStart <span class="ls-operator">=</span> <span class="ls-function">Ubound</span><span class="ls-operator">(</span>arrSort<span class="ls-operator">)</span><br /> loopEnd <span class="ls-operator">=</span> 1<br /> loopStep <span class="ls-operator">=</span> <span class="ls-operator">-</span>1<br /> <span class="ls-statement">Else</span><br /> loopStart <span class="ls-operator">=</span> 1<br /> loopEnd <span class="ls-operator">=</span> <span class="ls-function">Ubound</span><span class="ls-operator">(</span>arrSort<span class="ls-operator">)</span><br /> loopStep <span class="ls-operator">=</span> 1<br /> <span class="ls-statement">End</span> <span class="ls-statement">If</span><br /> <span class="ls-statement">For</span> loopCount <span class="ls-operator">=</span> loopStart <span class="ls-statement">To</span> loopEnd <span class="ls-statement">Step</span> loopStep<br /> <span class="ls-statement">Set</span> doc <span class="ls-operator">=</span> db<span class="ls-operator">.</span>GetDocumentByUNID<span class="ls-operator">(</span><span class="ls-function">Right</span><span class="ls-operator">(</span>arrSort<span class="ls-operator">(</span>loopCount<span class="ls-operator">)</span><span class="ls-operator">,</span> 32<span class="ls-operator">)</span><span class="ls-operator">)</span><br /> <span class="ls-function">Call</span> collSorted<span class="ls-operator">.</span>AddDocument<span class="ls-operator">(</span>doc<span class="ls-operator">)</span><br /> <span class="ls-statement">Next</span><br /><br /><span class="ls-comment">' ---</span><br /><span class="ls-comment">' --- 4) Return collection</span><br /><span class="ls-comment">' ---</span><br /> <span class="ls-statement">Set</span> SortCollection <span class="ls-operator">=</span> collSorted<br /><br /><span class="ls-statement">End</span> <span class="ls-statement">Function</span><br /></div></pre><br /><br /><br /><div style="BORDER-RIGHT: 1px solid; BORDER-TOP: 1px solid; FONT-SIZE: 8pt; MARGIN: 5px 50px; BORDER-LEFT: 1px solid; COLOR: gray; BORDER-BOTTOM: 1px solid; FONT-FAMILY: sans-serif"><center>This LotusScript was converted to HTML using the <b><i>ls2html</i></b> routine,<br />provided by Julian Robichaux at <a href="http://www.nsftools.com" target="_blank">nsftools.com</a>.</center></div><br />I had some thoughts about extending the code even further to allow different sort orders for the different fields (like Ascending on the first field and Descending on the second) but didn't end up needing it for my project. Hope you find this useful.<br /><br />Technorati: <a href="http://technorati.com/tag/show-n-tell" rel="tag">Show-n-Tell Thursday</a>Don McNallyhttp://www.blogger.com/profile/17995406659448494074noreply@blogger.com7tag:blogger.com,1999:blog-24863326.post-1156529881736821702006-08-25T13:49:00.000-04:002006-08-30T16:19:59.756-04:00SnTT: Web Services that Return Arrays<img src="http://webpages.charter.net/dtmc/images/SNTTbtn.png" align="right" />Be prepared: this is a longer post than usual. I wrote my first web service the other day. I was pretty excited the first time it actually worked (if you use Visual Studio to develop, always remember to save your work before you try to test your code!). So, aside from not saving my code before testing, the thing that slowed me down the most was figuring out how to handle the data that was coming back from the web service. I was sending back an array of a user-defined class and while I found a couple of <a href="http://www-128.ibm.com/developerworks/lotus/library/nd7-webservices/">good</a> <a href="http://www6.software.ibm.com/dw/education/ls/lsdomino7ws">articles (IBM ID required on this one)</a> on writing the web service so it would return what I wanted, I couldn't find anything about how to handle it in the calling program. But now that I have figured it out, I thought I'd share with the class.<br /><br />This is a scaled down version of my web service code:<br /><style>.lotusscript { font-family: sans-serif; font-size: 9pt; color: black; }<br />.ls-comment { color: green; }<br />.ls-quote { color: black; }<br />.ls-datatype { color: black; }<br />.ls-operator { color: blue; }<br />.ls-keyword { color: blue; }<br />.ls-statement { color: blue; }<br />.ls-function { color: blue; }<br />.ls-class { color: black; }<br />.ls-constant { color: purple; }<br /></style><br /><pre><div class="lotusscript"><br /><span class="ls-keyword">Public</span> <span class="ls-statement">Class</span> OneIssue<br /> <span class="ls-keyword">Public</span> IssueNumber <span class="ls-keyword">As</span> <span class="ls-keyword">String</span><br /> <span class="ls-keyword">Public</span> IssueTitle <span class="ls-keyword">As</span> <span class="ls-keyword">String</span><br /> <span class="ls-keyword">Public</span> IssueDesc <span class="ls-keyword">As</span> <span class="ls-keyword">String</span><br /> <span class="ls-keyword">Public</span> IssueStatus <span class="ls-keyword">As</span> <span class="ls-keyword">String</span><br /> <span class="ls-keyword">Public</span> IssueTargetDate <span class="ls-keyword">As</span> <span class="ls-keyword">String</span><br /> <br /> <span class="ls-statement">Sub</span> <span class="ls-keyword">New</span><br /> <br /> <span class="ls-statement">End</span> <span class="ls-statement">Sub</span><br /> <br /> <span class="ls-statement">Sub</span> ReadValues<span class="ls-operator">(</span>doc <span class="ls-keyword">As</span> <span class="ls-class">NotesDocument</span><span class="ls-operator">)</span><br /> IssueNumber <span class="ls-operator">=</span> doc<span class="ls-operator">.</span>IssueNumber<span class="ls-operator">(</span>0<span class="ls-operator">)</span><br /> IssueTitle <span class="ls-operator">=</span> doc<span class="ls-operator">.</span>IssueTitle<span class="ls-operator">(</span>0<span class="ls-operator">)</span><br /> IssueDesc <span class="ls-operator">=</span> doc<span class="ls-operator">.</span>IssueDesc<span class="ls-operator">(</span>0<span class="ls-operator">)</span><br /> IssueStatus <span class="ls-operator">=</span> doc<span class="ls-operator">.</span>TrackingDisposition<span class="ls-operator">(</span>0<span class="ls-operator">)</span><br /> IssueTargetDate <span class="ls-operator">=</span> doc<span class="ls-operator">.</span>TargetCompletion<span class="ls-operator">(</span>0<span class="ls-operator">)</span><br /> <span class="ls-statement">End</span> <span class="ls-statement">Sub</span><br /><span class="ls-statement">End</span> <span class="ls-statement">Class</span><br /><br /><span class="ls-keyword">Public</span> <span class="ls-statement">Class</span> IssueArray<br /> <span class="ls-keyword">Public</span> MultipleIssues<span class="ls-operator">(</span><span class="ls-operator">)</span> <span class="ls-keyword">As</span> OneIssue<br /><span class="ls-statement">End</span> <span class="ls-statement">Class</span><br /><br /><span class="ls-keyword">Public</span> <span class="ls-statement">Class</span> LOBReport<br /> <span class="ls-keyword">Private</span> session <span class="ls-keyword">As</span> <span class="ls-class">NotesSession</span><br /> <span class="ls-keyword">Private</span> db <span class="ls-keyword">As</span> <span class="ls-class">NotesDatabase</span><br /> <br /> <span class="ls-statement">Sub</span> <span class="ls-keyword">New</span><br /> <span class="ls-statement">Set</span> session <span class="ls-operator">=</span> <span class="ls-keyword">New</span> <span class="ls-class">NotesSession</span><br /> <span class="ls-statement">Set</span> db <span class="ls-operator">=</span> session<span class="ls-operator">.</span>CurrentDatabase<br /> <span class="ls-statement">End</span> <span class="ls-statement">Sub</span><br /> <br /> <span class="ls-keyword">Public</span> <span class="ls-statement">Function</span> GetIssues<span class="ls-operator">(</span>OrgUnit <span class="ls-keyword">As</span> <span class="ls-keyword">String</span><span class="ls-operator">)</span> <span class="ls-keyword">As</span> IssueArray<br /> <span class="ls-statement">Dim</span> dc <span class="ls-keyword">As</span> <span class="ls-class">NotesDocumentCollection</span><br /> <span class="ls-statement">Dim</span> docIssue <span class="ls-keyword">As</span> <span class="ls-class">NotesDocument</span><br /> <span class="ls-statement">Dim</span> view <span class="ls-keyword">As</span> <span class="ls-class">NotesView</span><br /> <span class="ls-statement">Dim</span> x <span class="ls-keyword">As</span> <span class="ls-keyword">Integer</span><br /> <br /> <span class="ls-statement">Set</span> GetIssues <span class="ls-operator">=</span> <span class="ls-keyword">New</span> IssueArray<br /> <span class="ls-statement">Set</span> view <span class="ls-operator">=</span> db<span class="ls-operator">.</span>GetView<span class="ls-operator">(</span><span class="ls-quote">"ByBusUnit"</span><span class="ls-operator">)</span><br /> <span class="ls-statement">Set</span> dc <span class="ls-operator">=</span> view<span class="ls-operator">.</span>GetAllDocumentsByKey<span class="ls-operator">(</span>OrgUnit<span class="ls-operator">)</span><br /> <span class="ls-statement">If</span> <span class="ls-operator">Not</span> dc <span class="ls-operator">Is</span> <span class="ls-constant">Nothing</span> <span class="ls-statement">Then</span><br /> x <span class="ls-operator">=</span> 0<br /> <span class="ls-statement">Set</span> docIssue <span class="ls-operator">=</span> dc<span class="ls-operator">.</span>GetFirstDocument<br /> <span class="ls-statement">While</span> <span class="ls-operator">Not</span> docIssue <span class="ls-operator">Is</span> <span class="ls-constant">Nothing</span><br /> <span class="ls-statement">Redim</span> <span class="ls-keyword">Preserve</span> GetIssues<span class="ls-operator">.</span>MultipleIssues<span class="ls-operator">(</span>x<span class="ls-operator">)</span><br /> <span class="ls-statement">Set</span> GetIssues<span class="ls-operator">.</span>MultipleIssues<span class="ls-operator">(</span>x<span class="ls-operator">)</span> <span class="ls-operator">=</span> <span class="ls-keyword">New</span> OneIssue<br /> GetIssues<span class="ls-operator">.</span>MultipleIssues<span class="ls-operator">(</span>x<span class="ls-operator">)</span><span class="ls-operator">.</span>ReadValues<span class="ls-operator">(</span>docIssue<span class="ls-operator">)</span><br /> x <span class="ls-operator">=</span> x <span class="ls-operator">+</span> 1<br /> <span class="ls-statement">Set</span> docIssue <span class="ls-operator">=</span> dc<span class="ls-operator">.</span>GetNextDocument<span class="ls-operator">(</span>docIssue<span class="ls-operator">)</span><br /> <span class="ls-statement">Wend</span><br /> <span class="ls-statement">End</span> <span class="ls-statement">If</span><br /> <span class="ls-statement">End</span> <span class="ls-statement">Function</span><br /><span class="ls-statement">End</span> <span class="ls-statement">Class</span><br /></div></pre><br /><br /><div style="BORDER-RIGHT: 1px solid; BORDER-TOP: 1px solid; FONT-SIZE: 8pt; MARGIN: 5px 50px; BORDER-LEFT: 1px solid; COLOR: gray; BORDER-BOTTOM: 1px solid; FONT-FAMILY: sans-serif"><center>This LotusScript was converted to HTML using the <b><i>ls2html</i></b> routine,<br />provided by Julian Robichaux at <a href="http://www.nsftools.com" target="_blank">nsftools.com</a>.</center></div><br /><br />Notice the classes at the top that define a single record of the class and the array of the first class. So what do you do with that in your calling program? My front end is a "proof of concept" at this point so I used Visual Studio.net to create a small Windows program. I won't get into the details of setting up your VS.net project and referencing the web service, but this is the code that calls the web service and handles the returned value:<br /><style>.lotusscript { font-family: sans-serif; font-size: 9pt; color: black; }<br />.ls-comment { color: green; }<br />.ls-quote { color: black; }<br />.ls-datatype { color: black; }<br />.ls-operator { color: blue; }<br />.ls-keyword { color: blue; }<br />.ls-statement { color: blue; }<br />.ls-function { color: blue; }<br />.ls-class { color: black; }<br />.ls-constant { color: purple; }<br /></style><br /><pre><div class="lotusscript"><br /> <span class="ls-statement">Dim</span> LOBReport <span class="ls-keyword">As</span> <span class="ls-keyword">New</span> webserverreference<span class="ls-operator">.</span>LOBReportService<br /> <span class="ls-statement">Dim</span> retval <span class="ls-keyword">As</span> <span class="ls-keyword">New</span> webserverreference<span class="ls-operator">.</span>ISSUEARRAY<br /> <span class="ls-statement">Dim</span> x <span class="ls-keyword">As</span> <span class="ls-keyword">Integer</span><br /> <span class="ls-statement">Dim</span> y <span class="ls-keyword">As</span> webserverreference<span class="ls-operator">.</span>ONEISSUE<br /> <span class="ls-statement">Dim</span> z <span class="ls-keyword">As</span> <span class="ls-keyword">Integer</span><br /> <span class="ls-statement">Dim</span> strings<span class="ls-operator">(</span>0<span class="ls-operator">)</span> <span class="ls-keyword">As</span> <span class="ls-keyword">String</span><br /><br /> TextBox1<span class="ls-operator">.</span>Clear<span class="ls-operator">(</span><span class="ls-operator">)</span><br /> z <span class="ls-operator">=</span> 0<br /> retval <span class="ls-operator">=</span> LOBReport<span class="ls-operator">.</span>GETISSUES<span class="ls-operator">(</span>ComboBox1<span class="ls-operator">.</span><span class="ls-constant">Text</span><span class="ls-operator">)</span><br /> <span class="ls-statement">If</span> <span class="ls-operator">Not</span> retval<span class="ls-operator">.</span>MULTIPLEISSUES <span class="ls-operator">Is</span> <span class="ls-constant">Nothing</span> <span class="ls-statement">Then</span><br /> <span class="ls-statement">For</span> x <span class="ls-operator">=</span> 0 <span class="ls-statement">To</span> <span class="ls-function">UBound</span><span class="ls-operator">(</span>retval<span class="ls-operator">.</span>MULTIPLEISSUES<span class="ls-operator">)</span><br /> y <span class="ls-operator">=</span> retval<span class="ls-operator">.</span>MULTIPLEISSUES<span class="ls-operator">(</span>x<span class="ls-operator">)</span><br /> <span class="ls-statement">ReDim</span> <span class="ls-keyword">Preserve</span> strings<span class="ls-operator">(</span><span class="ls-function">UBound</span><span class="ls-operator">(</span>strings<span class="ls-operator">)</span> <span class="ls-operator">+</span> 6<span class="ls-operator">)</span><br /> strings<span class="ls-operator">(</span>z<span class="ls-operator">)</span> <span class="ls-operator">=</span> <span class="ls-quote">"Issue Number: "</span> <span class="ls-operator">+</span> y<span class="ls-operator">.</span>ISSUENUMBER<br /> strings<span class="ls-operator">(</span>z <span class="ls-operator">+</span> 1<span class="ls-operator">)</span> <span class="ls-operator">=</span> <span class="ls-quote">"Issue Title: "</span> <span class="ls-operator">+</span> y<span class="ls-operator">.</span>ISSUETITLE<br /> strings<span class="ls-operator">(</span>z <span class="ls-operator">+</span> 2<span class="ls-operator">)</span> <span class="ls-operator">=</span> <span class="ls-quote">"Issue Description: "</span> <span class="ls-operator">+</span> y<span class="ls-operator">.</span>ISSUEDESC<br /> strings<span class="ls-operator">(</span>z <span class="ls-operator">+</span> 3<span class="ls-operator">)</span> <span class="ls-operator">=</span> <span class="ls-quote">"Issue Status (LOB): "</span> <span class="ls-operator">+</span> y<span class="ls-operator">.</span>ISSUESTATUS<br /> strings<span class="ls-operator">(</span>z <span class="ls-operator">+</span> 4<span class="ls-operator">)</span> <span class="ls-operator">=</span> <span class="ls-quote">"Issue Target Completion: "</span> <span class="ls-operator">+</span> y<span class="ls-operator">.</span>ISSUETARGETDATE<br /> strings<span class="ls-operator">(</span>z <span class="ls-operator">+</span> 5<span class="ls-operator">)</span> <span class="ls-operator">=</span> <span class="ls-quote">""</span><br /> z <span class="ls-operator">=</span> z <span class="ls-operator">+</span> 6<br /> <span class="ls-statement">Next</span><br /> TextBox1<span class="ls-operator">.</span>Lines <span class="ls-operator">=</span> strings<br /> <span class="ls-statement">Else</span><br /> TextBox1<span class="ls-operator">.</span><span class="ls-constant">Text</span> <span class="ls-operator">=</span> <span class="ls-quote">"No records found, or user not authorized for business unit"</span><br /> <span class="ls-statement">End</span> <span class="ls-statement">If</span><br /> <span class="ls-statement">End</span> <span class="ls-statement">Sub</span><br /></div></pre><br /><br />At the top, I define objects of the type defined in the classes in my web service, both the single item class and the array class. Then I loop through the array and process each item in it as an instance of my single item class.<br /><br />Maybe the problems I had were because I haven't used Visual Basic much, but I hope putting this out here saves someone else some time.<br /><br />Technorati: <a href="http://technorati.com/tag/show-n-tell thursday" rel="tag">Show-n-Tell Thursday</a><br />Categories: <em>Show-n-Tell Thursday_</em>Don McNallyhttp://www.blogger.com/profile/17995406659448494074noreply@blogger.com0tag:blogger.com,1999:blog-24863326.post-1150985668340984222006-06-22T09:36:00.000-04:002006-06-28T15:25:50.710-04:00SnTT: JDBC in Notes<img src="http://webpages.charter.net/dtmc/images/SNTTbtn.png" align="right" />I just finished with my first use of JDBC in a Notes application to access DB2 and thought I'd share. Initially, I looked at ODBC, but that required setting up a connection in the ODBC Data Sources on the client machine and I really wanted something that would "just work" without having to do anything to the client machines. I had seen a while back some code to automatically create that Data Source but was never able to get it to work in our environment.<br /><br />First is the Java agent that makes the connection and performs the query in DB2.<br /><br /><style>.lotusscript { font-family: sans-serif; font-size: 9pt; color: black; }<br />.ls-comment { color: green; }<br />.ls-quote { color: black; }<br />.ls-datatype { color: black; }<br />.ls-operator { color: blue; }<br />.ls-keyword { color: blue; }<br />.ls-statement { color: blue; }<br />.ls-function { color: blue; }<br />.ls-class { color: black; }<br />.ls-constant { color: fuchsia; }<br /></style><br /><pre><div class="lotusscript"><br /><span class="ls-operator">import</span> lotus.domino.*;<br /><span class="ls-operator">import</span> java.sql.*;<br /><span class="ls-operator">import</span> java.util.*;<br /><span class="ls-operator">import</span> javax.swing.JOptionPane;<br /><br /><span class="ls-keyword">public</span> <span class="ls-statement">class</span> DB2Lookup extends AgentBase <span class="ls-quote">{<br /><br /> <span class="ls-keyword">private static</span> String dbuser = <span class="ls-constant">"userid"</span>;<br /> <span class="ls-keyword">private static</span> String dbpwd = <span class="ls-constant">"pswd"</span>;<br /> <span class="ls-keyword">private static</span> String _url = <span class="ls-constant">"jdbc:db2://fully.qualified.server:db2portid/dbname"</span>;<br /> <span class="ls-keyword">private static</span> String _drv = <span class="ls-constant">"com.ibm.db2.jcc.DB2Driver"</span>;<br /> <span class="ls-keyword">private</span> java.sql.ResultSet rs;<br /> <span class="ls-keyword">private</span> java.sql.Statement stmt;<br /> <span class="ls-keyword">private</span> java.sql.Connection con;<br /><br /> <span class="ls-keyword">public</span> String[] NotesMain(String tableID, String custNum, String custName) {<br /> Vector vec = <span class="ls-keyword">new</span> Vector();<br /> <span class="ls-keyword">try</span> {<br /> Class.forName(_drv);<br /> <span class="ls-keyword">Properties</span> p = <span class="ls-keyword">new</span> Properties();<br /> p.put(<span class="ls-constant">"user"</span>, dbuser);<br /> p.put(<span class="ls-constant">"password"</span>, dbpwd);<br /> con = DriverManager.getConnection(_url, p);<br /> stmt = con.createStatement();<br /> rs = stmt.executeQuery(<span class="ls-constant">"SELECT DISTINCT NAME,CUSTNUM FROM SCHEMA."</span> + <br /> tableID + <span class="ls-constant">" WHERE CUSTNUM='"</span> + custNum + <br /> <span class="ls-constant">"' AND UPPER(NAME) LIKE('%"</span> + custName.toUpperCase() +<br /> <span class="ls-constant">"%') ORDER BY NAME")</span>;<br /> <span class="ls-keyword">while</span> (rs.next()) {<br /> String sN = rs.getString(<span class="ls-constant">"NAME"</span>);<br /> String sO = rs.getString(<span class="ls-constant">"OBLIGOR"</span>);<br /> vec.add(sN + <span class="ls-constant">""</span> + sO);<br /> }</span><br /> <br /> } <span class="ls-keyword">catch</span> (SQLException se) {<br /> <span class="ls-comment">// Inform user of any SQL errors</span><br /> System.out.println(<span class="ls-constant">"SQL Exception: "</span> + se.getMessage());<br /> se.printStackTrace(System.out);<br /><br /> } <span class="ls-keyword">catch</span> (Exception e) {<br /> e.printStackTrace();<br /><br /> } <span class="ls-keyword">finally</span> {<br /> <span class="ls-keyword">try</span> {<br /> rs.close();<br /> stmt.close();<br /> con.close();<br /> } <span class="ls-keyword">catch</span> (SQLException se) {<br /> System.out.println(<span class="ls-constant">"SQL Exception: "</span> + se.getMessage());<br /> se.printStackTrace(System.out);<br /> }<br /> }<br /> <span class="ls-statement">if</span> (vec.isEmpty()) {<br /> vec.add(<span class="ls-constant">"NOTHING"</span>);<br /> }<br /> String forms[] = (String []) vec.toArray( <span class="ls-keyword">new</span> String[vec.size()]);<br /> <span class="ls-statement">return</span> forms;<br /> }<br />}<br /></div></pre><br /><br />The agent takes parameters passed to it, executes the DB2 query, processes the result set and creates a string array to return to the caller. The biggest stumbling block I hit was handling queries that returned no records. Until I hit on adding the "NOTHING" entry if the vector came back empty, I kept crashing my Notes client. So beware!<br /><br />IBM has a JDBC Type 4 driver available for free re-distribution as part of your application. You can find it <a href="http://www14.software.ibm.com/webapp/download/preconfig.jsp?id=2004-09-20+10:09:21.003415R&cat=database&fam=&s=c&S_TACT=105AGX11&S_CMP=DB2">here</a> (IBM registration required, and this is for DB2 UDB, Cloudscape and Derby. <strong>UPDATE:</strong> The link points to the version is for Linux/Unix/Windows; not sure if there are drivers for other OSes). The db2jcc.jar and db2jcc_license_cu.jar archives need to be attached to your Java agent (use the Edit Project button to do this).<br /><br />Here is the LotusScript agent that calls the Java agent.<br /><br /><style>.lotusscript { font-family: sans-serif; font-size: 9pt; color: black; }<br />.ls-comment { color: green; }<br />.ls-quote { color: black; }<br />.ls-datatype { color: black; }<br />.ls-operator { color: blue; }<br />.ls-keyword { color: blue; }<br />.ls-statement { color: blue; }<br />.ls-function { color: blue; }<br />.ls-class { color: black; }<br />.ls-constant { color: purple; }<br /></style><br /><pre><div class="lotusscript"><br /><span class="ls-comment">' Declarations</span><br /><span class="ls-keyword">Option</span> <span class="ls-keyword">Public</span><br /><span class="ls-keyword">Option</span> <span class="ls-keyword">Declare</span><br /><span class="ls-statement">Use</span> <span class="ls-quote">"DB2Lookup"</span><br /><span class="ls-statement">Uselsx</span> <span class="ls-quote">"*javacon"</span><br /><br /><span class="ls-statement">Sub</span> <span class="ls-keyword">Initialize</span><br /> <span class="ls-statement">Dim</span> docThis <span class="ls-keyword">As</span> <span class="ls-class">NotesDocument</span><br /> <span class="ls-statement">Dim</span> myClass <span class="ls-keyword">As</span> <span class="ls-class">JavaClass</span><br /> <span class="ls-statement">Dim</span> myObject <span class="ls-keyword">As</span> <span class="ls-class">JavaObject</span><br /> <span class="ls-statement">Dim</span> mySession <span class="ls-keyword">As</span> <span class="ls-class">JavaSession</span><br /> <span class="ls-statement">Dim</span> ws <span class="ls-keyword">As</span> <span class="ls-class">NotesUIWorkspace</span><br /> <span class="ls-statement">Dim</span> IDs <span class="ls-keyword">As</span> <span class="ls-keyword">Variant</span><br /> <span class="ls-statement">Dim</span> stat <span class="ls-keyword">As</span> <span class="ls-keyword">Variant</span><br /><br /> <span class="ls-statement">Set</span> ws <span class="ls-operator">=</span> <span class="ls-keyword">New</span> <span class="ls-class">NotesUIWorkspace</span><br /> <span class="ls-statement">Set</span> docThis <span class="ls-operator">=</span> ws<span class="ls-operator">.</span>CurrentDocument<span class="ls-operator">.</span>Document<br /> <span class="ls-statement">If</span> docThis<span class="ls-operator">.</span>CustName<span class="ls-operator">(</span>0<span class="ls-operator">)</span> <span class="ls-operator">=</span> <span class="ls-quote">""</span> <span class="ls-operator">Or</span> docThis<span class="ls-operator">.</span>CustNum<span class="ls-operator">(</span>0<span class="ls-operator">)</span> <span class="ls-operator">=</span> <span class="ls-quote">""</span> <span class="ls-operator">Or</span> docThis<span class="ls-operator">.</span>TableID<span class="ls-operator">(</span>0<span class="ls-operator">)</span> <span class="ls-operator">=</span> <span class="ls-quote">""</span> <span class="ls-statement">Then</span><br /> <span class="ls-function">Messagebox</span> <span class="ls-quote">"You must have entries in the Customer Name, Customer Number<br />and Table fields before using this function"</span><span class="ls-operator">,</span>16<span class="ls-operator">,</span><span class="ls-quote">"Error"</span><br /> <span class="ls-statement">Exit</span> <span class="ls-statement">Sub</span><br /> <span class="ls-statement">End</span> <span class="ls-statement">If</span><br /><br /> <span class="ls-statement">Set</span> mySession <span class="ls-operator">=</span> <span class="ls-keyword">New</span> <span class="ls-class">JavaSession</span><span class="ls-operator">(</span><span class="ls-operator">)</span><br /> <span class="ls-statement">Set</span> myClass <span class="ls-operator">=</span> mySession<span class="ls-operator">.</span>GetClass<span class="ls-operator">(</span><span class="ls-quote">"DB2Lookup"</span><span class="ls-operator">)</span><br /> <span class="ls-statement">Set</span> myObject <span class="ls-operator">=</span> myClass<span class="ls-operator">.</span><span class="ls-function">CreateObject</span><br /> IDs <span class="ls-operator">=</span> myObject<span class="ls-operator">.</span>NotesMain<span class="ls-operator">(</span>docThis<span class="ls-operator">.</span>TableID<span class="ls-operator">(</span>0<span class="ls-operator">)</span><span class="ls-operator">,</span>docThis<span class="ls-operator">.</span>CustNum<span class="ls-operator">(</span>0<span class="ls-operator">)</span><span class="ls-operator">,</span>docThis<span class="ls-operator">.</span>Borrower<span class="ls-operator">(</span>0<span class="ls-operator">)</span><span class="ls-operator">)</span><br /><br /> <span class="ls-statement">If</span> IDs<span class="ls-operator">(</span>0<span class="ls-operator">)</span> <span class="ls-operator">=</span> <span class="ls-quote">"NOTHING"</span> <span class="ls-statement">Then</span><br /> <span class="ls-function">Messagebox</span> <span class="ls-quote">"No customers found meeting your criteria; please revise and try again"</span><span class="ls-operator">,</span><br />64<span class="ls-operator">,</span><span class="ls-quote">"Customer Lookup"</span><br /> <span class="ls-statement">Exit</span> <span class="ls-statement">Sub</span><br /> <span class="ls-statement">End</span> <span class="ls-statement">If</span><br /> <span class="ls-statement">If</span> <span class="ls-function">Ubound</span><span class="ls-operator">(</span>IDs<span class="ls-operator">)</span> <span class="ls-operator">></span> 0 <span class="ls-statement">Then</span><br /> stat<span class="ls-operator">=</span> ws<span class="ls-operator">.</span>Prompt<span class="ls-operator">(</span><span class="ls-constant">PROMPT_OKCANCELLIST</span><span class="ls-operator">,</span><span class="ls-quote">"Select Customer"</span><span class="ls-operator">,</span><span class="ls-quote">"Select the Customer </span></div><div class="lotusscript"><span class="ls-quote">from this list:"</span><span class="ls-operator">,</span><span class="ls-quote">""</span><span class="ls-operator">,</span>IDs<span class="ls-operator">)</span><br /> <span class="ls-statement">If</span> stat <span class="ls-operator">=</span> <span class="ls-quote">""</span> <span class="ls-statement">Then</span> <span class="ls-statement">Exit</span> <span class="ls-statement">Sub</span><br /> <span class="ls-statement">Else</span><br /> stat <span class="ls-operator">=</span> IDs<span class="ls-operator">(</span>0<span class="ls-operator">)</span><br /> <span class="ls-statement">End</span> <span class="ls-statement">If</span><br /> <span class="ls-statement">With</span> docThis<br /> <span class="ls-operator">.</span>CustName <span class="ls-operator">=</span> <span class="ls-function">Trim</span><span class="ls-operator">(</span><span class="ls-function">Strleft</span><span class="ls-operator">(</span>stat<span class="ls-operator">,</span><span class="ls-quote">""</span><span class="ls-operator">)</span><span class="ls-operator">)</span><br /> <span class="ls-operator">.</span>CustNum <span class="ls-operator">=</span> <span class="ls-function">Trim</span><span class="ls-operator">(</span><span class="ls-function">Strright</span><span class="ls-operator">(</span>stat<span class="ls-operator">,</span><span class="ls-quote">""</span><span class="ls-operator">)</span><span class="ls-operator">)</span><br /> <span class="ls-statement">End</span> <span class="ls-statement">With</span><br /> <span class="ls-function">Call</span> ws<span class="ls-operator">.</span>CurrentDocument<span class="ls-operator">.</span>Reload<br /><span class="ls-statement">End</span> <span class="ls-statement">Sub</span><br /></div></pre><br /><br /><div style="BORDER-RIGHT: 1px solid; BORDER-TOP: 1px solid; FONT-SIZE: 8pt; MARGIN: 5px 50px; BORDER-LEFT: 1px solid; COLOR: gray; BORDER-BOTTOM: 1px solid; FONT-FAMILY: sans-serif"><center>This LotusScript was converted to HTML using the <b><i>ls2html</i></b> routine,<br />provided by Julian Robichaux at <a href="http://www.nsftools.com" target="_blank">nsftools.com</a>.</center></div><br /><br />The agent makes sure there are entries in all the fields passed for the JDBC call. If no records are found, the user is informed. If one record is found, the fields are updated automatically. If more than one record is found, a dialog box is presented from which the user can choose an entry.<br /><br />Who says Notes/Domino is a closed environment? Now go forth and JDBC!<br /><br />Technorati: <a href="http://technorati.com/tag/show-n-tell+thursday" rel="tag">Show-n-Tell Thursday</a><br />Categories: <span class="category">Show-n-Tell Thursday_</span><strong></strong>Don McNallyhttp://www.blogger.com/profile/17995406659448494074noreply@blogger.com7tag:blogger.com,1999:blog-24863326.post-1147969611291225182006-05-18T12:09:00.000-04:002006-05-18T12:28:24.046-04:00SnTT: Taking Notes Podcast<img src="http://webpages.charter.net/dtmc/images/SNTTbtn.png" align=right>I posted this suggestion on the <a href="http://takingnotes.openntf.org/">Taking Notes Podcast blog</a> but didn't get any feedback on it yet, so I'm going to try a bit of an experiment. The experiment is to see how far this blog reaches (not very far, I suspect).<br /><br />At any rate, I find the Taking Notes podcast to be a good source of information - and it's entertaining a lot of the time too. If you haven't checked it out, do so, either from the blog site or through iTunes. Bruce <a href="http://www.bruceelgort.com/blogs/be.nsf/plinks/BELT-6PWLCU">posted today</a> about some of the guests they have lined up for future episodes and they look good.<br /><br />Now for the experiment: Bruce said in comments on the most recent podcast that they have about 1,000 listeners, which he thinks is pretty good. But I think there are a lot more people out there who could benefit from the podcast so let's spread the word. So, if you read this, are a Notes/Domino developer or admin (or are otherwise interested in Notes/Domino), listen to the podcast and have your own blog, publicize the podcast on your blog. And leave me a comment that you did (please!).<br /><br />And keep up the good work, Bruce and Julian!<br /><br />Technorati: <a href="http://technorati.com/tag/show-n-tell+thursday" rel="tag">Show-n-Tell Thursday</a><br />Categories: <span class="category">Show-n-Tell Thursday_</span>Don McNallyhttp://www.blogger.com/profile/17995406659448494074noreply@blogger.com2tag:blogger.com,1999:blog-24863326.post-1147371901099768052006-05-11T14:07:00.000-04:002006-05-12T21:22:10.410-04:00SnTT: Sub-Categories in Views<img src="http://webpages.charter.net/dtmc/images/SNTTbtn.png" align=right>Kind of by accident a while back, I discovered how to add a second level of categorization in a view without adding a second categorized column. You *can* add a second column, but that will cause a second category to appear under all top-levels; I only wanted sub-categories under one of my top-level categories.<br /><br />Here is a picture of what I wanted:<br /><a href="http://photos1.blogger.com/blogger/90/2586/1600/Sub-categoryView.jpg"><img style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;" src="http://photos1.blogger.com/blogger/90/2586/400/Sub-categoryView.jpg" border="0" alt="" /></a><br><br />I only wanted additional categories under the 4. Fieldwork category. How do you do it? Add a backslash (\) and your second category to the end of your formula in the categorized column:<br /><a href="http://photos1.blogger.com/blogger/90/2586/1600/Sub-categoryFormula.jpg"><img style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;" src="http://photos1.blogger.com/blogger/90/2586/400/Sub-categoryFormula.jpg" border="0" alt="" /></a><br><br />Note that you have to "escape" the backslash by entering it in the formula twice. I stumbled on this by seeing the behavior in a view that was categorized by a file path field (eg. Data\PlayDbs\Developer1\MyBlog.nsf). Now you have sub-categories just where you want them with no effect on categories that don't need them.<br /><br />Technorati: <a href="http://technorati.com/tag/show-n-tell thursday" rel="tag">Show-n-Tell Thursday</a><br><br />Categories: <span class="category">Show-n-Tell Thursday_</span>Don McNallyhttp://www.blogger.com/profile/17995406659448494074noreply@blogger.com1tag:blogger.com,1999:blog-24863326.post-1146766710378097802006-05-04T14:02:00.000-04:002006-05-13T07:51:40.213-04:00SnTT: Computed Outline Entries<img style="float:right; margin:0 0 10px 10px;cursor:pointer; cursor:hand;" src="http://photos1.blogger.com/blogger/90/2586/200/SNTTbtn.png" border="0" alt="" />I did this a while ago but ran across it again and thought I'd share it. I'm not sure in which release this first appeared (somewhere in 6.x), but you can set an outline entry to compute the database from which to read a design item. You may already know that you can choose a Named Item for the outline entry to display and to choose that item either from the current database or from another database. We wanted to read an item from another database but to have the database change each year without changing the outline entry. That was not possible in R5 but is now.<br /><br />To do this, set your Content Type to Named Item and then click the @ (Formula) button next to the Value field. Here is a screen shot of the resulting dialog:<br /><a href="http://photos1.blogger.com/blogger/90/2586/1600/OutlineDialog.jpg"><img style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;" src="http://photos1.blogger.com/blogger/90/2586/320/OutlineDialog.jpg" border="0" alt="" /></a><br><br />In the Formula for Database block, notice that the path to the database includes variables. This one points to a database in a folder for the current year; we have an entry like this plus one for the prior year in one of our outlines. We used to have to modify the outline each year to update these two entries but now we don't have to!<br /><br />Technorati: <a href="http://technorati.com/tag/show-n-tell thursday" rel="tag">Show-n-Tell Thursday</a><br />Categories: <span class="category">Show-n-Tell Thursday_</span>Don McNallyhttp://www.blogger.com/profile/17995406659448494074noreply@blogger.com0tag:blogger.com,1999:blog-24863326.post-1145538282501546662006-04-20T08:51:00.000-04:002006-05-13T07:53:09.880-04:00SnTT: Forcing Users to Use Your Button<img src="http://webpages.charter.net/dtmc/images/SNTTbtn.png" align=right>OK, so I'm back with another SnTT tip. This is one we use to force users to click an action button to edit a document rather than use any other means (open from a view in Edit mode, press CTRL-E, click Actions | Edit Document, etc.).<br /><br />This defines a global variable on the form (since it has to be available to the QueryModeChange and the action button) and includes code for the form's QueryOpen and QueryModeChange events, plus code for the action button itself. I haven't tested this in any other kind of button/hotspot within a form, but I suspect it would work.<br /><br />We use it in an application where we want users to make changes only through a dialog box so we can restrict the fields they can change. Sure, we could have run the code from the QueryModeChange, but that's the beauty of Notes/Domino: lots of different ways to accomplish the same thing!<br /><br /><style>.lotusscript { font-family: sans-serif; font-size: 9pt; color: black; }<br />.ls-comment { color: green; }<br />.ls-quote { color: black; }<br />.ls-datatype { color: black; }<br />.ls-operator { color: blue; }<br />.ls-keyword { color: blue; }<br />.ls-statement { color: blue; }<br />.ls-function { color: blue; }<br />.ls-class { color: black; }<br />.ls-constant { color: purple; }<br /></style><br /><pre><div class=lotusscript><br /><font class=ls-comment>'(Globals): </font><br /><br /><font class=ls-keyword>Option</font> <font class=ls-keyword>Public</font><br /><font class=ls-statement>Dim</font> EditAction <font class=ls-keyword>As</font> <font class=ls-keyword>Integer</font><br /><br /><font class=ls-comment>'Form_name: </font><br /><br /><font class=ls-statement>Sub</font> Querymodechange<font class=ls-operator>(</font>Source <font class=ls-keyword>As</font> <font class=ls-class>Notesuidocument</font><font class=ls-operator>,</font> Continue <font class=ls-keyword>As</font> <font class=ls-keyword>Variant</font><font class=ls-operator>)</font><br /> <font class=ls-statement>If</font> <font class=ls-operator>Not</font> Source<font class=ls-operator>.</font>EditMode <font class=ls-operator>and</font> <font class=ls-operator>Not</font> EditAction <font class=ls-statement>Then</font><br /> <font class=ls-function>Messagebox</font> <font class=ls-quote>"You must use the Edit action button to edit the document"</font><font class=ls-operator>,</font>64<font class=ls-operator>,</font><font class=ls-quote>"Edit " &_<br> "Document"</font><br /> Continue <font class=ls-operator>=</font> <font class=ls-constant>False</font><br /> <font class=ls-statement>End</font> <font class=ls-statement>If</font><br /><font class=ls-statement>End</font> <font class=ls-statement>Sub</font><br /><font class=ls-statement>Sub</font> Queryopen<font class=ls-operator>(</font>Source <font class=ls-keyword>As</font> <font class=ls-class>Notesuidocument</font><font class=ls-operator>,</font> Mode <font class=ls-keyword>As</font> <font class=ls-keyword>Integer</font><font class=ls-operator>,</font>_<br><br />Isnewdoc <font class=ls-keyword>As</font> <font class=ls-keyword>Variant</font><font class=ls-operator>,</font> Continue <font class=ls-keyword>As</font> <font class=ls-keyword>Variant</font><font class=ls-operator>)</font><br /> <font class=ls-statement>If</font> <font class=ls-operator>Not</font> Isnewdoc <font class=ls-statement>Then</font><br /> <font class=ls-statement>If</font> Source<font class=ls-operator>.</font>EditMode <font class=ls-statement>Then</font><br /> <font class=ls-function>Messagebox</font> <font class=ls-quote>"This document cannot be opened in Edit mode"</font><font class=ls-operator>,</font>16<font class=ls-operator>,</font><font class=ls-quote>"Edit Document"</font><br /> Continue <font class=ls-operator>=</font> <font class=ls-constant>False</font><br /> <font class=ls-statement>End</font> <font class=ls-statement>If</font><br /> <font class=ls-statement>End</font> <font class=ls-statement>If</font><br /><font class=ls-statement>End</font> <font class=ls-statement>Sub</font><br /><br /><font class=ls-comment>'Edit: (action button)</font><br /><br /><font class=ls-statement>Sub</font> Click<font class=ls-operator>(</font>Source <font class=ls-keyword>As</font> <font class=ls-class>Button</font><font class=ls-operator>)</font><br /> <font class=ls-statement>Dim</font> ws <font class=ls-keyword>As</font> <font class=ls-keyword>New</font> <font class=ls-class>NotesUIWorkspace</font><br /><br /> EditAction <font class=ls-operator>=</font> <font class=ls-constant>True</font><br /><br /><font class=ls-comment>' Do your checks to make sure they can edit the document, then put it into Edit mode</font><br /><br /> ws<font class=ls-operator>.</font>CurrentDocument<font class=ls-operator>.</font>EditMode <font class=ls-operator>=</font> <font class=ls-constant>True</font><br /><br /> EditAction <font class=ls-operator>=</font> <font class=ls-constant>False</font><br /><font class=ls-statement>End</font> <font class=ls-statement>Sub</font><br /></div></pre><br /><br /><div style="FONT-SIZE: 8pt; COLOR: gray; FONT-FAMILY: sans-serif; BORDER: 1 solid Grey; margin : 5px 50px 5px 50px;"><center>This LotusScript was converted to HTML using the <b><i>ls2html</i></b> routine,<br>provided by Julian Robichaux at <a href="http://www.nsftools.com" target="_blank">nsftools.com</a>.</center></div><br /><br />Technorati: <a href="http://technorati.com/tag/show-n-tell thursday" rel="tag">Show-N-Tell Thursday</a><br />Categories: <span class="category">Show-n-Tell Thursday_</span>Don McNallyhttp://www.blogger.com/profile/17995406659448494074noreply@blogger.com2tag:blogger.com,1999:blog-24863326.post-1143726294122520712006-03-30T08:24:00.000-05:002006-05-13T07:54:35.660-04:00SnTT: Compact Local Databases on Demand<img src="http://webpages.charter.net/dtmc/images/SNTTbtn.png" align="right" />A couple of weeks ago, Chris Miller posted a <a href="http://www.idonotes.com/IdoNotes/IdoNotes.nsf/dx/03132006025355PMCMISEC.htm">tip</a> about scheduling databases to be compacted on a user workstation. If, for whatever reason, you don't want to schedule that task, here is some code you can put in a user-accessible button (like in a "Procedures Manual" document) that compacts databases and sends a mail message to the user with the results.<br /><br /><style>.lotusscript { font-family: sans-serif; font-size: 9pt; color: black; }.ls-comment { color: green; }.ls-quote { color: black; }.ls-datatype { color: black; }.ls-operator { color: blue; }.ls-keyword { color: blue; }.ls-statement { color: blue; }.ls-function { color: blue; }.ls-class { color: black; }.ls-constant { color: purple; }</style><pre><div class="lotusscript"><span class="ls-statement">Dim</span> session <span class="ls-keyword">As</span> <span class="ls-keyword">New</span> <span class="ls-class">NotesSession</span><br /><span class="ls-statement">Dim</span> db <span class="ls-keyword">As</span> <span class="ls-class">NotesDatabase</span><br /><span class="ls-statement">Dim</span> dbdir <span class="ls-keyword">As</span> <span class="ls-keyword">New</span> <span class="ls-class">NotesDBDirectory</span><span class="ls-operator">(</span><span class="ls-quote">""</span><span class="ls-operator">)</span><br /><span class="ls-statement">Dim</span> Stat <span class="ls-keyword">As</span> <span class="ls-keyword">Long</span><br /><span class="ls-statement">Dim</span> nlog <span class="ls-keyword">As</span> <span class="ls-keyword">New</span> <span class="ls-class">NotesLog</span><span class="ls-operator">(</span><span class="ls-quote">"Compact Utility"</span><span class="ls-operator">)</span><br /><br /><span class="ls-function">Call</span> nlog<span class="ls-operator">.</span>OpenMailLog<span class="ls-operator">(</span>session<span class="ls-operator">.</span>username<span class="ls-operator">,</span><span class="ls-quote">"DB Compact Utility"</span><span class="ls-operator">)</span><br /><br /><span class="ls-statement">Set</span> db <span class="ls-operator">=</span> dbdir<span class="ls-operator">.</span>GetFirstDatabase<span class="ls-operator">(</span><span class="ls-constant">Database</span><span class="ls-operator">)</span><br /><span class="ls-function">Call</span> db<span class="ls-operator">.</span><span class="ls-function">Open</span><span class="ls-operator">(</span><span class="ls-quote">""</span><span class="ls-operator">,</span>db<span class="ls-operator">.</span>Filepath<span class="ls-operator">)</span><br /><span class="ls-statement">On</span> <span class="ls-function">Error</span> <span class="ls-statement">Resume</span> <span class="ls-statement">Next</span><br /><span class="ls-statement">While</span> <span class="ls-operator">Not</span> <span class="ls-operator">(</span>db <span class="ls-operator">Is</span> <span class="ls-constant">Nothing</span><span class="ls-operator">)<br /></span><span class="ls-function"> Print</span> <span class="ls-quote">"Compacting: "</span> <span class="ls-operator">&</span> db<span class="ls-operator">.</span>FilePath<br /> <span class="ls-statement">If</span> db<span class="ls-operator">.</span>PercentUsed <span class="ls-operator"><</span> 90 <span class="ls-statement">Then<br /> </span>stat <span class="ls-operator">=</span> db<span class="ls-operator">.</span>Compact<br /> <span class="ls-statement">If</span> <span class="ls-function">Err</span> <span class="ls-operator">=</span> 4005 <span class="ls-statement">Then<br /> </span><span class="ls-function">Call</span> nlog<span class="ls-operator">.</span>LogAction<span class="ls-operator">(</span>db<span class="ls-operator">.</span>FilePath <span class="ls-operator">&</span> <span class="ls-quote">" is in use or not on workspace."</span><span class="ls-operator">)<br /> </span><span class="ls-function">Err</span> <span class="ls-operator">=</span> 0<br /> <span class="ls-statement">Else<br /> </span><span class="ls-function">Call</span> nlog<span class="ls-operator">.</span>LogAction<span class="ls-operator">(</span><span class="ls-quote">"Compacted "</span> <span class="ls-operator">&</span> db<span class="ls-operator">.</span>FilePath <span class="ls-operator">&</span> <span class="ls-quote">" bytes freed: "</span> <span class="ls-operator">&</span> <span class="ls-function">Str</span><span class="ls-operator">(</span>stat<span class="ls-operator">)</span><span class="ls-operator">)<br /> </span><span class="ls-statement">End</span> <span class="ls-statement">If<br /> </span><span class="ls-statement">Else<br /> </span><span class="ls-function">Call</span> nlog<span class="ls-operator">.</span>LogAction<span class="ls-operator">(</span><span class="ls-quote">"Database "</span> <span class="ls-operator">&</span> db<span class="ls-operator">.</span>FilePath <span class="ls-operator">&</span> <span class="ls-quote">" already "</span> <span class="ls-operator">&</span> db<span class="ls-operator">.</span>PercentUsed <span class="ls-operator">&</span> <span class="ls-quote">" percent in use"</span><span class="ls-operator">)<br /> </span><span class="ls-statement">End</span> <span class="ls-statement">If<br /> </span><span class="ls-function">Call</span> db<span class="ls-operator">.</span><span class="ls-function">Close</span><span class="ls-operator">(</span><span class="ls-operator">)<br /> </span><span class="ls-statement">Set</span> db <span class="ls-operator">=</span> dbdir<span class="ls-operator">.</span>GetNextDatabase<span class="ls-operator">(</span><span class="ls-operator">)<br /> </span><span class="ls-function">Call</span> db<span class="ls-operator">.</span><span class="ls-function">Open</span><span class="ls-operator">(</span><span class="ls-quote">""</span><span class="ls-operator">,</span>db<span class="ls-operator">.</span>filepath<span class="ls-operator">)<br /></span><span class="ls-statement">Wend<br />C</span><span class="ls-function">all</span> nlog<span class="ls-operator">.</span><span class="ls-function">Close<br /></span><span class="ls-function">Messagebox</span><span class="ls-operator">(</span><span class="ls-quote">"Finished Compacting"</span><span class="ls-operator">)</span></div></pre><br /><div style="BORDER-RIGHT: 1px solid; BORDER-TOP: 1px solid; FONT-SIZE: 8pt; MARGIN: 5px 50px; BORDER-LEFT: 1px solid; COLOR: gray; BORDER-BOTTOM: 1px solid; FONT-FAMILY: sans-serif"><center>This LotusScript was converted to HTML using the <b><i>ls2html</i></b> routine,<br />provided by Julian Robichaux at <a href="http://www.nsftools.com" target="_blank">nsftools.com</a>.</center></div><br /><br /><br />Technorati tags: <a href="http://technorati.com/tag/show-n-tell thursday" rel="tag">Show-N-Tell Thursday</a><br />Categories: <span class="category">Show-n-Tell Thursday_</span>Don McNallyhttp://www.blogger.com/profile/17995406659448494074noreply@blogger.com1tag:blogger.com,1999:blog-24863326.post-1143518805966852572006-03-27T23:05:00.001-05:002006-05-03T11:49:47.266-04:00SnTT: Homegrown Application Change Documentation<span style="font-style: italic;">Originally posted on March 17, 2006</span><br /><br />Change control in your application development has become increasingly important over the last couple of years. How good is your system for documenting changes? There are products out there to help manage the process and keep track of which design elements have changed but you can develop a basic system on your own.<br /><br />In the Lotus Sandbox at <a href="http://www.ibm.com/ldd/sandbox.nsf">developerWorks: Lotus (LDD/Notes.net)</a> you can find the <a href="http://www-10.lotus.com/ldd/sandbox.nsf/ecc552f1ab6e46e4852568a90055c4cd/f81067f94a1143f3852567d800660625?OpenDocument">DatabaseDesign class</a>. We used this class as the basis for developing code that compares design elements in two databases to identify differences. The databases can be two replicas of the same database (for example one local and one on the server) or two copies of a database (for example one development and one production). Within the same application, we also used the class to create lists of design items to populate drop down lists in a dialog box; that lets users create their own list of changed design items by choosing from lists.<br /><br />Unfortunately, at this time I am not able to show you the code we developed, but I thought it was worthwhile to point out that the class is out there (thanks <a href="http://www.damienkatz.net">Damien Katz</a> and Andre Guirard) and that it can be helpful in developing a change recording system. <a href="mailto:donald.mcnally@nationalcity.com?subject=Change Recorder">Contact me</a> if you run into problems developing your change recorder.<br /><br /><br />Technorati tags: <a href="http://technorati.com/tag/show-n-tell thursday" rel="tag">Show-N-Tell Thursday</a>Don McNallyhttp://www.blogger.com/profile/17995406659448494074noreply@blogger.com0tag:blogger.com,1999:blog-24863326.post-1143518742746445852006-03-27T23:05:00.000-05:002006-03-27T23:05:42.746-05:00SnTT: Sametime Success Story<span style="font-style: italic;">Originally posted on March 9, 2006</span><br /><br />This is a story from a while ago but it seemed worthwhile to share as a success. About four years ago, we decided to evaluate Sametime in our environment. We installed the server and rolled out the Connect client to a few people. We also made the web conferencing components available to a larger group of people to test that feature.<br /><br />When we did our post-trial analysis, we found that we had pretty much paid for the software during the trial. One group of people used it for an online meeting instead of paying for another vendor's "rented" option. Our team used it to install a piece of software on 15 user machines, saving trips to desktops (including some in remote offices) or time on the phone walking people through the procedures. I know: who visits desktops to install software? But this was four years ago before automated software distribution was implemented in our environment.<br /><br />To this day, it receives fairly heavy use for scheduled and instant meetings. My team uses it nearly every day to demo applications that are in development, to resolve problems on user machines or provide training at application rollout. We have also been slowly incorporating presence awareness into our applications.<br /><br />The reach of the product is relatively small within our organization, but the investment continues to pay for itself.Don McNallyhttp://www.blogger.com/profile/17995406659448494074noreply@blogger.com0tag:blogger.com,1999:blog-24863326.post-1143518672255448122006-03-27T23:03:00.000-05:002006-03-27T23:04:32.256-05:00Show-n-Tell Thursday<span style="font-style: italic;">Originally posted on March 2, 2006</span><br /><br />Last week, I showed a way to show responses in a view but open the parent of the response when a document was double-clicked. This week, I have another view tip. Form formulas allow you to open documents from a view using a form other than the one defined on the document. What I will show you is a way to provide users a choice of two forms to display documents and the means to set which form they want to use when they open the view.<br /><br />Why would you use this? We have an application in which we want to display the fields from a document in a "report" format that can be printed for easier reading or in "data entry" format for user entry. This method allows us to use one view to perform both functions. In our case, the documents that appear in the view that uses this code are main documents and the "report" format form also contains LotusScript code to get field values from specific response documents to include in the report.<br /><br />For simplicity, this assumes that all the documents in the view use the same form but it wouldn't be too hard to extend the code to handle more than one form. To give the users a way to determine the form they prefer, create an action button in the view with this code:<br /><br />curr:=@GetProfileField("UserProfile";"DisplayFormat";@UserName);<br />@If(curr="Summ";<br /> @If(@Prompt([YesNo];"Change Format";"You are currently opening these documents in the Summary Report format. Open documents with the document's form?")=1;<br /> @SetProfileField("UserProfile";"DisplayFormat";"";@UserName);@Return(""));<br /> @If(@Prompt([YesNo];"Change Format";"You are currently opening these documents with the document's form. Open documents in Summary Report format?")=1;<br /> @SetProfileField("UserProfile";"DisplayFormat";"Summ";@UserName);@Return("")))<br /><br />The Form Formula for the view is then:<br /><br />@If(@IsNewDoc;Form;<br /> @GetProfileField("UserProfile";"DisplayFormat";@UserName)="Summ";"ReportFormat";<br /> Form)<br /><br />Nothing to it!Don McNallyhttp://www.blogger.com/profile/17995406659448494074noreply@blogger.com0tag:blogger.com,1999:blog-24863326.post-1143518572424678092006-03-27T22:58:00.000-05:002006-03-27T23:02:52.436-05:00Show-n-Tell Thursday<span style="font-style: italic;">Originally posted on February 23, 2006</span><br /><br /><span class="body"> Here's my first contribution to this new web-wide feature. It took me a while to come up with something small that I thought may be useful. This is a kinda sorta pseudo-relational behavior you can incorporate into a view.<br /><br />Say you have a set of response documents that contain the fields you want to display in a view. But, instead of opening those documents when they are clicked, you would rather open their parent document. And you display the response documents in other views, but in those views you want to open the response document (so you don't want to put code in the QueryOpen of the response form). What do you do?<br /><br />Put this code in the QueryOpenDocument event of the view and the parent document will open instead of the document that you double-clicked to open.<br /><br /><b>UPDATE:</b> I added some error handling for orphans, unreadable parents and replication conflicts. Don't know why I didn't think of it, other than that we hadn't run into those problems in the view where I used the code. <pre><div class="lotusscript"><br /><span class="ls-statement"><span style="font-family: Georgia,serif;"><style>.lotusscript { font-family: sans-serif; font-size: 9pt; color: black; }<br />.ls-comment { color: green; }<br />.ls-quote { color: black; }<br />.ls-datatype { color: black; }<br />.ls-operator { color: blue; }<br />.ls-keyword { color: blue; }<br />.ls-statement { color: blue; }<br />.ls-function { color: blue; }<br />.ls-class { color: black; }<br />.ls-constant { color: purple; }<br /></style><br /><pre><div class="lotusscript"><br /><span class="ls-statement">Sub</span> Queryopendocument<span class="ls-operator">(</span>Source <span class="ls-keyword">As</span> <span class="ls-class">Notesuiview</span><span class="ls-operator">,</span> Continue <span class="ls-keyword">As</span> <span class="ls-keyword">Variant</span><span class="ls-operator">)</span><br /> <span class="ls-statement">Dim</span> doc <span class="ls-keyword">As</span> <span class="ls-class">NotesDocument</span><br /> <span class="ls-statement">Dim</span> docParent <span class="ls-keyword">As</span> <span class="ls-class">NotesDocument</span><br /> <span class="ls-statement">Dim</span> ws <span class="ls-keyword">As</span> <span class="ls-keyword">New</span> <span class="ls-class">NotesUIWorkspace</span><br /> <span class="ls-statement">Dim</span> openIt <span class="ls-keyword">As</span> <span class="ls-keyword">Integer</span><br /> <br /> openIt <span class="ls-operator">=</span> <span class="ls-constant">False</span><br /> <span class="ls-statement">Set</span> doc <span class="ls-operator">=</span> Source<span class="ls-operator">.</span>Documents<span class="ls-operator">.</span>GetFirstDocument<br /> <span class="ls-statement">If</span> doc<span class="ls-operator">.</span>IsResponse <span class="ls-statement">Then</span><br /> Continue <span class="ls-operator">=</span> <span class="ls-constant">False</span><br /> <span class="ls-statement">If</span> doc<span class="ls-operator">.</span>HasItem<span class="ls-operator">(</span><span class="ls-quote">"$Conflict"</span><span class="ls-operator">)</span> <span class="ls-statement">Then</span><br /> <span class="ls-function">Messagebox</span> <span class="ls-quote">"Opening replication conflicts is not allowed in this view"</span><span class="ls-operator">,</span>48<span class="ls-operator">,</span><span class="ls-quote">"Open Document"</span><br /> <span class="ls-statement">Exit</span> <span class="ls-statement">Sub</span><br /> <span class="ls-statement">End</span> <span class="ls-statement">If</span><br /> <span class="ls-statement">Set</span> docParent <span class="ls-operator">=</span> doc<span class="ls-operator">.</span>ParentDatabase<span class="ls-operator">.</span>GetDocumentByUNID<span class="ls-operator">(</span>doc<span class="ls-operator">.</span>ParentDocumentUNID<span class="ls-operator">)</span><br /> <span class="ls-statement">If</span> docParent <span class="ls-operator">Is</span> <span class="ls-constant">Nothing</span> <span class="ls-statement">Then</span><br /> openIt <span class="ls-operator">=</span> <span class="ls-constant">False</span><br /> <span class="ls-statement">Elseif</span> docParent<span class="ls-operator">.</span>IsDeleted <span class="ls-operator">Or</span> <span class="ls-operator">Not</span> docParent<span class="ls-operator">.</span>IsValid <span class="ls-statement">Then</span><br /> openIt <span class="ls-operator">=</span> <span class="ls-constant">False</span><br /> <span class="ls-statement">Else</span><br /> openIt <span class="ls-operator">=</span> <span class="ls-constant">True</span><br /> <span class="ls-statement">End</span> <span class="ls-statement">If</span><br /> <span class="ls-statement">If</span> openIt <span class="ls-statement">Then</span><br /> <span class="ls-function">Call</span> ws<span class="ls-operator">.</span>EditDocument<span class="ls-operator">(</span><span class="ls-constant">False</span><span class="ls-operator">,</span>docParent<span class="ls-operator">)</span><br /> <span class="ls-statement">Else</span><br /> <span class="ls-function">Messagebox</span> <span class="ls-quote">"Cannot open requested document. This may be because "</span> <span class="ls-operator">&</span><span class="ls-operator">_</span><br /> <span class="ls-quote">"the selected document is an orphan or because "</span> <span class="ls-operator">&</span><span class="ls-operator">_</span><br /> <span class="ls-quote">"you do not have access to the requested document."</span><span class="ls-operator">,</span>48<span class="ls-operator">,</span><span class="ls-quote">"Open Document"</span><br /> <span class="ls-statement">Exit</span> <span class="ls-statement">Sub</span><br /> <span class="ls-statement">End</span> <span class="ls-statement">If</span><br /> <span class="ls-statement">End</span> <span class="ls-statement">If</span><br /><span class="ls-statement">End</span> <span class="ls-statement">Sub</span><br /></div></pre><br /><br /><div style="FONT-SIZE: 8pt; COLOR: gray; FONT-FAMILY: sans-serif; BORDER: 1 solid Grey; margin : 5px 50px 5px 50px;"><center>This LotusScript was converted to HTML using the <b><i>ls2html</i></b> routine,<br />provided by Julian Robichaux at <a href="http://www.nsftools.com" target="_blank">nsftools.com</a>.</center></div></span></span></div></pre></span>Don McNallyhttp://www.blogger.com/profile/17995406659448494074noreply@blogger.com0