Using Rico LiveGrid Forms

OVERVIEW

While this document just refers to ASP, identical functionality is provided by the PHP and .net plugins.

  1. create a new ASP file that includes ricoLiveGridForms.vbs
  2. in the new ASP, define each of the fields in the table to be edited - usually 1-3 lines of code per field
  3. the resulting ASP not only creates the appropriate grid, but also generates an input form in a hidden div
  4. the ASP also adds "add/edit/delete record" entries to the popup menu
  5. when add or edit is chosen, the form is unhidden (and filled in if edit was chosen)
  6. when the user hits the save button, the form silently posts back to the original ASP
  7. the ASP saves the data to the database and sends a response back to the client
  8. the client displays a success or failure message in the bookmark area

The following two diagrams shows how requests and responses flow with LiveGrid Forms pages, and with LiveGrid pages without forms. While the two diagrams are labelled ASP/PHP, they also apply to .net (except that there is no dbClass2 in .net).

USAGE

This class provides all of the functions necessary to view, insert, update, and delete records on a single table. An ASP script should follow these steps:

  1. Create a single instance of the class
    set oForm=new TableEditClass
  2. call the SetTableName method
    oForm.SetTableName "customer"
  3. optionally set the CanAdd, CanEdit, CanDelete, ConfirmDelete, and/or RecordName properties if desired
    oForm.options("canAdd")=CanAdd
    oForm.options("canEdit")=CanModify
    oForm.options("canDelete")=CanDelete
    
  4. if TableName is a view or has no primary key defined, editing will be disabled
  5. call AddEntryField or AddCalculatedField for each field/column to be displayed Fields appear in both the table and form views, with the following exceptions:
  6. After each call to AddEntryField or AddCalculatedField, the other methods and properties can be invoked to control how the field is presented to the user (e.g. SortAsc). Calls to these properties/methods apply only to the field most recently added.
  7. call DisplayPage - this displays the grid or executes the database update
    oForm.DisplayPage

TABBED FORMS

Forms with multiple panels/tabs are fully supported using the Rico.TabbedPanel class. Simply call AddPanel before calling AddEntryField to define the fields for that panel. For example:

' these fields appear on the first panel
oForm.AddPanel "Panel #1"
oForm.AddEntryField "field1", "Field #1", "T", ""
oForm.AddEntryField "field2", "Field #2", "T", ""
oForm.AddEntryField "field3", "Field #3", "T", ""

' these fields appear on the second panel
oForm.AddPanel "Panel #2"
oForm.AddEntryField "field4", "Field #4", "T", ""
oForm.AddEntryField "field5", "Field #5", "T", ""
oForm.AddEntryField "field6", "Field #6", "T", ""

Alternatively, the ColGroupIdx property can be set for each field as it is defined. In this case, AddPanel can be called at any time prior to DisplayPage.

' these fields appear on the first panel
oForm.AddEntryField "field1", "Field #1", "T", ""
oForm.CurrentField("ColGroupIdx")=0
oForm.AddEntryField "field2", "Field #2", "T", ""
oForm.CurrentField("ColGroupIdx")=0
oForm.AddEntryField "field3", "Field #3", "T", ""
oForm.CurrentField("ColGroupIdx")=0

' these fields appear on the second panel
oForm.AddEntryField "field4", "Field #4", "T", ""
oForm.CurrentField("ColGroupIdx")=1
oForm.AddEntryField "field5", "Field #5", "T", ""
oForm.CurrentField("ColGroupIdx")=1
oForm.AddEntryField "field6", "Field #6", "T", ""
oForm.CurrentField("ColGroupIdx")=1

oForm.AddPanel "Panel #1"
oForm.AddPanel "Panel #2"

FORM METHODS

AddPanel "Panel Heading"
Defines the heading for a tabbed panel on the input form.
DisplayPage
Displays the grid or updates the database depending on the value of "action".
DefineAltTable (TableName, FieldList, FieldData, Delim)
Function that returns a TabId to be used in subsequent AltTable calls. Defines a secondary table to store additional, related fields. Key field(s) in main table must also exist in AltTable. FieldList & FieldData are delimited strings that define additional, constant values/functions to be stored in the secondary table. Delim specifies the delimiter character used in FieldList & FieldData. FieldList & FieldData must contain the same number of delimited entries.
genXHTML
Call to generate pure XHTML output.
SetDbConn (dbcls)
Specifies the instance of dbClass to be used. If a global instance named oDB exists, then it will be used without this method having to be called.

FORM PROPERTIES

All LiveGrid options are supported as properties, in addition to these which are specific to LiveGrid Forms.

action (read only)
specifies the current action being performed: table, ins, upd, del
gridVar (read only)
returns the name of the client-side LiveGrid object
bufferVar (read only)
returns the name of the client-side LiveGrid Buffer object
AutoInit
automatically initialize the grid (create the data rows) default is true
InitScript (read only)
returns the javascript code (as a string) to initialize the grid (use when AutoInit is false)
TableFilter
specifies a where clause to be used in table view (optional)
// only show records for the logged in user
$oForm->TableFilter = "userid=$myuserid";
canAdd
allow user to add new records, defaults to true
canEdit
allow user to edit records, defaults to true
canDelete
allow user to delete records, defaults to true
canClone
allow user to clone records (edit existing record but save as new), defaults to false
formView
Extend the grid with LiveGrid Forms -- data entry form is created, add/edit/delete items are added to the grid menu, etc. Default is true in ASP and PHP, false in .net.
updateURL
post updates back to this location, defaults to the page that generated the grid
ConfirmDelete
flag specifying whether a confirmation popup should be displayed after the user clicks the delete button, defaults to true (see also ConfirmDeleteCol)
DebugFlag
displaying debugging messages, defaults to false
RecordName
string to customize add, edit, and delete title tags, defaults to "Record"
maxDisplayLen
Text box width. default is 20.
TableName (write only)
the table or view to be displayed/edited (required)
TableSelectNew
String used to identify when a user has selected to create a new value for an EntryType N field. Default is "___new___".
showSaveMsg
Disposition of database update responses:
When using tabbed panels on the input form:
panelWidth
Width of tabbed panels in pixels. Default is 500.
panelHeight
Height of tabbed panels in pixels. Default is 200.
hoverClass
CSS class when hovering over panel tab. Default is "tabHover".
selectedClass
CSS class when panel tab is selected. Default is "tabSelected".

FORM EVENTS

It is possible to hook into several form events.

formOpen
Fires when the input form is displayed.
oForm.options("formOpen")=
  "alert('Questions? Please call the support desk.');"
formClose
Fires right after the input form is closed.
onSubmitResponse
Fires after a form has been sent to the server and a response has been received and processed.

FIELD DEFINITION-METHODS

AddEntryField (ColumnName, Heading, EntryTypeCode, DefaultValue)
Adds a new column to the grid and a new entry field to the popup form in ASP and PHP.
ColumnName
column name in the database table (does not support blanks or any name that would require square brackets in SQL, e.g. [Apr 2005])
Heading
name that appears on the grid column's heading and also on the popup form
EntryTypeCode
string containing a code that controls how the column is displayed on the input form
  • S: Display this column as a drop-down select list during data entry. Values may be specified using the "SelectValues" or "SelectSql" options. If neither is specified, then the values for the column are obtained using a "select distinct" query.
  • R: Same as "S", except the items are displayed using radio buttons.
  • SL,RL: Same as S & R, except that a lookup value is displayed in table view (uses query specified by SelectSql). Typically used on columns that are foreign keys. SQL to get the display value is specified using the "SelectSql" option.
  • CL: Same as "SL", except that the value is selected using a custom control (such as the Rico Tree control). The SelectCtl option must be assigned the id of the custom control.
  • N: Same as "S", but allows the user to create a new value. Typically used without the "SelectValues" or "SelectSql" options.
  • H: column is hidden from the user (DefaultValue will be stored in the table on adds and edits)
  • D: this is a date field (blanks allowed if column allows nulls)
  • TS: timestamp, not shown on entry form, writes current time to database on inserts and updates
  • I: integer number (blanks allowed if column allows nulls and required is false)
  • F: floating-point number (blanks allowed if column allows nulls and required is false)
  • B: non-blank text field (user gets a popup message in form view when clicking save and the field is empty)
  • T: standard text field (blanks allowed)
  • TA: text area field
  • tinyMCE: rich text edit field using the tinyMCE library.
DefaultValue
column's default value in the form view

The equivalent to AddEntryField() in .net is to declare column fields as part of the markup. The "ColData" attribute contains the default value. Here is an example from ex2edit.aspx:

<Rico:Column runat='server' heading='Order#' width='60' 
             ColName='OrderID' EntryType='B' ColData='<auto>' />
AddEntryFieldW (ColumnName, Heading, EntryTypeCode, DefaultValue, ColWidth)
Same as AddEntryField except an extra parameter is added for column width (in pixels).
AddCalculatedField (ColumnFormula, Heading)
ColumnFormula is any valid SQL expression or subquery. If the subquery needs to reference a column in the table being displayed, then the column name should be prefaced with the alias "t." Calculated fields will be displayed in table view, but not in form view.
AddFilterField (ColumnName, FilterValue)
Only display records where the contents of ColumnName=FilterValue. This becomes a hidden field (entry type H).
ConfirmDeleteCol (ConfirmDeleteColumn in .net plug-in)
The text content of the column will be included in delete confirmation messages
SortAsc
In table view, sort by this column in ascending order (applies to most recently added field)
SortDesc
In table view, sort by this column in descending order (applies to most recently added field)

FIELD DEFINITION-PROPERTIES

All LiveGrid column properties are supported, in addition to these which are specific to LiveGrid Forms.

AddQuotes
When false, the column value will be left unquoted when inserting or updating the database. Default is false for entry type TS, and true for all other entry types. This makes it possible to populate columns with SQL function calls. For example:
oForm.AddEntryField "LastEditUser","","H","suser_sname()"
oForm.CurrentField("AddQuotes")=false
oForm.AddEntryField "LastEditDate","","H","getdate()"
oForm.CurrentField("AddQuotes")=false
required
Boolean value that specifies whether the input field may be left empty (default: false if column allows nulls and EntryType is not "B", true otherwise).
noFormBreak
Boolean value that when true, specifies that this entry field should placed on the same form row as the last field (default: false - each form field is placed on a separate row).
AltTable (TabId)
Specifies that the field should be stored in an alternate table TabId should be the value returned by a previous call to DefineAltTable
TxtAreaRows
For columns with entry type "TA", this is # of rows to display in the textarea when in form view (default 4)
TxtAreaCols
For columns with entry type "TA", this is # of columns to display in the textarea when in form view (default 80)
FilterFlag
If true, then the grid is filtered by the default value for this column (default=false)
Help
Creates a title tag containing the specified text (form view only). So if the user hovers over the field label, they will see this text as balloon help. For example:
oForm.CurrentField("Help")="Date must be entered in mm/dd/yyyy format"
pattern
A string containing a regular expression. User entries will be checked to ensure they match the pattern specified. There are a few special values:
It is recommended that a Help entry be included whenever a pattern is specified. If the field fails validation, the help text will be included in the error message presented to the user. For example:
oForm.CurrentField("Help")="Enter date as mm/dd/yyyy"
oForm.CurrentField("pattern")="^\\d{1,2}/\\d{1,2}/\\d{4}$"
min/max
Specifies the minimum/maximum allowable values for fields with EntryType "I", "F", and "D". For example:
oForm.AddEntryField "field1", "Field #1", "I", "0"
oForm.CurrentField("min")=1
oForm.CurrentField("max")=10
oForm.CurrentField("Help")="Enter a value between 1 and 10"

oForm.AddEntryField "field2", "Field #2", "D", Date()
oForm.CurrentField("min")="2000-01-01"
oForm.CurrentField("max")="2099-12-31"
oForm.CurrentField("Help")="Enter a value in the 21st century!"
InsertOnly
Only write this field to the database when peforming an insert (default=false).
oForm.AddEntryField "CreateDate","","H","getdate()"
oForm.CurrentField("AddQuotes")=false
oForm.CurrentField("InsertOnly")=true
UpdateOnly
Only write this field to the database when peforming an update (default=false).
oForm.CurrentField("UpdateOnly")=true
ReadOnly
If true, data is displayed on the entry form but cannot be changed, text is gray (default=false). Does not apply to entry types of S, SL, N, R, RL -- use objTE.CurrentField("FormView")="hidden" instead.
SelectValues
Specifies the choices the user sees in form view for EntryTypes of N, S, and R. If supplied, then this should be a string of comma-separated values. For example:
oForm.CurrentField("SelectValues")="Y,N"
SelectSql
Specifies the SQL select statement to use for EntryTypes of SL, CL, and RL. The select statement should return 2 columns: the first being the code and the second being the text value/description. For example:
oForm.CurrentField("SelectSql")="select ID,Name from Customers"
SelectFilter
SelectSql serves 2 purposes. First, it is used to retrieve the appropriate data to display in the grid. Second, it is used to populate the values in the select box (SL) or radio buttons (RL) on the pop-up form. In some cases, you may want these to be different. In the SelectSql example above, we are retrieving customer name. But let's say that our Customers table has a "CreditHold" field and we want to disable the selection of customers on credit hold in the form view, but still display them in the grid. This is where SelectFilter comes in:
oForm.CurrentField("SelectFilter")="CreditHold='NO'"