I wanted to share some frustrations that I have encountered with NotesSQL, to try to help others who may be trying to do something similar. (I am using NotesSQL 3.02e with Notes 6.0.1 in Windows 2000, to extract data from a Notes database into Excel.)
NotesSQL allows data to be extracted from views or forms. When extracting data from a form, all of the documents in the database that use that form can be seen as a “table” which can be queried, the “columns” of which are the fields on the form (including all of the ‘computed for display’ fields and also including some internal ‘fields’, eg NoteID, NoteUNID, NoteModified, etc).
First frustration is that when querying a view, any numeric columns are treated as text, so that the client application (Excel in this case) cannot perform arithmetic on the data retrieved (eg cannot use the data in pivot tables to show totals).
To get around this frustration, it is possible to extract the data directly from a form, although it may be necessary to create additional ‘Computed for Display’ fields to calculate values that would otherwise have been calculated in a view column.
Second frustration is that if a numeric field has been defined as a Percent format with, say, 1 decimal place (eg 0.37156 is displayed as 37.2%) then the field is extracted as a numeric column in the table but not as a percentage but as a number with the specified number of decimals (eg .4 in the case of the example given) resulting in a loss of significant digits.
Interestingly the sample database available from the IBM/Lotus web site (‘NotesSQL Sample database’, NotesSQL_Sample.nsf) http://www.lotus.com/notessql has no numeric columns in any of its views, so this problem is not exposed in the sample. Also none of the forms use Percent formatted numeric fields, so as not to expose the latter problem, either.
The other frustration is that NotesSQL regards certain words as reserved, see my earlier post on this subject… http://www-10.lotus.com/ldd/nd6forum.nsf/DateAllThreadedweb/12803b07300fbc9885256e2f003dd93a?OpenDocument which indicates that ‘Year’ and ‘Month’ are undocumented reserved words, although the error messages to indicate this could be more helpful (as could the on-line documentation).
I may be missing something but I have studied the on-line documentation closely enough to spot any mention of these issues?
I hope this information is helpful.