Binding multi-value long text columns (Functions/Subroutines/Programs)
Created at 16 DEC 1997 03:01PM
Introduction
OpenInsight's EditTable control has limitations when supporting large amounts of textual data in multi-value fields:
1) Each cell has a 1000 character-limit; (the "Data length" is set per column in the EditTable properties dialog in the Form Designer)
2) Editing large amounts of text is difficult within a cell
3) The EditTable can not display multiple lines of text within a cell
If an EditTable column is bound to a multi-valued text field, that text field will be truncated to the "Data length" for the column when a row is saved. The EditTable is a good tool for displaying related (ie. associated) multi-values (AMV groups), but long text columns should not be bound in the EditTable. Instead, with long text, the EditTable should be used to display the first portion of the first line of the text, allowing the user to view the first part of each text value with its related data.
This document will describe a method for displaying the first portion of the first line of multi-valued, multi-line text in an EditTable, allowing editing and viewing of the entire text in a separate EditBox, while storing the entire multi-valued text in an invisible bound control. Using a small amount of event code, the bound control will reflect inserts and deletes in the AMV group and will reflect modifications made to the text in theEditBox.
Selecting the Right Control
It is already clear that the EditTable is not the right control for displaying, storing, and editing long and/or multi-line text. The suggested control for displaying and editing multi-line text is the EditBox and the suggested control for storing (ie. binding) the multi-value, multi-line text is theEditLine.
The EditBox control supports multi-line text editing in a manner similar to the Windows Notepad application. It is the best means for displaying and editing large amounts of multi-line text in OpenInsight. For this type of use, the most effective styles for the EditBox are auto-vscrolland the vertical scroll bar, which allow the user to quickly navigate the text in the EditBox using the mouse drag action or the scroll bar. The auto-hscroll and horizontal scroll bar styles should not be used, so that the text will automatically word-wrap. Optionally, the EditBox can be defined as read-only, supporting display but not modification.
Two different properties are used with the EditBox control depending on how multiple lines are to be delimited:
1) TEXT property: Multiple lines are delimited by CR/LF (carriage return and line feed, ASCII characters 13 and 10)
2) TEXTVAL property: Multiple lines are delimited by text marks (@TM)
Advanced Revelation uses text marks to delimit multiple lines of text in multi-value, multi-line descriptions, so for shared data, use the TEXTVAL property. For this reason, the examples in this document use text marks to delimit multi-line descriptions.
The EditLine control is used for binding the multi-value, multi-line text for several simple reasons:
1) It does not care about multi-line delimiters because it is single-line oriented
2) It uses slightly less resources and is slightly quicker than the EditBox
3) It has roughly the same storage limit as the EditBox (~32k), although both can handle up to 64k of data under some conditions
Implementation
The following tasks summarize all of the different events that must be handled to implement this solution:
1) When the form is created, the relationships of the bound control, the linked control, and the EditTable must be defined
2) When a READ event is performed, the linked control and the display values in the EditTable must be refreshed
3) When a different row is selected in the EditTable, the value in the linked control must be refreshed
4) When a row is inserted or deleted in the EditTable, the value in the bound control must remain in synch with the other related fields
5) When the text value is modified by the user, the value must be stored in the bound control and the new value must be displayed in the EditTable
To represent the above items, create the following insert record:
[<PRE>][<CODE>]compile insert ManageText_Equates
declare function ManageText
declare subroutine ManageText
* Usage:
* Initialize a form to store text in the EditLine specified
* by BoundCtrl, to display and allow editing in the EditBox
* specified by LinkedCtrl, and to display the first portion
* of each text value in the column Col of EditTable:
*
* ManageText(MT_INIT$, BoundCtrl, LinkedCtrl, EditTable, Col)
* After the form reads, update the display of the linked
* and EditTable controls:
*
* * in READ event
* Forward_Event()
* ManageText(MT_READ$, CtrlEntID)
* return 0
* When the current position in the EditTable changes:
*
* ManageText(MT_POSCHANGED$, CtrlEntID)
* When a row is inserted into the EditTable:
*
* ManageText(MT_INSERTROW$, CtrlEntID)
* When a row is deleted from the EditTable:
*
* ManageText(MT_DELETEROW$, CtrlEntID)
* On LOSTFOCUS from the linked EditBox (or on the CHANGED
* event to force redisplay of the edit table as the user
* types the text)
*
* ManageText(MT_CHANGED$, CtrlEntID)
equ MT_INIT$ to "INITIALIZE"
equ MT_READ$ to "READ"
equ MT_POSCHANGED$ to "POSCHANGED"
equ MT_INSERTROW$ to "INSERTROW"
equ MT_DELETEROW$ to "DELETEROW"
equ MT_CHANGED$ to "CHANGED"[</CODE>][</PRE>]
The implementation is contained in the following function:
[<PRE>][<CODE>]function ManageText(Instruction, CtrlEntID, LinkedCtrl, EditTable, Col)
********************************************
* This function is provided as example code.
*
* Due to formatting limitations of HTML, this
* function will not display with the intended
* formatting. A text file with the intended
* formatting is available at the end of this
* document.
********************************************
declare subroutine Set_Property
declare function Get_Property, Send_Message
$insert Logical
$insert ManageText_Equates
equ MT_LINKINFO$ to "@LINKS" ;* window property used to store link info
equ MT_LINKBIND$ to 1 ;* bound controls
equ MT_LINKTBL$ to 2 ;* linked edit tables (associated to MT_LINKBIND$)
equ MT_LINKCOL$ to 3 ;* edit table columns (associated to MT_LINKBIND$)
equ MT_LINKMAX$ to 4 ;* max col data length (associated to MT_LINKBIND$)
equ MT_LINKEDIT$ to 5 ;* linked edit controls (associated to MT_LINKBIND$)
Ret = TRUE$
begin case
case Instruction = MT_INIT$ ; gosub Initialize
case Instruction = MT_READ$ ; gosub Read
case Instruction = MT_POSCHANGED$; gosub PosChanged
case Instruction = MT_INSERTROW$ ; gosub InsertRow
case Instruction = MT_DELETEROW$ ; gosub DeleteRow
case Instruction = MT_CHANGED$ ; gosub Changed
end case
return Ret
***************
* link controls
***************
Initialize:
* verify parameters
Ctrls = CtrlEntID: @rm: LinkedCtrl: @rm: EditTable: @rm: EditTable
Props = "TYPE" : @rm: "TYPE" : @rm: "TYPE" : @rm: "LIMIT"
Vals = Get_Property(Ctrls, Props)
Types = field(Vals, @rm, 1, 3)
Limit = field(Vals, @rm, 4, 4)
convert @rm to "," in Types
if (Types = "EDITBOX,EDITBOX,EDITTABLE" or Types = "EDITFIELD,EDITBOX,EDITTABLE") and Col >= 1 and Limit<1> >= Col then
* get existing link information
WinID = CtrlEntID [1,"."]
Links = Get_Property(WinID, MT_LINKINFO$)
* update link info
Links<MT_LINKBIND$, -1> = CtrlEntID
Links<MT_LINKTBL$ , -1> = EditTable
Links<MT_LINKCOL$ , -1> = Col
Links<MT_LINKMAX$ , -1> = Send_Message(EditTable, "COLCHARS", Col)
Links<MT_LINKEDIT$, -1> = LinkedCtrl
Set_Property(WinID, MT_LINKINFO$, Links)
end else
Ret = FALSE$
end
return
*************************************************
* post-read processing - populate linked controls
*************************************************
Read:
* get link info
WinID = CtrlEntID
Links = Get_Property(WinID, MT_LINKINFO$)
* try to avoid multiple set_property calls
CacheCtrls = ""
CacheProps = ""
CacheVals = ""
* populate each edit table control
cLinks = count(Links<MT_LINKTBL$>, @vm) + (Links<MT_LINKTBL$> # "")
for i = 1 to cLinks
* extract link info
BoundCtrl = Links<MT_LINKBIND$, i>
EditTable = Links<MT_LINKTBL$ , i>
Col = Links<MT_LINKCOL$ , i>
MaxLen = Links<MT_LINKMAX$ , i>
LinkedCtrl = Links<MT_LINKEDIT$, i>
* get text, number of values, current value
Ctrls = BoundCtrl: @rm: EditTable: @rm: EditTable
Props = "TEXT" : @rm: "LIMIT" : @rm: "SELPOS"
Vals = Get_Property(Ctrls, Props)
Text = field(Vals, @rm, 1)
cRows = field(Vals, @rm, 2)<2>
MV = field(Vals, @rm, 3)<2>
* row number of at least 1
if MV else
MV = 1
end
* set display text in the edit table
for iRow = 1 to cRows
* set the display text for the row
CacheCtrls := @rm: EditTable : @rm: EditTable
CacheProps := @rm: "ACCESSPOS" : @rm: "ACCESSDATA"
CacheVals := @rm: Col: @fm: iRow: @rm: Text<1,iRow> [1,MaxLen] [1,\0D\]
* avoid list of property values getting too large
if len(Vals) > 32000 then
gosub FlushProps
end
next iRow
* set display/edit-able text in the edit box control
* and store current MV position
CacheCtrls := @rm: LinkedCtrl: @rm: LinkedCtrl
CacheProps := @rm: "TEXT" : @rm: "@MV"
CacheVals := @rm: Text<1,MV>: @rm: MV
* avoid list of property values getting too large
if len(Vals) > 32000 then
gosub FlushProps
end
next i
* set remainder of properties
gosub FlushProps
return
FlushProps:
if len(Vals) then
Set_Property(CacheCtrls [2,99999], CacheProps [2,99999], CacheVals [2,99999])
CacheCtrls = ""
CacheProps = ""
CacheVals = ""
end
return
**************************************************
* edit table pos changed; redisplay lined edit box
**************************************************
PosChanged:
* get link info and see if the MV pos actually changed
EditTable = CtrlEntID
WinID = CtrlEntID [1,"."]
Ctrls = WinID : @rm: EditTable: @rm: EditTable
Props = MT_LINKINFO$: @rm: "SELPOS" : @rm: "PREVSELPOS"
Vals = Get_Property(Ctrls, Props)
Links = field(Vals, @rm, 1)
MV = field(Vals, @rm, 2)<2>
PrevMV = field(Vals, @rm, 3)<2>
if MV # PrevMV then
* determine linked controls
cLinks = count(Links<MT_LINKTBL$>, @vm) + (Links<MT_LINKTBL$> # "")
for i = 1 to cLinks
if Links<MT_LINKTBL$, i> = EditTable then
* extract link info
BoundCtrl = Links<MT_LINKBIND$, i>
LinkedCtrl = Links<MT_LINKEDIT$, i>
* get text
Text = Get_Property(BoundCtrl, "TEXT")
* set display/edit-able text in the edit box control
* and store current MV position
Ctrls = LinkedCtrl: @rm: LinkedCtrl
Props = "TEXT" : @rm: "@MV"
Vals = Text<1,MV>: @rm: MV
Set_Property(Ctrls, Props, Vals)
end
next i
end
return
**************************************
* row was inserted into the edit table
**************************************
InsertRow:
* get link info
WinID = CtrlEntID [1,"."]
Links = Get_Property(WinID, MT_LINKINFO$)
* determine linked controls
EditTable = CtrlEntID
cLinks = count(Links<MT_LINKTBL$>, @vm) + (Links<MT_LINKTBL$> # "")
for i = 1 to cLinks
if Links<MT_LINKTBL$, i> = EditTable then
* extract link info
BoundCtrl = Links<MT_LINKBIND$, i>
LinkedCtrl = Links<MT_LINKEDIT$, i>
* get text, number of values, current value
Ctrls = BoundCtrl: @rm: EditTable
Props = "TEXT" : @rm: "SELPOS"
Vals = Get_Property(Ctrls, Props)
Text = field(Vals, @rm, 1)
MV = field(Vals, @rm, 2)<2>
* insert a blank value into the text
Text = insert(Text, 1, MV, 0, "")
* set display/edit-able text in the edit box control
Ctrls = BoundCtrl: @rm: LinkedCtrl
Props = "TEXT" : @rm: "TEXT"
Vals = Text : @rm: ""
Set_Property(Ctrls, Props, Vals)
end
next i
return
*************************************
* row was deleted from the edit table
*************************************
DeleteRow:
* get link info
WinID = CtrlEntID [1,"."]
Links = Get_Property(WinID, MT_LINKINFO$)
* determine linked controls
EditTable = CtrlEntID
cLinks = count(Links<MT_LINKTBL$>, @vm) + (Links<MT_LINKTBL$> # "")
for i = 1 to cLinks
if Links<MT_LINKTBL$, i> = EditTable then
* extract link info
BoundCtrl = Links<MT_LINKBIND$, i>
LinkedCtrl = Links<MT_LINKEDIT$, i>
* get text, number of values, current value
Ctrls = BoundCtrl: @rm: EditTable
Props = "TEXT" : @rm: "SELPOS"
Vals = Get_Property(Ctrls, Props)
Text = field(Vals, @rm, 1)
MV = field(Vals, @rm, 2)<2>
* delete the text value
Text = delete(Text, 1, MV, 0)
* set display/edit-able text in the edit box control
Ctrls = BoundCtrl: @rm: LinkedCtrl: @rm: LinkedCtrl
Props = "TEXT" : @rm: "TEXT" : @rm: "@MV"
Vals = Text : @rm: Text<1,MV>: @rm: MV
Set_Property(Ctrls, Props, Vals)
end
next i
return
*************************
* linked text was changed
*************************
Changed:
* get link info
WinID = CtrlEntID [1,"."]
Links = Get_Property(WinID, MT_LINKINFO$)
* determine which controls the edit control is linked to
LinkedCtrl = CtrlEntID
locate LinkedCtrl in Links<MT_LINKEDIT$> using @vm setting Pos then
* extract link info
BoundCtrl = Links<MT_LINKBIND$, Pos>
EditTable = Links<MT_LINKTBL$ , Pos>
Col = Links<MT_LINKCOL$ , Pos>
MaxLen = Links<MT_LINKMAX$ , Pos>
* get text (ie. all values), modified text, current MV position
Ctrls = BoundCtrl: @rm: LinkedCtrl: @rm: LinkedCtrl
Props = "TEXT" : @rm: "TEXT" : @rm: "@MV"
Vals = Get_Property(Ctrls, Props)
Text = field(Vals, @rm, 1)
Entry = field(Vals, @rm, 2)
MV = field(Vals, @rm, 3)
* check if text changed
if Entry # Text<1,MV> then
Text<1,MV> = Entry
Ctrls = BoundCtrl: @rm: EditTable : @rm: EditTable
Props = "TEXT" : @rm: "ACCESSPOS" : @rm: "ACCESSDATA"
Vals = Text : @rm: Col: @fm: MV: @rm: Entry [1,MaxLen] [1,\0D\]
Set_Property(Ctrls, Props, Vals)
end
end
return[</CODE>][</PRE>]
The following events show how to implement the long text management in an OpenInsight form. The example is a purchase order entry form with an EditTable, PO_DTL, that contains purchase order line information. The fourth column of PO_DTL is the purchase order line description. At one time, the description column was bound to the purchase order file, but descriptions longer than 1000 characters were truncated by the control. To solve this, two controls (and the event code below) were added to the form. The first new control, PO_DESC, is a hidden EditLine that is bound to the purchase order line description; (the EditTable column that was bound to the description was unbound). The second control, EB_DESC, is the EditBox used to display and edit each purchase order line description. These events , for the most part, could have been implemented much easier as QuickEvents, but Basic+ code is shown to assist in the understanding of the processing and how the functionality would be added to existing event cod
e.
Form CREATE event:
[<PRE>][<CODE>]$insert ManageText_Equates
* PO_DESC - bound editline
* EB_DESC - edit box to link
* PO_DTL - edit table with multi-value data (linked text is displayed in column 4)
ManageText(MT_INIT$, @window: ".PO_DESC", @window: ".EB_DESC", @window: ".PO_DTL", 4)
return 1[</CODE>][</PRE>]
Form READ event:
[<PRE>][<CODE>]declare subroutine Forward_Event
declare function Get_EventStatus
$insert ManageText_Equates
Forward_Event()
if Get_EventStatus() else
ManageText(MT_READ$, CtrlEntID)
end
return 0[</CODE>][</PRE>]
EditTable POSCHANGED event:
[<PRE>][<CODE>]declare subroutine Forward_Event
$insert ManageText_Equates
Forward_Event()
ManageText(MT_POSCHANGED$, CtrlEntID)
return 0[</CODE>][</PRE>]
EditTable INSERTROW event:
[<PRE>][<CODE>]$insert ManageText_Equates
ManageText(MT_INSERTROW$, CtrlEntID)
return 1[</CODE>][</PRE>]
EditTable DELETEROW event:
[<PRE>][<CODE>]$insert ManageText_Equates
ManageText(MT_DELETEROW$, CtrlEntID)
return 1[</CODE>][</PRE>]
EditBox CHANGED or LOSTFOCUS event:
[<PRE>][<CODE>]declare subroutine Msg
$insert ManageText_Equates
ManageText(MT_CHANGED$, CtrlEntID)
return 1[</CODE>][</PRE>]
_________
See Also: TEXT and TEXTVAL properties, POSCHANGED, INSERTROW, and DELETEROW events.
Source code: