Forms and Spreadsheet

Note: images to be added when format is approved!

PQLForms and Data Grid Control

SIR Advanced Training New York 2005

David Baxter SIR

Introduction

PQLForms and Spreadsheet are tools for viewing and updating your data. This paper will cover each of these then show how they can be used together and with normal PQL as well.

This course will not go into great detail on the syntax of the forms commands. This information is included with the sample files.

PQLForms

PQLForms is a set of extensions to VisualPQL to create and run sets of linked, interactive dialogs for data entry, retrieval and update. A complete set of dialogs is a single VisualPQL routine known as a Form.

Writing PQLForms

The PQLForms commands define what variables are on each dialog, how they are displayed and edited, how the dialog is to look, and how dialogs are linked together. A PQLForm has built in buttons and associated logic to allow the user to navigate through a set of records and to display, edit and insert data according to the database description. A developer can use all standard VisualPQL commands as necessary and these are executed at appropriate places in the form.

The commands look a bit like old forms with PQL syntax:

FORM

. SCREEN RECORD EMPLOYEE

.   GENERATE

. END SCREEN

END FORM

FORM UPDATE LABELS

. SCREEN RECORD EMPLOYEE

.   FIELD ID

.   FIELD NAME

.   FIELD GENDER

. END SCREEN

END FORM

Running PQLForms

PQLForms are translated into regular PQL. You can get compile errors at the forms translation phase or possibly at the retrieval translation phase. You can save a copy of the regular PQL using the /PQLFILE keyword on the FORM command.

Start form translation

End form translation

Start retrieval translation

Start retrieval execution

Generating PQLForms

A default form can be generated for a database and can be used directly to view, create, or delete records.

WRITE SCHEMA

          FILENAME = "COMP_FORM.pql" /

          PQLFORMS /

From the SIR Menus, choose Database, Write Schema… or Data, Forms…

Forms Painter

The forms painter can be used to change the layout and functionality of the PQLForm program

Embedded PQL

Some clauses take embedded PQL code. These include the SCREEN clauses:

  INITIAL (PQL code)

  READ    (PQL code)

  SELECT  (PQL code)

  WRITE   (PQL code)

And the FIELD clauses:

  EDITIN (PQL commands)

  EDITOUT (PQL commands)

and FBUTTON’s

  ACTION (PQL commands) 

Enter normal PQL with commands separated with “;”. The “;” is converted to new line in the generated PQL commands.

Subroutines

A PQLForm can be re-compiled every time it is used or the compiled version of the form can be saved as an executable member on the procedure file. A PQLForm can also be compiled and saved as a sub-routine and can then be executed as part of another PQLForm or standard retrieval. A PQLForm is run in the same way as any other VisualPQL routine either directly or from a menu.

FORM SUBROUTINE SYSTEM.EMPLOYEE REPLACE

WRITE SCHEMA FILENAME = … / PQLFORMS / SUBS /

Form subroutines can be executed:

FBUTTON ACTION (EXECUTE SUBROUTINE EMPLOYEE) PROMPT "EMPLOYEE SCREEN"

Calling Forms from PQL

RETRIEVAL NOAUTOCASE

EXECUTE SUBROUTINE SYSTEM.EMPLOYEE

Spreadsheet

The Spreadsheet control is used by both the DBMS Spreadsheet Command and the PQL GRID command.

Spreadsheet DBMS Command

SIR SPREADSHEET 

         RECTYPE = EMPLOYEE /UPDATE

Alternative data entry and modification

Control displayed variables and order with /VARIABLE=(list)

GRID PQL Command

The GRID command displays data in a spreadsheet format and can be used to display arrays of data and to accept back changes. The grid is a special dialog with predefined buttons and a grid of data. The columns are array variables; the rows are occurrences in the array. The grid displays very quickly and essentially has no size limitations beyond those imposed by processing very large arrays.

You could use VARGET and VARPUT functions to transfer database data to an array, start the GRID controls and then copy the array data back to the database.

Examples

The included example file RECGRID.PQL creates two subroutines that use the grid control to display all the OCCUP and REVIEW records for a single case. This example lets you modify the data and update the database.

Forms and Spreadsheet

The subroutines above can be executed from PQLForms using an FBUTTON:

FBUTTON ACTION (

    EXECUTE SUBROUTINE SYSTEM.OCCGRID (ID) RETURNING (RC)

   ) PROMPT "Occupation Records"             

The attached GRIDFORM.PQL is a working example of this.

PQL and Forms and Spreadsheet

A PQL Dialog program can call a PQLForm stored as a Subroutine. The DIALOGFRM.PQL shows how this can be done. When this program is run it displays a dialog. Enter an ID or letter in the top section and press Find, Select an Employee to edit their details and press one of the action buttons to change their OCCUP and REVIEW records.