AddColumn (NotesQueryResultsProcessor - LotusScript)

Creates a single column of values to be returned when QueryResultsProcessor is executed. Column values can be generated from a field or a formula. Sorting order, categorization and hidden attributes determine the returned stream of results entries. Columns span all input result sets and databases taking part in the QueryResultsProcessor. Execute calls in the QueryResultsProcessor require at least one column to be specified.

Defined in

NotesQueryResultsProcessor

Syntax

Call notesQueryResultsProcessor.AddColumn( String Name, Optional String Title, Optional String Formula, Optional Integer SortOrder, Optional Boolean Hidden, Optional Boolean Categorized)

Parameters

Name

The unique programmatic name of the column within a QueryResults Processor instance. If there is no override using the addFormula method call, the name provided is treated as a field name in each database involved in the QueryResultsProcessor object.

In JSON output, the name value used is the element name for each returned entry. Values in the name field can also specify aggregate functions. Aggregate functions require categorized columns and they return computed numerical values across sets of results within a given category. For more information, see the description of categorized. For aggregate functions requiring a name as an argument, that name can be overridden the same as any name value without the aggregate function.

Title

String. Optional. The display title of the column. Used only in generated views, the title is the UI column header.

Formula

String. Formula language to serve as the default means of computing values for the column. If not supplied and if not overridden by an addFormula value, the name argument is treated as a field name. The precedence of supplying column values is:

AddFormula Formula Language override
Formula argument of the AddColumn method
Use the name argument of the AddColumn method as a database field name

If supplied, the Formula Language provided is applied to the column across all results added using addCollection or addDominoQuery. Formulas are not allowed on columns with aggregates.

Note: Do not enter a literal (for example, "\"My Literal\"") as a formula. Doing so results in malformed views. To return a literal value, use the AddFormula method, instead.

SortOrder

Integer. Optional. A constant indicating the direction of the sort. Default is SORT_UNORDERED. Legal values are:

SORT_UNORDERED
SORT_ASCENDING
SORT_DESCENDING

Hidden

Boolean. Optional. Specify this to sort by a column value but not return it. If true, the column cannot have a SortOrder of SORT_UNORDERED and cannot have Categorized set to True.

Categorized

Boolean. Optional. Categorized columns have a single entry returned for each unique value with entries containing that value nested under it. In JSON results, these nested entries are represented in arrays under each categorized unique value.

Multiply-occurring fields (i.e. lists) are not allowed to be categorized.

Any categorized column with a preceding uncategorized column or columns will force the creation of categorized values containing all prior uncategorized columns plus the categorized column.

A categorized column creates values for any preceding uncategorized column in addition to the categorized column. Categorized columns can nest to create subcategories. Consider the following columns, added in order left to right:

Col1	Col2	Col3	Col4	Col5	Col6	Col7
categorized			categorized		categorized

The first category contains a value for Col1. A subcategory contains categorized values for Col2, Col3 and Col4. A third category level contains categorized values for Col5 and Col6. Col7 value is included for each document under each of the three categories.

Aggregate function names begin with double @@ signs and are case insensitive. They cannot be nested one within another. Their numeric output can be either integer or floating point values. Examples of the five aggregate functions are:

@@avg(col1) Returns the sum all values of col1 within the current category divided by the number of documents.
@@sum(col2) Returns the sum of all values of col2 within the current category.
@@max(col3) Returns the maximum numeric value of col3 within the current category.
@@min(col4) Returns the minimum numeric value of col4 within the current category.
@@count() Returns the count of documents within the current category

Aggregate function columns must appear after the categorized column whose values control their computation. They are always SORT_UNORDERED, that is, unsorted.

Consider the following set of columns, added in order left to right:

1	2	3	4	5	6	7	8
Col1	Col2 categorized	@@avg(col3)	@@sum(col4)	Col5	Col6 categorized	@@avg(col7)	Col8

Col1 and Col2 are combined to create categorized values. The average of col3 values and the sum of col4 values are produced for each combined value. A subcategory containing the combination of Col5 and Col6 values creates values under the first category and the average of col7 values in documents containing each subcategory value is produced. Finally, Col8 values are produced for each document under both the category and subcategory. Note that you must use successive columns to combine columns to produce categories, so column 4 cannot switch places with column 5 nor can column 8 switch places with 7. The required column ordering then, is:

a. Any non-categorized, non-aggregate columns
b. A single categorized column
c. Any aggregate columns (can be multiple as above)
d. Finally, any document-level columns not taking part in categorization. That is, document detail.

Violations of that ordering result in validation errors or unexpected results. See the examples for the expected output using categories and aggregate functions.

Example

The following example shows a simple, single category set of columns.

Set qrp = db.Createqueryresultsprocessor()

	…
Call qrp.Addcolumn("sales_person", "", “”, SORT_ASCENDING, False, True)
Call qrp.AddColumn("ordno", "", “”, SORT_DESCENDING, False, False, 
     “order_number * 1000”)
Call qrp.AddColumn("order_origin", "", “”, SORT_UNORDERED, False, False)
Call qrp.AddColumn("partno")

Example with categories and aggregate functions

The following example creates nested category results with the column names shown in the previous table. For an example using overrides, see case 2 in the addFormula example.

‘  First category - 2 fields making 1 categorized key value	
Call qrp.AddColumn("Department", "Dept",, “$Left(dept; 10)”, 
     SORT_ASCENDING, False, False);
Call qrp.AddColumn("JobTitle", "Job Title ", “"SORT_ASCENDING, False, True)
				
‘  aggregates off the first category
Call qrp.AddColumn("@@avg(salary)", "", “”, SORT_UNORDERED, False, False)
Call qrp.AddColumn("@@sum(salary)", "", “”, SORT_UNORDERED, False, False)
	
‘  2nd category -  2 field categorized key
Call qrp.AddColumn("GEO”)  ‘ see shortened method form below
Call qrp.AddColumn("OU", "Organizational Unit ",””, SORT_ASCENDING, False, True)
				
‘ 4 aggregates off the 2nd category
Call qrp.AddColumn("@@count()", "", “”, SORT_UNORDERED, False, False)
Call qrp.AddColumn("@@avg(salary)", "", SORT_UNORDERED, False, False)
Call qrp.AddColumn("@@sum(salwithraise)", "", “”, SORT_UNORDERED, False, False)
Call qrp.AddColumn("@@avg(salwithraise)", "", “”, SORT_UNORDERED, False, False)
			
‘  document detail under both categories - unique to Domino
Call qrp.AddColumn("FullName", "Full Name ", “”, SORT_ASCENDING, False, False)