RE: [Openroad-users] Emulating a database combo lookup in OpenRoad...
Utley, Ian
Hi Gary.
You don't mention which version of OpenROAD you are using, but you
can trap the END and ESC keys by using the KeyDown event instead of the
KeyPress Event.
Additionally, have you considered retrieving all the place names
with 1 SQL statement, and display them within an OptionField which has
the OF_EDITABLE Property?
Ian.
-----Original Message-----
From: openroad-us...@peerlessit.com
[mailto:openroad-us...@peerlessit.com] On Behalf Of Gary
Hansford
Sent: 18 March 2004 14:26
To: 'openroa...@peerlessit.com'
Subject: [Openroad-users] Emulating a database combo lookup in
OpenRoad...
All,
I'm after some better ideas on how I can emulate a database combo
lookup
(if at all under OpenRoad)... The following script works fine until the
user
presses the cursor keys and/or the END key, I cannot trap these keys so
it
all goes to pot... Has anyone tried anything similar ? I suppose raising
a
DAR might be an idea (e.g. Database Lookup on COMBO, allow END etc keys
to
be trapped ) but i'm looking for a solution sooner...
Cheers for any ideas you can come up with
Gary Hansford
--------------------------------
Field Script:
This field script, searches a database table as you key characters (to
try
it out create three entry fields on a form, one called whatever you want
-
varchar, 2nd l_v_search - varchar, 3rd l_i_keypress - integer). Change
the
SQL to your table name and associate the following script with the first
field: -
/*
** Handle the Key presses
*/
ON KEYPRESS =
declare
l_kpi_keyinfo = KeyPressInfo;
l_i_length = integer not null;
l_v_place = VARCHAR(256) not null;
enddeclare
{
l_kpi_keyinfo = KeyPressInfo(CurFrame.MessageObject);
l_i_keypress = l_kpi_keyinfo.ANSIcode;
if l_i_keypress = 0
then
l_i_keypress = -1;
endif;
/*
** Delete Pressed ?
*/
IF l_kpi_keyinfo.ANSIcode = 8
THEN
l_i_length = FIELD($_CurFld).FirstMarked - 2;
IF l_i_length > 0
THEN
$_CurFld = LEFT(l_v_search, l_i_length);
l_v_search = $_CurFld + '%';
ELSE
$_CurFld = '';
l_kpi_keyinfo.ANSICode = 0;
RESUME;
ENDIF;
/*
** Tab Into Field ?
*/
ELSEIF l_kpi_keyinfo.ANSIcode = 9
THEN
$_CurFld = '';
l_v_search = '';
RESUME;
/*
** Store KeyPress and Search...
*/
ELSE
IF FIELD($_CurFld).FirstMarked > 0
THEN
l_v_search = LEFT($_CurFld,FIELD($_CurFld).FirstMarked - 1) +
VARCHAR(UPPERCASE(l_kpi_keyinfo.Character)) +
'%';
ELSE
l_v_search = $_CurFld +
VARCHAR(UPPERCASE(l_kpi_keyinfo.Character))
+ '%';
ENDIF;
ENDIF;
/*
** If we have something in the field lets perform the search
*/
l_v_place ='';
l_i_length = LENGTH(l_v_search);
SELECT place as :l_v_place
FROM places
WHERE place LIKE :l_v_search
ORDER BY l_v_place;
COMMIT;
IF l_v_place != ''
THEN
$_CurFld = :l_v_place;
FIELD($_CurFld).MarkSubText(startposition=l_i_length);
l_kpi_keyinfo.ANSICode = 0;
ELSE
CurFrame.Beep();
l_kpi_keyinfo.ANSICode = 0;
l_v_search = $_CurFld + '%';
ENDIF;
}
/*
** Field Has been exited
*/
on EXIT =
{
IF CurFrame.ReasonCode = RC_DOWN
THEN
IF LENGTH(l_v_search) < 4
THEN
MESSAGE 'Will need at least 3 characters please...';
ELSE
MESSAGE 'You Pressed the <DOWN> key'+HC_NEWLINE+
'At this point I would bring up a new frame with a
list'+
' of available towns beginning with '+ l_v_search +
' and you''d pick one...';
ENDIF;
RESUME;
ENDIF;
l_v_search = $_CurFld;
$_CurFld='';
}
_______________________________________________
Openroad-users mailing list
Openroa...@peerlessit.com
http://www.peerlessit.com/mailman/listinfo/openroad-users
_______________________________________________
Openroad-users mailing list
Openroa...@peerlessit.com
http://www.peerlessit.com/mailman/listinfo/openroad-users
Gary Hansford
Gary Hansford
Hiya Ian,
I cannot find the "KeyPress" event listed in the OpenRoad Language
Reference (PFD- 4.1 - SP1) or is this a "stealth" event !! ;-)
The reason I don't want to load the rows into an OptionField is because
there are around 6 million of them and the PC starts to gasp and wheeze.. [
did I mention its a Microsoft Platform :-) ]
Thanks for the help...
Gary
-----Original Message-----
From: Utley, Ian [mailto:Ian....@ca.com]
Sent: 18 March 2004 16:06
To: Gary Hansford; openroa...@peerlessit.com
Subject: RE: [Openroad-users] Emulating a database combo lookup in
OpenRoad...
Hi Gary.
You don't mention which version of OpenROAD you are using, but you
can trap the END and ESC keys by using the KeyDown event instead of the
KeyPress Event.
Additionally, have you considered retrieving all the place names
with 1 SQL statement, and display them within an OptionField which has
the OF_EDITABLE Property?
Ian.
Utley, Ian
Hi Gary,
I believe that the KeyDown event has been in the product since the
MR release (which is now SP1), as this is the minimum version required
by the DemoPack 2 Jigfall game, which uses KeyDown events; so it should
work with your release. The SP2 documentation does contain details about
the KeyDown event and is available on the CA support site
http://supportconnect.ca.com
I have cut and pasted the basics below:
KeyDown Event
-============-
The KeyDown event is triggered whenever OpenROAD receives a
WM_KEYDOWN or WM_SYSKEYDOWN message. The event is not queued but
is generated immediately. Information about the key is stored in a
KeyDownData object, which is delivered to the 4GL program in the
MessageObject attribute of the FrameExec.
The 4GL event code has the opportunity to stop further processing of the
WM_KEYDOWN or WM_SYSKEYDOWN message by modifying the KeyDownData object.
To discard the message, set the KeyDownData.VirtualKey to zero.
The PrintScrn key does not cause a KeyDown event, and no other events
are generated in conjunction with a KeyDown event. In particular, the
KeyDown event does not cause either a SetValue or a ChildSetValue event.
See the "Virtual Key Values" appendix in the Language Reference Guide
for a listing of virtual key codes and their values.
In the following example processing of the 'A' Key is stopped:
/* Note: the virtual key code for the A key is 65. */
on KeyDown =
declare
KeyData = KeyDownData default NULL;
VirtualKey = integer not null;
enddeclare
begin
KeyData = KeyDownData(CurFrame.MessageObject);
VirtualKey = KeyData.VirtualKey;
/* No further processing of the A key */
if (VirtualKey = 65) then
KeyData.VirtualKey = 0;
endif;
end;
Hope this helps.
Ian.
Gary Hansford
Dear All,
Thanks to all for your help, thought you'd all be interested in my semi
competed code (and it works by jolly). As you key characters it will fetch
the first matching row from the database. It allows SQL wild cards '%' and
'_' to be used (hit <return> for them to take effect) and limits database
searching so they only take place when "real" characters are pressed (not
when END/LEFT/RIGHT/DELETE pressed).
For a quick demo here's all you have to do: -
1) Create a single line varchar entry field on your frame, and change the
keypress/entry/exit event names to your field (mines field1).
2) Change the sql in procedure "findplaces" as you wish. Later you could
enhance the whole routine when <down> is pressed by making a list appear
just under the entry field (p_i_expand_search = TRUE) instead of my little
popup message.
3) If you're on 4.1/SP2 you can remove the Macro assignments for $VK_ and
use the real values...
I'm hoping some of you may find this useful and want to enhance & share
further...
Cheers
Gary
PS: No comments on coding practice please, i'm only human !
------------------------------------------------------
#define $VK_RETURN '13'
#define $VK_HOME '8'
#define $VK_BACK '8'
#define $VK_DOWN '40'
#define $VK_DELETE '46'
#define $VK_LEFT '37'
#define $VK_RIGHT '39'
#define $VK_END '35'
#define $VK_HOME '36'
initialize()=
declare
l_keypress_field = EntryField DEFAULT NULL,
ph_keystroke = ProcHandle DEFAULT NULL,
ph_findplaces = ProcHandle DEFAULT NULL,
keystroke = Procedure RETURNING NONE,
findplaces = Procedure RETURNING INTEGER,
enddeclare
{
ph_keystroke = CurFrame.Scope.GetProcHandle('keystroke');
ph_findplaces = CurFrame.Scope.GetProcHandle('findplaces');
}
/*
* ----------------------------------------------------
* FRAME EVENT: KEYDOWN
* ----------------------------------------------------
*/
ON CHILDKEYDOWN =
declare
enddeclare
begin
ph_keystroke.Call(
p_entryfield = l_keypress_field,
p_searchprochandle = ph_findplaces
);
end;
/*
* ----------------------------------------------------
* FIELD EVENT: ENTRY - field1
* ----------------------------------------------------
*/
ON ENTRY field1 =
declare
enddeclare
begin
l_keypress_field = FIELD(field1);
end;
/*
* ----------------------------------------------------
* FIELD EVENT: KEYPRESS - field1
* ----------------------------------------------------
*/
ON KEYPRESS field1 =
declare
enddeclare
begin
ph_keystroke.Call(
p_entryfield = l_keypress_field,
p_searchprochandle = ph_findplaces
);
end;
/*
* ----------------------------------------------------
* FIELD EVENT: EXIT - field1
* ----------------------------------------------------
*/
ON EXIT field1 =
declare
enddeclare
begin
l_keypress_field = NULL;
end;
/*
/*--------------------------------------------------------------------------
--*/
/* Local Procedure : KeyStroke
*/
/* Params in : p_entryfield Entryfield user is typing into
*/
/* p_searchprochandle Procedure that performs search
*/
/* p_i_expand_search Execute <down-arrow> processing
*/
/* Params Byref : <none>
*/
/* Returncode : <none>
*/
/*--------------------------------------------------------------------------
--*/
/* Description : Handles KEYPRESS and KEYDOWN keystrokes for the
field */
/* pointed to by p_entryfield. Builds up a LIKE search
*/
/* field in the l_v_search field and then initiates the
*/
/* p_searchprochandle proc to perform the actual
search. */
/* The p_searchprochandle proc should return ER_OK if a
*/
/* row is found OR ER_FAIL for any other reason.
*/
/*
*/
/* The p_i_expand_search is used to request the routine
*/
/* immediately call the prochandle passing the search
*/
/* field and the p_i_expand_search set to true.
*/
/*--------------------------------------------------------------------------
--*/
PROCEDURE keystroke (
p_entryfield = EntryField,
p_searchprochandle = ProcHandle,
p_i_expand_search = INTEGER NOT NULL DEFAULT FALSE,
) =
DECLARE
l_KeypressInfo = KeyPressInfo DEFAULT NULL;
l_KeyDownData = KeyDownData DEFAULT NULL;
l_i_length = INTEGER NOT NULL;
l_v_search = VARCHAR(256) NOT NULL;
l_i_retcode = INTEGER NOT NULL;
l_v_value = VARCHAR(256) NOT NULL;
l_v_dbval = VARCHAR(256) NOT NULL;
l_i_dosearch = INTEGER NOT NULL DEFAULT FALSE;
ENDDECLARE
BEGIN
/*
** Ensure we have an EntryField and ProcHandle to Despatch
*/
IF p_entryfield IS NULL
OR p_searchprochandle IS NULL
THEN
RETURN;
/*
* Key Pressed Inside the Entry Field
*/
ELSEIF CurFrame.MessageObject.isA(class=KeyPressInfo) = TRUE
THEN
l_KeyPressInfo = KeyPressInfo(CurFrame.MessageObject);
IF l_KeyPressInfo.ANSIcode = 9
THEN
l_v_search='';
p_entryfield.setFieldValue(value='');
RETURN;
ELSE
p_entryfield.getFieldValue(value=byref(l_v_value));
IF p_entryfield.FirstMarked > 0
THEN
l_v_search = LEFT(l_v_value,p_entryfield.FirstMarked - 1)
+ VARCHAR(UPPERCASE(l_KeyPressInfo.Character))
+ '%';
ELSE
l_v_search = l_v_value
+ VARCHAR(UPPERCASE(l_KeyPressInfo.Character))
+ '%';
ENDIF;
l_KeyPressInfo.ANSICode = 0;
ENDIF;
/*
* Trap KeyDown System Events
*/
ELSEIF CurFrame.MessageObject.isA(class=KeyDownData) = TRUE
THEN
l_KeyDownData = KeyDownData(CurFrame.MessageObject);
p_entryfield.getFieldValue(value=byref(l_v_value));
IF l_KeyDownData.VirtualKey = $VK_BACK
OR l_KeyDownData.VirtualKey = $VK_DELETE
OR l_KeyDownData.VirtualKey = $VK_LEFT
THEN
l_i_length = p_entryfield.FirstMarked - 1;
IF l_i_length > 1
THEN
p_entryfield.MarkSubText(startposition=l_i_length);
l_v_search = LEFT(l_v_value,p_entryfield.FirstMarked - 1)
+ '%';
ELSEIF l_i_length < 0 AND p_entryfield.CursorPosition > 1
THEN
l_i_length = p_entryfield.CursorPosition - 1;
p_entryfield.MarkSubText(startposition=l_i_length);
l_v_search = LEFT(l_v_value,p_entryfield.FirstMarked - 1)
+ '%';
ELSE
p_entryfield.setFieldValue(value='');
p_entryfield.UnmarkAllText();
l_v_search = '';
ENDIF;
l_KeyDownData.VirtualKey = 0;
RETURN;
/*
* Right Key Pressed
*/
ELSEIF l_KeyDownData.VirtualKey = $VK_RIGHT
THEN
l_i_length = p_entryfield.FirstMarked + 1;
IF l_i_length > 1 AND l_i_length < LENGTH(l_v_value)
THEN
p_entryfield.MarkSubText(startposition=l_i_length);
l_v_search = LEFT(l_v_value,p_entryfield.FirstMarked - 1)
+ '%';
ELSE
p_entryfield.UnmarkAllText();
l_v_search = l_v_value + '%';
l_i_length = LENGTH(l_v_value)+1;
ENDIF;
p_entryfield.CursorPosition = l_i_length;
l_KeyDownData.VirtualKey = 0;
RETURN;
/*
* End Key Pressed
*/
ELSEIF l_KeyDownData.VirtualKey = $VK_END
THEN
p_entryfield.UnmarkAllText();
p_entryfield.CursorPosition = LENGTH(l_v_value) + 1;
l_v_search = l_v_value + '%';
l_KeyDownData.VirtualKey = 0;
RETURN;
/*
* Home Key Pressed
*/
ELSEIF l_KeyDownData.VirtualKey = $VK_HOME
THEN
p_entryfield.setFieldValue(value='');
p_entryfield.UnmarkAllText();
l_v_search = '';
l_KeyDownData.VirtualKey = 0;
RETURN;
/*
* Return Key Pressed (whilst in a Search Mode ?)
*/
ELSEIF l_KeyDownData.VirtualKey = $VK_RETURN
AND (l_v_value LIKE '%\%%' ESCAPE '\' OR
l_v_value LIKE '%\_%' ESCAPE '\')
THEN
l_KeyDownData.VirtualKey = 0;
l_i_dosearch = TRUE;
l_v_search = l_v_value + '%';
/*
* Down Key Pressed ?
*/
ELSEIF l_KeyDownData.VirtualKey = $VK_DOWN
THEN
l_KeyDownData.VirtualKey = 0;
p_i_expand_search = TRUE;
/*
** Some Other Key was pressed
*/
ELSE
RETURN;
ENDIF;
/*
* Some Other Event Called us
*/
ELSE
RETURN;
ENDIF;
/*
** Expand Search Requested ?
*/
IF p_i_expand_search = TRUE
THEN
p_entryfield.getFieldValue(value=byref(l_v_value));
IF p_entryfield.FirstMarked > 0
THEN
l_v_search = LEFT(l_v_value,p_entryfield.FirstMarked - 1) + '%';
ELSE
l_v_search = l_v_value + '%';
ENDIF;
ENDIF;
/*
** If we have something in the field lets perform the search
*/
l_i_length = LENGTH(l_v_search);
IF l_i_dosearch = FALSE
THEN
IF l_v_search LIKE '%\%%\%' ESCAPE '\' OR
l_v_search LIKE '%\_%\%' ESCAPE '\'
THEN
p_entryfield.setFieldValue(value=LEFT(l_v_search,l_i_length-1));
p_entryfield.CursorPosition = l_i_length;
RETURN;
ENDIF;
ENDIF;
l_i_retcode = p_searchprochandle.Call(
p_v_search = l_v_search,
b_v_match = BYREF(l_v_dbval),
p_i_expand_search =
p_i_expand_search);
IF l_i_retcode = ER_OK
THEN
p_entryfield.setFieldValue(value=l_v_dbval);
p_entryfield.MarkSubText(startposition=l_i_length);
ELSE
CurFrame.Beep();
ENDIF;
RETURN;
END
/*
/*--------------------------------------------------------------------------
--*/
/* Local Procedure : FindPlaces
*/
/* Params in : p_v_search Clause to LIKE with
*/
/* p_i_expand_search Popup List should appear
*/
/* Params Byref : b_v_match Return Matching Rows
*/
/* Returncode : ER_OK Match was found
*/
/* ER_FAIL No Match Found / Error
*/
/*
*/
/* If p_i_expand_search is set the routine will popup a
*/
/* list of matching items and allow the user to select
*/
/*--------------------------------------------------------------------------
--*/
/* Description : Called by the KeyStroke Search Routine to perform
the */
/* database lookup.
*/
/*--------------------------------------------------------------------------
--*/
PROCEDURE findplaces (
p_v_search = VARCHAR(256) NOT NULL,
p_i_expand_search= INTEGER NOT NULL DEFAULT FALSE,
b_v_match = VARCHAR(256) NOT NULL
) =
DECLARE
ENDDECLARE
BEGIN
IF p_i_expand_search = TRUE
THEN
MESSAGE 'Would Perform a List Popup Using '+p_v_search;
RETURN ER_FAIL;
ELSE
b_v_match ='';
EXECUTE IMMEDIATE
'SELECT FIRST 1 place FROM places WHERE place LIKE '
+ HC_QUOTE
+ p_v_search
+ HC_QUOTE
INTO :b_v_match;
COMMIT;
IF b_v_match != ''
THEN
RETURN ER_OK;
ELSE
RETURN ER_FAIL;
ENDIF;
ENDIF;
END