/*
-----------------------------------------------------------------------------
autofill_entryfield.cc
Authors..: Omar Mohammed, Ken Mayer
Credit...: Mervyn Bick, Ronnie MacGregor, Akshat Kapoor
Credit for various bits of help in places on this,
it's a group effort, really.
Date.....: April, 2020
May, 2020 (fix in autofill_entryfield2)
Version..: 1.5
Notes....: This file contains two auto fill entryfield classes.
The first is based on work by Omar Mohammed, and is designed
to act very much like the way Excel does when typing a value
in a column and fills in possible choices as you type. You can
then accept them and move on or continue typing -- this uses
just a dataLink to the field in question.
The second is designed to work a little differently, in that it
requires the dataLink, but it can uses a different table as a
sort of dictionary. However, it is not designed to add to the
"dictionary" table. Therefore it has what would be called if
using a combobox or listbox, a dataSource as well as a dataLink.
Rather than using the values in the "dataLinked" column, it
uses the values from another table and field ... This one is
subclassed from the first one ...
Both versions should work with an ADO database as well as a
BDE-based database, local tables, or otherwise. See the
usage explanations in the documentation below.
NOTE: ADO Code tested with MySQL by Ken Mayer.
-----------------------------------------------------------------------------
*/
/*
-----------------------------------------------------------------------------
Class....: autofill_entryfield
Author...: Omar Mohammed
Date.....: March 18, 2020 -- modified from an example posted by Omar in
the dBASE newsgroups, by Ken Mayer, attempting to turn this
into a "generic" custom class.
Version..: 1.1 April 20, 2020 -- updated to deal with issues having
to deal with data modules, minor corrections in
code, but necessary, and a few issues with ADO databases.
Notes....: This is an autofill entryfield like the way Excel (or Google,
sort of) uses to fill the rest of the word for you by searching
the current contents of the table in the dataLinked field.
The one thing this doesn't do is provide, if there are more than
one match, a list of options to select from. That would require
a combobox control and adding calls to load the dataSource for
the combobox, etc.
Usage....: set procedure to :dUFLP:autofill_entryfield.cc
** on a form in the form designer, place this control,
** and set the dataLink like you would any other entryfield.
** that's it. When run, adding a new record or editing an
** existing one will load information and as the user types
** attempt to find a matching value ...
If this is an ADO database, you need to set the following
in the form's onOpen event handler (ensures we use
dBASE's ADO objects as needed):
function form_onOpen()
// any other code you might need
form.AUTOFILL_ENTRYFIELD1.ado := true
return
-----------------------------------------------------------------------------
*/
class autofill_entryfield( oParent ) of entryfield( oParent ) custom
with (this)
key = class::autofill_KEY
onKey = class::autofill_ONKEY
onOpen = class::autofill_OnOpen
value = ""
endwith
// ---------------------
// custom properties
// ---------------------
// this.ado should be set true in form's onOpen, canOpen, or
// other appropriate method/event handler
this.ado = false // is this an ADO database?
// this information is found in the onOpen event handler:
this.fieldName = ""
this.tableName = ""
this.dataBase = ""
function onDesignOpen( lFromPalette )
// only execute this code when dropped onto a form from
// the palette ... Set default height/width properties:
if not lFromPalette
return
endif
if this.form.metric # 0 and form.metric # 6
msgbox( "Please use either form metric of 0 (Chars) or "+;
"6 (Pixels) for this control." )
return
endif
if this.form.metric == 0 // chars
this.height = 1
this.width = 28
else this.form.metric == 6 // pixel
this.height = 22
this.width = 196
endif
return
function autofill_onOpen()
// check to see if dataLink is set, if not,
// return error and close form
if this.dataLink == ""
msgbox( "Entryfield must be dataLinked to a field in the table!",;
"autoFill error", 16 )
form.close()
endif
// if we do have a dataLink, let's work with it ...
this.fieldName = this.dataLink.fieldName
// parent = fieldArray
// parent = rowset
this.tableName = this.dataLink.parent.parent.tableName
// -----------------------------------------------------------------------
// do we have a database?
// parent = fieldArray
// parent = rowset
// parent = query
this.databaseName = this.dataLink.parent.parent.parent.database.databaseName
this.database = null // the default database is _app.databases[1] which
// points to the current folder, it's really not
// needed for this. It doesn't have a value for
// the databaseName property
if not empty( this.databaseName )
this.database = this.dataLink.parent.parent.parent.database
endif
// -----------------------------------------------------------------------
// if we have a backslash in the table path, the odds are that the
// table is a local table, and for our purposes we want to strip
// out the path to the table (returned by the tableName property
// of the rowset object above) ...
if "\" $ this.tableName
this.tableName = right( this.tableName, ;
len( this.tableName ) - rat("\", this.tableName ) )
endif
// put the lookup query code in its own method
class::setupQuery()
// set up the ListBox control:
class::setupListBox()
return
// eof: autofill_onOpen()
function setupQuery
// define the database and the query here, so it is in one place.
// query to search for field uses current table and field that we're
// dataLinked to in a separate query.
if not this.ado
this.q = new query()
else
this.q = new adoquery()
endif // this.ado
// if we have a databaseName we should set the reference to
// the database object:
if not empty( this.databaseName )
this.q.database = this.database // set above
endif
// -----------------------------------------------------------------------
// Tricky trying to insert the properties into the select statement,
// basically we're limiting the query to a single field, the one we're
// dataLinked to, but in order to set the sequence we're using a
// calculated field (upper(fieldname)) and using that in the order
// by clause. Weirdly, this does come together and appears to work
// properly.
// * There are quotes around the field name in case it has spaces in it,
// or it's a reserved word, or ...
// * DISTINCT is used in case of duplicate values in the field, this will
// shorten the listing to a single record for each variation ... (SQL
// version of a UNIQUE index option with dBASE tables)
// (Thanks to Mervyn Bick for helping with this monster)
// modified to use parameter for the field ...
// Ronnie MacGregor suggested trying without quotes, as it was causing
// issues with MySQL and ADO, and it seems to work fine ... oy! (the
// original version had quotes around field and table names)
this.q.sql := [SELECT DISTINCT t.] + this.fieldName + [ as lookup_field, upper( t.] +;
this.fieldName + [ ) as upper_field] +;
[ FROM ] + this.tableName + [ t]+ ;
[ where upper( t.] + this.fieldName +[ ) LIKE :partial]+;
[ order by upper_field]
// parameters are the best way to handle this, so we can just do
// a requery when we need it, rather than redefining the SQL statement
// each time the user types something. If this is an ADO query,
// then we need to do this a little different:
if not this.ado
this.q.params["partial"] = "%"
else
// modified to use the .value property, because ADO requires it
// Akshat Kapoor reminded me of this:
this.q.parameters["partial"].value = "%"
endif
this.q.active := true
return
// eof: setupQuery()
function setupListbox
// use a listbox and display options if there's more than 1 ...
// the listbox will display under the entryfield, but to
// keep everything encapsulated we are assigning the object reference
// as a custom property of this control (allows for multiple instances
// of this control on a form).
oThis = this // reference the entryfield control
this.list = new listbox( oThis.parent )
this.list.visible := false // only display as needed
// top, left and width properties:
this.list.top := oThis.top + oThis.height // directly under entryfield
this.list.left := oThis.left // left of entryfield
this.list.width := oThis.width // width of entryfield
// suggested by Akshat Kapoor:
this.list.height := oThis.height*3 // 3 times height of entryfield
// height is complicated (get position of the bottom of the listbox,
// if it is > than the bottom of the form, we need to bring it up ...):
if this.list.top+this.list.height > oThis.parent.height
// simplified, based on suggestion by Akshat Kapoor:
this.list.height = oThis.parent.height - this.list.top
/*
if oThis.parent.metric == 6
this.list.height := oThis.parent.height - this.list.height - 30 // pixels
else
this.list.height := oThis.parent.height - this.list.height - 1 // char
endif
*/
endif
this.list.af_field = oThis // Can't use "parent" property (read-only)
this.list.onSelChange := {; this.af_field.value = this.value; this.visible=false }
return
// eof: setupListbox
function autofill_key( nChar, nPosition, bShift, bControl)
// the key event fires for every keystroke (well, almost, there are
// some that don't seem to fire it).
// this routine is checking for backspace/delete and/or spaces
// being typed as part of the value. In the case of backspace/delete
// we want to ignore it in the autofill_onKey() event handler below.
// In the case of a space, we want to make sure it's part of the
// text ...
this.bspa = false
// 8 = backspace
// 127 = delete
if (nchar == 127 or nchar == 8)
this.bspa = true
endif
this.space = false
// 32 = space
if( nChar == 32 )
this.space = true
endif
return true
// eof: autofill_key()
function autofill_onKey(nChar, nPosition, bShift, bControl)
// the onKey event fires after the key event handler
// if the user pressed Backspace or Delete, return
if this.bspa = true
return
endif
// Omar's code forced upper case, but in a lot of cases
// upper case is not always desirable for the entryfield value
partial_field = this.value.rightTrim().leftTrim()
// if the user typed a space (using the example data, "NEW "
// for example), we want to add it into the text so we're not
// having issues with ... well, everything.
if this.space
partial_field += " "
endif
// by setting the query in the onOpen event handler, we need to
// use a parameter and a requery:
if not this.ado
this.q.params["partial"] = upper( partial_field ) + "%"
else
this.q.parameters["partial"].value = upper( partial_field ) + "%"
endif
// restate the sql property of the query with new values
// for parameter "partial":
this.q.requery()
// if there is a value/match found there will be at least one
// record in the rowset
if this.q.rowset.count() > 0
// get field but trim it ...
full_field = this.q.rowset.fields["lookup_field"].value.rightTrim().leftTrim()
// if the count is greater than 1
if this.q.rowset.count() > 1
// build an array for the listbox of all "matches"
this.aData = new array()
for i = 1 to this.q.rowset.count()
this.aData.add( this.q.rowset.fields["lookup_field"].value.rightTrim().leftTrim() )
this.q.rowset.next()
next
// assign listbox dataSource
this.list.dataSource := "array this.aData"
// make it visible:
this.list.visible := true
else // if it is 1 or less then don't display the list anymore
this.list.visible := false
endif // this.q.rowset.count() > 1
else // we have one or zero matches
// Altered code a bit, April 19, 2020
// If rowset.count is 0 or 1, turn off the list, because
// it might still be there from an earlier match
// AND set the "full_field" to the value of the partial (what the
// user typed)
this.list.visible := false
full_field = partial_field
endif // this.q.rowset.count() > 0
// for this we want to highlight (the selected value in the entryfield)
// everything to the right of where the cursor was when the user
// was typing, so that if they type more characters the selected text
// disappears or updates appropriately.
if len(full_field) > 0
// put text into entryfield
this.value = full_field
// how far back from the right side do we need to highlight
// the text?
backstr = len(full_field) - len(partial_field)
// go to end of value displayed
this.keyboard("{end}")
// loop backward to highlight text to the right of where
// the user had been typing
for x = 1 to backstr
this.keyboard("{shift+leftarrow}")
endfor // x = 1 ...
endif // len(full_field) > 0
return // eof: autofill_onKey()
endclass
/*
-----------------------------------------------------------------------------
Class....: autofill_entryfield2
Author...: Ken Mayer
Date.....: March 19, 2020
Version..: 1.1 April 19, 2020 -- updated to deal with issues having
to deal with data modules, minor corrections in
code, but necessary
1.2 May 20, 2020 -- fixed an issue with two different
databases being used for the data, the code
in autofill_onOpen was overwriting the object's
databaseName property and then we couldn't find
the correct table ...
Notes....: This is a variation on autofill_entryfield (above), in that
it is designed to perform an autoFill field, but rather than
using data in the same field of the table as the source,
it uses data from a different table, a sort of dictionary
and is very much like working with a combobox.
Usage....: set procedure to :dUFLP:autofill_entryfield.cc
** on a form in the designer, set an instance of
** autofill_entryfield2 on the form. Using the Inspector,
** open the form's onOpen event handler in the Source Code
** Editor (click "Events" in the inspector, find onOpen,
** click the tool button). Add the following code
** where appropriate in the function that is created
** (the following assumes the standard naming on a form
** in the form designer, adding a numeric value to the
** end of the name of the control):
** form.autofill_entryfield21.dataBaseName := "Name_of_Database"
** form.autofill_entryfield21.tableName := "MyLookupTable"
** form.autofill_entryfield21.fieldName := "MyLookupField"
** If there is no database (the tables are in the same folder
** as the application) you can leave it out. Otherwise,
** please define the database name, even if it is the same
** as the field the entryfield is dataLinked to.
Example -- in the same folder:
function form_onOpen()
form.AUTOFILL_ENTRYFIELD21.tableName := "Autofill_Cities"
form.AUTOFILL_ENTRYFIELD21.fieldName := "CityNames"
return
Example 2 -- in a database:
function form_onOpen()
form.AUTOFILL_ENTRYFIELD21.databaseName := "MyApp_Data"
form.AUTOFILL_ENTRYFIELD21.tableName := "Autofill_Cities"
form.AUTOFILL_ENTRYFIELD21.fieldName := "CityNames"
return
If you are using an ADO database for this, then you need
to also set the following in the code above (either example),
as this ensures we are using the correct ADO Data objects:
form.AUTOFILL_ENTRYFIELD21.ado := true
-----------------------------------------------------------------------------
*/
// Subclass from the first control, meaning we don't have to repeat all
// the code that is duplicate (most of it).
class autofill_entryfield2( oParent ) of autofill_entryfield( oParent ) custom
with (this)
value = ""
onOpen = class::autoFill_onOpen
endwith
// custom properties for dataSource
this.ado = false
// see instructions in "USAGE" above in the header,
// for this version these should be set in the form's onOpen
// event handler:
this.databaseName = ""
this.tableName = ""
this.fieldName = ""
function autofill_onOpen()
// check to see if dataLink is set, if not, return error and close form
if this.dataLink == ""
msgbox( "Entryfield must be dataLinked to a field in the table!",;
"autoFill error", 16 )
form.close()
endif
// we need to grab the value from the form's onOpen
// event handler and put it somewhere else.
// problem is that the code below, in this method,
// will overwrite that value otherwise:
if not empty( this.databaseName )
this.lookupDatabase = this.databaseName
endif
if this.tableName == ""
msgbox( "You must define a lookup table!", "autoFill error", 16 )
form.close()
endif
if this.fieldName == ""
msgbox( "You must define a lookup field!", "autoFill error", 16 )
form.close()
endif
// ------------------------------------------------------------
// do we have a database?
// parent = fieldArray
// parent = rowset
// parent = query
if not this.ado
// For ADO this will pull the name of the database from the server,
// which is not what we want for our purposes, as the User BDE
// Database name may not match the name of the database as it is
// stored on the server
this.databaseName = this.dataLink.parent.parent.parent.database.databaseName
endif
this.database = null // the default database is _app.databases[1] which
// points to the current folder, it's really not
// needed for this. It doesn't have a value for
// the databaseName property
if not empty( this.databaseName )
this.database = this.dataLink.parent.parent.parent.database
endif
// if here, call setupQuery() method to actually do the query:
class::setupQuery()
// set up the listbox control
class::setupListBox()
return
// eof: autofill_onOpen()
function setupQuery()
// query to search for field uses database, table and field that is
// passed to the control in form's onOpen method. This is a little
// different from the first autoFill control ...
if not this.ado
this.q = new query()
else
this.q = new adoQuery()
endif
// if we have a databaseName we should set the database object:
if not empty( this.lookupDatabase )
// at this point we need to create the
// database object:
if not this.ado
this.d = new database()
this.d.databaseName := this.lookupDatabase
else // ado:
this.d = new adoDatabase()
this.d.databaseName := this.lookupDatabase
endif // not this.ado
// activate the database object
this.d.active = true
// assign database to query
this.q.database := this.d
endif // not empty( this.lookupDatabase )
? this.d.databaseName
// Tricky trying to insert the properties into the select statement,
// basically we're limiting the query to a single field, the one we're
// dataLinked to, but in order to set the sequence we're using a
// calculated field (upper(fieldname)) and using that in the order
// by clause. Weirdly, this does come together and appears to work
// properly.
// * There are quotes around the field name in case it has spaces in it,
// or it's a reserved word, or ...
// * DISTINCT is used in case of duplicate values in the field, this will
// shorten the listing to a single record for each variation ... (SQL
// version of a UNIQUE index option with dBASE tables)
// (Thanks to Mervyn Bick for helping with this monster)
// Ronnie MacGregor suggested trying without quotes, as it was causing
// issues with MySQL and ADO, and it seems to work fine ... oy! (the
// original version had quotes around field and table names)
this.q.sql := [SELECT DISTINCT t.] + this.fieldName + [ as lookup_field, upper( t.] +;
this.fieldName + [ ) as upper_field] +;
[ FROM ] + this.tableName + [ t]+ ;
[ where upper( t.] + this.fieldName +[ ) LIKE :partial ]+;
[ order by upper_field]
if not this.ado
this.q.params["partial"] = "%" // all rows to start
else
this.q.parameters["partial"].value = "%" // all rows to start
endif // not this.ado
this.q.active := true
return
// eof: setupQuery()
endclass
Warning: Unknown: write failed: No space left on device (28) in Unknown on line 0
Warning: Unknown: Failed to write session data (files). Please verify that the current setting of session.save_path is correct () in Unknown on line 0