Using Rico LiveGrid Forms
OVERVIEW
While this document just refers to ASP, identical functionality is provided
by the PHP and .net plugins.
- create a new ASP file that includes ricoLiveGridForms.vbs
- in the new ASP, define each of the fields in the table to be edited - usually 1-3 lines of code per field
- the resulting ASP not only creates the appropriate grid, but also generates an input form in a hidden div
- the ASP also adds "add/edit/delete record" entries to the popup menu
- when add or edit is chosen, the form is unhidden (and filled in if edit was chosen)
- when the user hits the save button, the form silently posts back to the original ASP
- the ASP saves the data to the database and sends a response back to the client
- 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:
- Create a single instance of the class
set oForm=new TableEditClass
- call the SetTableName method
oForm.SetTableName "customer"
- 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
- if TableName is a view or has no primary key defined, editing will be disabled
- call AddEntryField or AddCalculatedField for each field/column to be displayed
Fields appear in both the table and form views, with the following exceptions:
- entry type is H never gets sent to the client. Writes to the database get the defined default value.
- if FormView field property is set to "exclude", then the field appears in the table only.
- if FormView field property is set to "hidden", then the data is put in a hidden form field.
- Calculated fields appear in the table view only (same as FormView=exclude)
- 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.
- 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:
- full - show full response
- errors - show full response for errors and short response otherwise (default)
- 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:
- "email" - tests for a valid email address
- "float-unsigned" - tests for a valid unsigned floating point (real) number
- "float-signed" - tests for a valid signed floating point (real) number (this is the default when EntryType is "F")
- "int-unsigned" - tests for a valid unsigned integer number
- "int-signed" - tests for a valid signed integer number (this is the default when EntryType is "I")
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'"