Skip to main content

Creating a Field

The CREATEFIELD extension function allows you to create additional columns and populate their value with an expression, evaluating any other columns in the row. Only rows that have detail records will be evaluated and subtotal/total fields are ignored.

The expression must be valid Clarion Syntax, which is a rich programming language with a few examples here.

The Syntax of the extension is:

CREATEFIELD fieldname=Syntax Expression

The statement can contain field names, constants and operands. Valid field names used in the expression can only be the column names binded to the result set which is FIELDx where x is the column number. The only other valid field name is FIELDROW which is the current row number.

For example, if the result set returns 10 columns, they will be named FIELD1, FIELD2, FIELD3, ... FIELD10. The newly created field will be appended to the end of the columns in the order in which it appears in the SQL statement, and will be named with the next field number if it needs to be addressed in a subsequent CREATEFIELD statement.

For example:

SELECT ROOMNUMBER,DESCRIPTION,BEDTYPE FROM ROOMS

CREATEFIELD FIRSTFIELD=1

CREATEFIELD SECONDFIELD=2
  • The actual row number would be FIELDROW
  • Roomnumber would be FIELD1
  • Description would be FIELD2
  • RoomType would be FIELD3
  • FirstField would be FIELD4
  • SecondField would be FIELD5

In the above example, the 4th field would contain the value 1 and the 5th field would contain the value 2.

All new fields created would be defined as the ANY datatype and the @s128 (string 128) format picture, a string of 128 characters.

SELECT FIRSTNAME,LASTNAME,ADULTS FROM FOLIOHD

CREATEFIELD FULLNAME=CLIP(FIELD1) & ' ' & CLIP(FIELD2)

CREATEFIELD ADULTSSTATEMENT='THERE ARE ' & FIELD3 & ' ADULTS IN THE ROOM!'

CREATEFIELD THISROW='THIS ROW NUMBER IS ' & FIELDROW

To further define the field (ie: make it numeric, or justified right, or change to a numeric picture), you should then use the FORMATCOLUMN extension. In the following example, the CREATEFIELD creates a 4th field and then it is formatted to money/currency using @n-11.2

SELECT FIRSTNAME,ROOMRATE,ENDRATE FROM FOLIOHD

CREATEFIELD Difference=FIELD3-FIELD2

FORMATCOLUMN 4,COLUMNPICTURE=@N-11.2