1. science
2. medicine
3. surgery
4. cosmetic surgery

§ Sean Thrower, Actian
Cats and Dogs!
How
to domesticate OpenROAD
§ June 2013
list-handling to obey your every
Disclaimer
This document is for informational purposes only and is subject to change at any
time without notice. The information in this document is proprietary to Actian and no
part of this document may be reproduced, copied, or transmitted in any form or for
any purpose without the express prior written permission of Actian.
This document is not intended to be binding upon Actian to any particular course of
business, pricing, product strategy, and/or development. Actian assumes no
responsibility for errors or omissions in this document. Actian shall have no liability
for damages of any kind including without limitation direct, special, indirect, or
consequential damages that may result from the use of these materials. Actian
does not warrant the accuracy or completeness of the information, text, graphics,
links, or other items contained within this material. This document is provided
without a warranty of any kind, either express or implied, including but not limited to
the implied warranties of merchantability, fitness for a particular purpose, or noninfringement.
Confidential © 2012 Actian Corporation
2
Abstract
§ To handle sets of business items, OpenROAD provides you with
two representations: customized and type specific Arrays (cats);
and dynamic, optimized, generically structured ChoiceLists
(dogs). Cats and dogs are complementary: cats provide focused
browsing and editing via TableFields; dogs provide at-a-glance
selection via five different types of ChoiceField; between them they
cover the range of multi-item handling that most applications
require.
§ To get the best results, dogs in particular must be approached in
the right way. This presentation begins with a common
requirement – how to provide row-specific dropdown lists in
TableFields – to illustrate some key techniques; then follows with
advanced techniques for building lists from a range of source types
(database, files, datastrings, arrays, master-lists). The session will
also give you the opportunity to raise and discuss other list-
Contents
§ Cats and Dogs
§ Row-specific dropdown lists in table fields
§ Techniques for building lists
§ Other list-handling issues (Q&A)
• And incidentally, plenty of coding techniques!
Cats ...
§ To handle sets of business
items, OpenROAD
provides you with CATS customized and typespecific arrays ...
... and Dogs
§ And DOGS – dynamic,
optimized, generically
structured choicelists ...
Cats ...
§ Cats provide
• focused browsing and editing
via
• TableFields.
... Dogs
§ Dogs provide
• at-a-glance selection
via
• 5 different types of
ChoiceField.
You need both ...
§ Select ...
.. And sometimes both at once!
§ ... and Browse/Update
Row-specific dropdown lists
§ Today is about training your dog.
• Can you get it to do this?
Row-level dropdown
in a table field.
Row-specific dropdown lists
• And this?
Row-level, row-specific
dropdown in a table field.
Image dropdown lists
• And this?
Row-level image dropdown
in a table field.
Implementable in OpenROAD 5.0 +
§ This is not new capability. It takes a little invention, but you
can do this in any supported version of OpenROAD
§ The example code here will make use of
§ StringObject Split and Join methods
§ StringHashTables
• as the code is much simpler using these features, added in version
5.1
What do we need?
Final appearance, in editor
§ Mobile dropdown button
• SubForm valuesholder, containing
§ ButtonField arrowbtn with dropdown icon
§ OptionField valuesopt
• The button and option field are aligned TopRight, with the button on
top
§ You may need to wrap the button in a nameless StackField, in earlier
OpenROAD releases
§ Button field has RequireRealField=TRUE
What do we need?
§ Procedures,
to manage the dropdown and list behaviour
§ PopulateDropList
§ ShowArrowDropPanel
§ ApplyArrowDropSelection
§ TargetInfo
• None of these procedures live in the frame; instead you put them in
a service object ...
What do we need?
§ The service object
• Let’s call it
§ ServiceObject
• Containing
§ ArrowDropDown
• A setup method for the mobile dropdown
§ plus the 4 procedures on the previous page
(it will also contain setup methods and procedures for other services you might
implement)
What do we need?
§ Events,
to trigger the necessary behaviours:
§ Initialize
• setup the mobile dropdown
§ ChildMouseEnter tbl (or column)
• populate and show the appropriate dropdown list
§ ChildClick valuesholder
• select an item from the option field list
§ Scroll tbl, MouseExit tbl, WindowResized
• hide the mobile dropdown
§ ChildEntry tbl
• change the dropdown’s background on a highlighted row
4GL code:
Events
Setup Events:
Initialize
Initialize =
declare
/*
** Declare the service object and the procedure (prochandle) interface
*/
SO
= ServiceObject;
PopulateDropList
= ProcHandle default null;
ShowArrowDropPanel
= ProcHandle default null;
ApplyArrowDropSelection = ProcHandle default null;
TargetInfo
= procedure returning Object;
enddeclare
{
/*
** Setup the name and value dropdown
*/
arrowBox = FIELD(valuesholder);
SO.ArrowDropDown();
Populate Events: ChildMouseEnter tbl
On childmouseenter tbl (or tbl[*].<columnname>)
/*
** Get the target information
*/
trigger = curframe.TriggerField;
rc = TargetInfo.Call(trigger=trigger, indx=Byref(indx));
/*
** Populate and show the dropdown panel
*/
rc = PopulateDropList.Call(
frame=curframe, trigger=trigger,
listdefs=listdefs,
list=Byref(list) );
Selection Events: ChildClick valuesholder
On childclick valuesholder
/*
** Get the target cell, record or field the user was working on;
** Set and display the new value
*/
IF CurFrame.TriggerField.IsA(choicefield) = TRUE THEN
rc = ApplyArrowDropSelection.Call(
arrowBox=arrowBox, frame=curframe, target=Byref(item));
ENDIF;
Hide Events: MouseExit tbl ,
Scroll tbl,
On scroll tbl
On mouseexit tbl
WindowResized
On windowresized
/*
** Hide the arrowbox
*/
arrowrow.AllBias = FB_INVISIBLE;
Display Events:
ChildEntry tbl
On childentry tbl
/*
** Match the arrow background to the highlight
** if there is an arrow
*/
arrowrow = ChoiceField(arrowBox.FieldByName('valuesopt'))
.ValueList.ChoiceItems.ClientInteger;
if arrowrow > 0 then
cell = col.CellAttribute(record=arrowrow);
endif;
if arrowrow = row then
arrowBox.FieldByName('arrowbtn').SetAttribute(
BgColor=$HIGHBG; FgColor=$HIGHFG);
elseif cell is not null then
arrowBox.FieldByName('arrowbtn').SetAttribute(
BgColor=cell.BgColor;
FgColor=cell.FgColor);
endif;
4GL code: Methods
ArrowDropDown method:
Sets up the service procedures
§ Contains a text list of the procedure names
§ name1 name2 name3 ...
§ Uses each name to get the procedure’s ProcHandle
reference
§ GetProcHandle(name=name1);
§ Stores each prochandle in the calling frame
• in a variable of the same name.
Now each procedure can be executed from the calling frame, using:
§ Name.Call()
ArrowDropDown method
4GL code ...
Method ArrowDropDown(
caller = ProcExec default null;
//the frame using the service
)=
{
namestring = 'showarrowdroppanel applyarrowdropselection populatedroplist';
names = ToString(text=namestring).Split(delimiter=' ');
for i = 1 to names.LastRow do
name = names[i].Value;
//next name from the namestring
dyn = caller.Scope.CreateDynExpr(string=name);
//has the caller declared it?
if dyn is null then
return FALSE;
//caller is not ready
endif;
dyn.SetValue(value=curmethod.Scope.GetProcHandle(name=name)); //set it
endfor;
return TRUE;
//caller is ready
}
4GL code: Procedures
TargetInfo procedure
Gets essential information about the list target
§ Gets the tablefield associated with the trigger
§ WhichTableField()
§ Gets the tablefield’s:
§ array
§ targeted row (object and index)
§ targeted column
§ targeted cell
§ Returns them all, byref
TargetInfo procedure
4gl code ...
procedure TargetInfo(
trigger
tbl
col
arr
indx
cell
= FormField default null;
//the mouseentered field
= TableField default null;
//the target tablefield - byref
= ColumnField default null;
//the target columnfield - byref
= ARRAY of Object default null; //the target array - byref
= integer not null;
//the target row number - byref
= CellAttribute default null;
//the target cell’s cellattribute - byref
)=
Returns the targeted cell, or record, or field directly,
but all of these are also returned byref, plus column and table field and row number.
TargetInfo procedure
continued ...
/*
** Get the tablefield
*/
tbl = trigger.WhichTableField();
/*
** Get the array
*/
tbl.GetFieldValue(value=Byref(arr));
/*
** Get the row
*/
indx = tbl.WhichRow(trigger);
if indx > 0 then
elseif trigger.IsDescendantOf(tbl.TableBody) = FALSE then
else
/*the "trigger" was the protofield or the column*/
indx = tbl.ActiveRow;
endif;
TargetInfo procedure
continued ...
/*
** Get the column
** (for complex columns, the trigger may not have been named)
*/
col = tbl.TableBody.FieldByName(name=trigger.Name);
if col is null and indx > 0 then
cols = tbl.TableBody.ChildFields;
for i = 1 to cols.LastRow do
if trigger.IsDescendantOf(cols[i]) = FALSE then
continue;
endif;
col = cols[i];
endloop;
endfor;
endif;
TargetInfo procedure
continued ...
/* Get the cell
*/
if indx = 0 or col is null then
elseif col.HasCellAttributes = TRUE then
cell = col.CellAttribute(record=indx);
endif;
/*
** Return the targeted field, cell or record
*/
if cell is not null then
target = cell;
elseif indx > 0 then
target = arr[indx];
else
target = trigger;
endif;
return target;
PopulateDropList procedure
Populates the dropdown with the appropriate list
§ Gets the listholder for the list
§ A cell or row of the table field
§ Accesses a cached list or builds a new one
§ Builds the list based on a listdefinition
§ Keyword + evaluation string
§ References the listholder from the list
§ References the trigger, which may be different
More details later ...
ShowArrowDropPanel procedure
Displays the dropdown arrow
§ Hides the dropdown arrow
§ While the list is being switched and the arrow moved
§ Attaches the correct list
§ Ensures the arrow’s background matches its new location
§ Moves and redisplays the dropdown arrow
Now the dropdown is visible and ready to be clicked
ShowArrowDropPanel procedure
4GL code ...
procedure ShowArrowDropPanel(
ddArrow = SubForm default null;
//the arrow “panel” itself
frame
= FrameExec default null;
//the target frame
trigger
= FormField DEFAULT NULL; //the field that was clicked
list
= ChoiceList default null;
//must be a caller-scope variable
)=
Returns ER_OK or ER_FAIL, plus the attached list itself (byref)
ShowArrowDropPanel procedure
continued ...
/*
** Hide the list while setting up
*/
ddarrow.AllBias = FB_INVISIBLE;
/*
** Attach the list to the dropdown
*/
ddarrow.FieldByName(name='valuesopt').SetAttribute(valuelist=list);
/*
** Get the target information
*/
callproc TargetInfo(trigger=Byref(trigger), tbl=Byref(tbl), cell=Byref(cell));
ShowArrowDropPanel procedure
continued ...
/*
** Match the arrow background to the cell or field it is on
*/
if tbl is not null and cell is not null then
ddarrow.FieldByName(name='arrowbtn').SetAttribute(
BgColor=cell.BgColor, FgColor=cell.FgColor);
else
ddarrow.FieldByName(name='arrowbtn').SetAttribute(
BgColor=trigger.BgColor, FgColor=trigger.FgColor);
endif;
ShowArrowDropPanel procedure
continued ...
/*
** Display the dropdown
*/
ddarrow.SetAttribute(
parentField=CurFrame.TopForm);
ddarrow.SetAttribute(
layersequence=$TOPMOST,
AbsXRight=trigger.AbsXRight,
AbsYTop=trigger.AbsYTop,
AllBias=FB_CHANGEABLE);
return ER_OK;
ApplyArrowDropSelection procedure
Applies the selected value to the target
§ Hides the dropdown
§ Gets the target cell, record or field
§ Applies the selected value to the target
§ Refreshes the target display
§ To show the new value
ApplyArrowDropSelection procedure
4gl code ...
procedure ApplyArrowDropSelection(
arrowBox
frame
target
trigger
= SubForm default null;
= FrameExec default null;
= Object default null;
= FormField default null;
//the dropdown itself
//the target frame
//the target cell, record or field (byref)
//the original trigger (byref)
)=
Returns ER_OK or ER_FAIL, plus the target and the trigger (byref)
ApplyArrowDropSelection procedure
4gl code ...
/*
** Hide the list
*/
arrowBox.AllBias = FB_INVISIBLE;
/*
** Get the owner and trigger of the item the user was working on
*/
list = listFld.ValueList;
lookuphash = list.ClientData;
owner = lookuphash.Find(key='owner');
trigger = lookuphash.Find(key='trigger');
ApplyArrowDropSelection procedure
continued ...
/*
** Get the item the user was working on
** (the field, if owner is a field outside a tablebody; the rowobject,
** if owner is a cell or row)
*/
callproc TargetInfo(trigger=Byref(trigger), tbl=Byref(tbl),
arr=Byref(arr), indx=Byref(entry));
if owner.IsA(class=CellAttribute) = TRUE then
target = arr[entry];
elseif tbl is not null and owner != trigger then
target = owner;
else
target = trigger;
endif;
ApplyArrowDropSelection procedure
continued ...
/*
** Apply the selected value to the target
*/
choice = list.ChoiceItems[ list.IndexByText(textvalue=listFld.CurEnumText) ];
if tbl is not null then
name = tbl.FullName + '[' + varchar(entry) + '].' + trigger.Name;
else
name = trigger.FullName;
endif;
dyn = curframe.Scope.CreateDynExpr(string=name);
ApplyArrowDropSelection procedure
continued ...
case Left(trigger.Datatype, Locate(trigger.Datatype,'(')-1) of
'integer',
'smallint',
'integer8':
dyn.SetValue(value=choice.EnumValue);
'float':
dyn.SetValue(value=Float8(choice.EnumText));
'money':
dyn.SetValue(value=Money(choice.EnumText));
'date':
dyn.SetValue(value=Date(choice.EnumText));
'decimal':
dyn.SetValue(value=Decimal(choice.EnumText,15,8));
'varchar', 'char': dyn.SetValue(value=choice.EnumText);
'stringobject':
dyn.SetValue(value=ToString(text=choice.EnumText));
'bitmapobject': dyn.SetValue(value=choice.EnumBitmap);
default:
return ER_FAIL;
endcase;
ApplyArrowDropSelection procedure
continued ...
/*
** Show the new value
*/
if tbl is not null then
tbl.UpdField(clearhasdatachanged=0);
else
trigger.UpdField(clearhasdatachanged=0);
endif;
return ER_OK;
More about list building
PopulateDropList procedure
Populates the dropdown with the appropriate list
§ Gets the listholder for the list
§ A cell or row of the table field
§ Accesses a cached list or builds a new one
§ Builds the list based on a listdefinition
§ Keyword + evaluation string
§ References the listholder from the list
§ And the trigger, which may be different
PopulateDropList procedure
4GL code ...
procedure PopulateDropList(
frame
= FrameExec default null;
//the target (caller) frame
trigger
= FormField DEFAULT NULL; //the mouse-entered field
listdef
= varchar(256) not null;
//the list definition to populate from
list
= ChoiceList default null;
//must be a caller-scope variable
notcell
= integer not null default FALSE; //whether the listholder is a cell
)=
Returns ER_OK or ER_FAIL, plus the populated list (byref)
PopulateDropList procedure
continued ...
/*
** Get details of the listholder we are getting the list for
** Don't use the default listholder (the cell) ..if notcell was specified
*/
listholder = TargetInfo(trigger=Byref(trigger), tbl=Byref(tbl),
arr=Byref(arr), indx=Byref(indx), cell=Byref(cell));
if listholder is null then
return ER_FAIL;
endif;
if listholder = cell and notcell = TRUE then
listHolder = arr[indx];
endif;
/*
** Determine whether we need to build a list or have one already cached
*/
//not covered in this presentation
PopulateDropList procedure
continued ...
/* Build a dropdown valuelist for the indicated item
*/
choices = list.Choiceitems;
tokens = ToString(text=listdef).Split(delimiter=‘:‘, exactrows=2);
valuekey = tokens[1].Value; valuestring = tokens[2];
case valuekey of
‘SQL’:
//eg. SQL: select id, name, nickname from employee
{
i = 1;
EXECUTE IMMEDIATE :valuestring.Value
into choices[i].EnumValue, choices[i].EnumText, choices[i].EnumDisplay
{
i = i + 1;
}
commit;
}
PopulateDropList procedure
continued ...
‘IN’:
{
//eg. ‘IN: true, false, unknown’
tokens = ToString(text=valuestring).Split(delimiter=‘,’);
FOR i = 1 TO tokens.LastRow DO
value = tokens[ct].Value;
list.AddItem(
EnumValue = list.ChoiceItems.LastRow + 1;
TextValue = value;
DisplayValue = value;
BitmapValue = null);
ENDFOR;
}
...etc. ‘ARRAY’, ‘FILE’, ‘STRING’, ‘PROCEDURE’ can all be sources of lists too
endcase;
PopulateDropList procedure
continued ...
/*
** Add a back link to the list owner and the trigger
** (lookuphash is a StringHashTable)
*/
lookuphash.InsertObject(key='owner', object=listHolder);
lookuphash.InsertObject(key='trigger', object=trigger);
list.ClientData = lookuphash;
/*
** Forget it - if the list is empty
*/
if list.ChoiceItems.LastRow = 0 then
return ER_FAIL;
endif;
return ER_OK;
}
Questions about ...
Row-specific dropdown lists?
Questions about ...
Predefining list definitions?
• IN
• SQL
• ARRAY
• FILE
• STRING
• PROCEDURE
Questions about ...
Cats & Dogs?
Click icon to add picture
dynamic, optimized,
generically structured
choicelists
customized and typespecific arrays
Session Overview
§ Cats and Dogs
§ Row-specific dropdown lists in
table fields
§ Techniques for building lists
§ Other list-handling issues
(Q&A)
• And incidentally, plenty of coding
techniques!
Confidential © 2012 Actian Corporation
56
Thank You
www.actian.com
facebook.com/actiancorp
@actiancorp
Confidential © 2012 Actian Corporation
57