Create a spreadsheet-like table of varying rows of data on the fly in a rich text field! This is a great solution...
to creating a line item type of table when you don't know how many rows you will need, without the headaches of misaligned data that comes with using a multi-value field solution. While this example demonstrates varying rows, it can also be adapted to produce varying columns as well!
The idea is to produce an interim text file of data embedded with RTF codes,
then import that file into a rich text field. It works beautifully. The result
is a bordered table, with data perfectly aligned in each cell.
Everything you need to get going is in my example, and the RTF codes needed are
VERY simple! You will need two agents: a simple @function agent, and a script
agent. The first agent simply calls the script which builds the file, and then
imports the file into a specified field. You must be in a document in edit mode
to use this.
After you set up the following two agents, open a document in edit mode which
contains the field you have specified to populate and run the calling agent.
This code is a very simple HARD-CODED example. In reality, you will need to do
collections of documents, and populate the table with real data.
(replace "fieldname" with the name of an rich text field you want to populate)
@PostedCommand([ToolsRunMacro];"Create RTF Table");
@PostedCommand([EditGotoField] ; "fieldname");
@PostedCommand( [FileImport] ; "MicrosoftWord RTF" ;"c:\\~table.rtf" )
"Create RTF Table" AGENT:
(paste this entire code into an agent. it looks messy here, but once in Notes
it is much more readable!)
'This script creates an RTF file, formatting data in a table format. After the
RTF file is created, it can be imported into a Notes Rich Text field.
'This can be used in conjunction with a simple @function agent which call this
script and imports the file with a single click.
'The end result is a dynamically created table that Notes can understand and
whose properties can then be manipulated by the user.
'Note: Notes will not recognize the Table properties until the document is
Dim fileNum As Integer 'filenumber parameter for manipulating the file
Dim fileName As String 'the path and name of the file. Must end with an ".RTF"
'These 3 String arrays set up a table with 4 columns. Change the upper bound to
correspond with the table size you want to create.
'This can also be dynamic by using a redim statement. It has been kept simple
here for demonstration purposes
Dim TableDef(3) As String 'Required. Sets up the width parameters of the table
columns. It is used one time to set up the table.
Dim TableHead(3) As String 'Optional. Stores the String data of the Headings of
the table. If used, it is used one time to print the headings.
Dim TableRow(3) As String 'Required. Stores the data for each Cell of the
table. This changes for each row.
On Error Goto ProcessError
'this section sets the table constants. The TableDef array is required. The
numbers determine the position of the columns in your table.
Print "Setting table parameters..."
TableDef(0) = "2275" 'width of column 1 in twentieths of a point
TableDef(1) = "4550" 'width of column 1 + column 2 in twentieths of a point
TableDef(2) = "6825" 'width of column 1 + column 2 + column 3 in twentieths of
TableDef(3) = "9000" 'width of column 1 + column 2 + column 3 + column 4 in
twentieths of a point
'the TableHead array is optional, and stores the headings for the table.
'if you do not want a heading you will need to remove some code from the
WriteTableHeader subroutine (see below)
TableHead(0) = "Column 1" 'heading text for column 1
TableHead(1) = "Column 2" 'heading text for column 2
TableHead(2) = "Column 3" 'heading text for column 3
TableHead(3) = "Column 4" 'heading text for column 4
Gosub OpenOutputFile 'open the output file for writing
Gosub WriteTableHeader 'write the table header
For i=1 To 3 'write the table rows
'This populates the rows of the table. This simple example is a an iteration
which makes 3 rows.
'You can use any means you like to populate the rows. You simply populate the
TableRow array with data, and call WriteTableRow.
'You do this for each row of the table.
TableRow(0) = "Row " & i & " - Column 1"
TableRow(1) = "Row " & i & " - Column 2"
TableRow(2) = "Row " & i & " - Column 3"
TableRow(3) = "Row " & i & " - Column 4"
Gosub WriteTableEnd 'writes closing RTF code for the RTF file.
Gosub CloseOutputFile 'closes the output file
Print "Opening the output file..."
'Prepare to write out a sequential file
fileNum% = Freefile()
fileName$ = "c:\~table.rtf"
Open fileName$ For Output As fileNum%
Print "Writing table header..."
Print #fileNum% , BuildTableDef(TableDef) 'this is required and writes out the
RTF file header and table definition code
Print #fileNum% , "\b" & BuildTableRow(TableHead) & "\b0" 'This is optional and
writes out the heading for the table.
'If you did not popluate the TableHead array, remove this line of code.
'the "\b" and "\b0" are optional RTF codes for turning toggling BOLD type
'you can concatenate many other optional formatting codes here, and when