TexasSwede@gmail.com
Blog Tags
|
All entries tagged with
lotusscript
Bill Buchan just wrote about exporting data to Excel using a CSV file. There is actually a cool and surprisingly easy way to generate documents that show up in Excel as real spreadsheets, with formatting, colors, etc.
Simply create a file containing a HTML table. It may even work with multiple tables. You can use tags like <B> and <I> for bold/italic, and use the color attribute to set text color and background color. Use the colspan attribute to split a cell over multiple columns.
Save the file in Lotusscript as a regular text file, but with the extension XML XLS. Excel, as well as OpenOffice.org, will read the HTML and present as a nice spreadsheet.
The other day I was writing some code where I had to check a value against a list of potential values. I was importing a file as XML, and checking a number of transactions, each in their own node. If the Trans-ID tag had one of several values, the whole node was supposed to be discarded and not used.
I am sure many already use this method, but hopefully someone will learn something new.
In my example below, I am reading a field in a Notes document instead of data from an XML file, but the principle is the same:
tranType = Ucase(doc.GetItemValue("txn-type"))
If Instr("PD;DR;RP;NSFNF;RD;CRT;RCT;VCK;TPD;RCK;", tranType & ";") Then
End If
What I do is to use Instr() to check a string (the first argument) for the presence of a particular string (the second argument). To make sure I don't get any false matches, I add a semicolon to the end of the tranType as well as use the same character to separate the values in the first argument.
The Instr() function returns the position of the string found (1 or higher if found, 0 if not found). Since 0 is "False" and everything else is "True", if the tranType string is found, a value greater than 0 is returned, which is considered "True".
This LotusScript was converted to HTML using the ls2html routine,
provided by Julian Robichaux at nsftools.com.
Not Forum Friday, but Usenet Tuesday.  A user in comp.groupware.lotus-notes.programmer had a question about generating Notes document remotely/in an automated way from another web application, using Perl:
> We have a Lotus Notes application and we would like to automate
> creating a new document or record in that application from another web
> based application.
>
> Here are the details:
> User action on web application triggers a PERL script to create a new
> record (document) in Lotus Notes (or Domino).
Here is my suggestion:
Create an agent that read the URL parameters you send to it.
Let's call the agent CreateNewDocument, and have two fields we want to fill out when the document is created. The Perl script can then call the URL like this:
The agent read the arguments and populate the corresponding fields, then save the document. I would highly recommend using a list to store the parameters, then you have a generic function you can re-use in any application.
In my article in the November/December 2006 issue of The View (starting on page 25) you have code for this.
Here is a class I just wrote to parse an incoming HTTP GET or POST. Put it in a script library called for example "URL.class":
Class URLclass
Public url As String
Private params List As String
Public Sub New()
Dim session As New NotesSession
Dim doc As NotesDocument
Set doc = session.DocumentContext ' Document with all CGI variables
' Check if HTTP GET or POST was used...
If Instr(doc.Query_String(0),"&") > 0 Then ' GET was used
url = doc.Query_String(0)
Elseif Instr(doc.Request_Content(0),"&") > 0 Then ' POST was used
url = "&" & doc.Request_Content(0)
Else ' No parameters
Exit Sub
End If
End Sub
Public Function GetParams() As Variant
Dim offset As Integer
Dim startpos As Integer
Dim midpos As Integer
Dim endpos As Integer
Dim nextpos As Integer
Dim dataname As String
Dim datavalue As String
startpos = Instr(url,"&") ' Start of first parameter
Do While Not startpos = 0
nextpos = Instr(startpos+1, url, "&") ' Start of next parameter
If nextpos = 0 Then ' We reached the end
endpos = Len(url)+1
Else
endpos = nextpos
End If
midpos = Instr(startpos+1, url, "=") ' Position of = character
dataname = Mid$(url,startpos+1,midpos-startpos-1) ' Get name
datavalue = Mid$(url,midpos+1,endpos-midpos-1) ' Get value
params(dataname) = datavalue ' Add value to list
startpos = nextpos ' Set new start position
Loop
GetParams = params
End Function
End Class
Here is a sample agent that uses the class and print the arguments to the browser. Just expand on that code to create the document in the database:
Option Public
Option Declare
Use "URL.class"
Sub Initialize
Dim url As New URLclass
Dim params As Variant
params = url.GetParams()
Forall x In params
Print Listtag(x) & " = " & params(Listtag(x)) & "<br>"
End Forall
End Sub
The LotusScript was converted to HTML using the ls2html routine,
provided by Julian Robichaux at nsftools.com.
Yes, I know it is Tuesday, but I was too busy last week for the SNTT entry...
At my workplace, we are in the process of purchasing and implementing a new billing system. It relies heavily on XML files, stored in the file system, for inport/export. The idea is that we export XML from our policy system to be processed by this software. The results are returned as XML files, and we are currently looking at how we can consume them and present them to the accounting department.
We are mainly looking at tools like Crystal Reports 2008.
Just for fun, I decided to write an XML importer in Lotusscript. The XML is very simple so I did not have to do any advanced parsing.
I decided to write it as a class. The class just have a few methods. One is of course New(), the only thing it does is clear the xmldata string and also clear the list of XML tags from memory.
LoadXMLData() open the specified XML file and read the text into the xmldata string, adding a linebreak after each line.
XML2List take the xmlstring, parse it and build a list of all the tags and values. Because the XML is so simple, with no multiple values or deeply nested data, I can very easily parse it using normal string operations in a Do-While loop.
The final method is called CreateNotesDocument(), it creates a new Notes document in the specified database, using the specified form. To process the sample XML file with 66 XMl entries took 0.148 seconds (according to TeamStudio Profiler).
Here is the code:
Class XMLClass
Private xmldata As String
Private xml List As String
Public Sub New()
xmldata=""
Erase xml
End Sub
Public Sub LoadXMLData(filename As String)
Dim xmlline As String
Open filename For Input As #1
xmldata = ""
While Not Eof(1)
Line Input #1, xmlline
xmldata = xmldata & xmlline & Chr$(13)
Wend
Close #1
End Sub
Public Sub XML2List()
Dim tag As String
Dim value As String
Dim startpos As Long
Dim endpos As Long
Dim nextpos As Long
Dim endtagpos As Long
endpos = 1
Do
startpos = Instr(endpos,xmldata,"<")
If startpos > 0 Then
endpos = Instr(startpos,xmldata,">")
End If
If endpos > 0 Then
tag = Mid$(xmldata,startpos+1,endpos-startpos-1)
If Instr(tag,"/")=1 Then
startpos = Instr(endpos+1,xmldata,"<")
Else
nextpos = Instr(endpos,xmldata,"<")
endtagpos = Instr(endpos,xmldata,"</" & tag & ">")
If endtagpos = nextpos Then
value = Mid$(xmldata,endpos+1,nextpos-endpos-1)
If Iselement(xml(tag)) = False Then
xml(tag) = value
End If
endpos = Instr(nextpos,xmldata,">")
End If
End If
End If
Loop While startpos > 0
End Sub
Public Sub CreateNotesDocument(db As NotesDatabase, form As String)
Dim doc As NotesDocument
Set doc = New NotesDocument(db)
doc.Form = form
Forall x In xml
Call doc.ReplaceItemValue(Listtag(x),x)
End Forall
Call doc.Save(True,True)
End Sub
End Class
Sub Initialize
Dim session As New NotesSession
Dim db As NotesDatabase
Dim noic As XMLclass
Set db = session.CurrentDatabase
Set noic = New XMLclass()
Call noic.LoadXMLData("c:\XSLT\NOIC-1450-01 0-2007Nov09.xml")
Call noic.XML2List()
Call noic.CreateNotesDocument(db,"NOIC")
End Sub
This LotusScript was converted to HTML using the ls2html routine,
provided by Julian Robichaux at nsftools.com.
OK, I know it is Friday already, but here is my Show-n-Tell-Thursday entry...
Lists are a data type that I don't think is used enough. Many Notes developers, even some that been programming in the environment for years, don't use this very powerful feature. In previous years, Bill Buchan have been talking about lists in some of his Lotussphere sessions, and when asking how many in the audience that used lists, less than 10% raised their hands.
So what are lists? They are a data type, similar to an array, but instead of using a numeric index, it uses a string as index, or tag as it is called.
This makes it easy to address items in the list, check if they exists, etc.
Here is a simple example:
Dim age List as Integer
Dim name as String
age( "karl" ) = 38
age( "erik" ) = 7
name = InputBox$( "Enter name:" )
If IsElement(age(Lcase(name))) = True Then
MsgBox name & " is " & age(lcase(name)) & " years old."
Else
MsgBox name & " does not occur in the list."
End If
This code define a list of integers, containing the age of two people. When a name is entered in the input box, IsElement is used to check if an item with that name is found in the list. If it is, the age is displayed, if not another message is displayed, saying that the name was not found.
Note that the list tag (name in this case) is case sensitive!
To loop through a list and display all the values, use ForAll. Here is an example where we loop thorugh all the entries and display their list tags as well as the values:
Dim age List As Integer
age( "Karl" ) = 38
age( "Erik" ) = 7
Forall a In age
Msgbox Listtag(a) & " is " & a & " years old."
End Forall
Some of the things I use lists for are web agents, where I parse the URL passed for parameters, and build a list of them. Then it is very easy to write code that check for parameters and get the value if they exist.
I wrote an article about lists for The View, published in November/December 2006. You can find more details and examples there.
|
|
