CONSTRUCT + TYPE

When you generate the WHERE part of an SQL statement via a CONSTRUCT statement, the runtime needs to know not only the type of database but also the datatype of the field.

The type of database is determined by the current database connection.  It is important that whether using a single database connection model, or a multiple connection model to different types of databases that you switch to the target database before the CONSTRUCT statement.  That is your DATABASE, CONNECT TO or SET CONNECTION statement as appropriate, occurs before the CONSTRUCT statement.  This ensures that the runtime knows what database syntax to generate the where clause for.

The runtime also needs to know the data type of the field.  If a field is defined as formonly, the runtime does not the data type of the field.  It is important that when using formonly, that you also use the TYPE keyword to indicate to the runtime the datatype of the field.  The fact you might be using a widget such as a DATEEDIT is not enough to tell the runtime that the field is a date.  You explicitly need the TYPE keyword (or to not use formonly) so that the runtime knows what datatype the where clause should be generated for.

To illustrate this, I have prepared a little program.  Key things to note

 CONNECT TO ":memory:+driver='dbmsqt'"

… this connects to a SQLite database in memory.  This line is before the CONSTRUCT statement so that the runtime knows I am using SQLite.

In the form, note the definition of the two DATEEDIT fields, one with type date.

DATEEDIT f02 = formonly.field2;
DATEEDIT f03 = formonly.field3 TYPE DATE;

Now if I run the program, click today, and then press OK, note that the where clause generated is …

scr.field1='03/11/2025' and scr.field2='03/11/2025' and scr.field3='2025-11-03'

Note how the where clause generated for  field3  (DATEEDIT with TYPE) is different from the where clause generated for field2 (DATEDIT formonly)).  field3 has the where clause generated using the appropriate syntax for a date  whilst field2 (the DATEEDIT without a TYPE clause) has the same where clause as generated for a simple EDIT of a character field (field1).

You may find that if your codebase uses formonly, that a coding standard to follow is that every formonly field requires the TYPE clause.  Otherwise you run the risk of generating a where clause that does not return the values you expect.

In the past, generic code had difficulties with this as the generic form would be formonly and could not use TYPE as it was not known what the datatype was.  In the 1990’s, the predecessor to fgl_zoom had some hacky code to handle the different datatypes.  With the introduction of generic dialogs and the ability to create forms on the fly, fgl_zoom no longer has this problem as the form is generated formonly with TYPE specified.