0

Dbgrid (Release 7.3)

Description

The database grid (also called db_grid3) allows you to display as a table data that is in a data table in your project. You can make this displayed table read-only or you can allow users to select from entries in the table. If a user selects and entry from the table, you can pull data from this selection for use in the session. The example grid above contains references relate to the parameters described below.

The LogicNets Release 7.4 version of this part can be viewed here.

Used With

This part can be added to any form or form-node type in projects without a framework and with frameworks such as the Starter App or the Reporting framework. The Easy Customization Starter project available in the Designer includes a db_grid. This part features heavily in reporting applications, too.

Use

To add a database grid to a form node or form part, click the Elements tab of your node and click the Data tables button. Pull down to Display database grid and fill out the parameters based on the information in the Editor Fields table below. 

Editor Fields

In the editor for the db_grid3 part the system provides the following mandatory and optional items.

Tab

Field Name

Description/Use

Type/Options

Optional/Mandatory

Basic Data-object This is the name in which the system will store the table row id of the selected record if the user selects a record from the grid. Data Object Mandatory
Basic Data source(s) This is the name of the data table or tables you want to display as a grid to your users. Click the ... button to select from your project's resources/data folder the table or tables you want to display. Data Table Name Mandatory
Basic Columns This is where you select which columns from your data table that you want to display in your grid. If you click the ellipses button under "Columns", the system will select all of the columns. You can also add each column individually. Note: The table selection fields allow you to use complex SQLite queries, such as count, sum, case when, and other constructions. See below for additional information.   Mandatory
When you select a data table the following options are visible:
Basic Column This is the name of the column as it appears in your data table. - Text
- Number
- Date
Mandatory
Basic Title This is the title the system will display for the column. If you do not enter a title the system will display the column name. Text Optional
Basic NULL on top If you set this option to Yes for date fields in your table, the system will sort the column so those rows with a NULL value will appear at the top. Yes/No Optional
Basic Highlight term If you want the system to show in bold font a particular word if it appears in the column, enter the word in this field. The system looks for an exact match and you should enter the word without quotation marks around it. Text Optional
Advanced Max size This allows you to control the size of the column. It is the number of characters the system will show in the field before displaying ... to indicate there are additional characters in the field.  Number Optional
Basic Type This is the data type for the column. Selecting a type in this column gives you additional options. For example, if you want the grid to total the numbers in a particular column, the column must specify that the type is "Number". - Text
- Number
- Date
- Time
Optional
Basic Selection Clicking the ... button next to this field opens the Selection Editor, which allows you to specify rules around the data you want to display in your table. For example, if you want to use only those data entries for customers with the last name Smith, you can create a rule specifying that. If want to use a data object from a collection, you also choose that here.

Selection operators vary by variable type as follows:

  • numeric fields: equals, not equals, is larger (or equal) than, is smaller (or equal) than, in, not in
  • text fields: equals, not equals, is not, like, equals or empty, in, not in

Use like with leading and/or trailing wildcard (%) if necessary

In, and not in comparison value must be a collection as text, quoted and comma separated. Use the format variable part to do the conversion.

Complex criteria can be built using the editor by using AND/OR (toggle to switch) and adding parentheses around specific terms to define the precise selection rules.

Advanced Note: while less commonly used, the greater than/smaller than rules for numeric fields can also be applied to text fields, as per SQL

Text Optional
Basic Group by If the table you want to display contains duplicate entries, you can use this field to group them for display. For example, if you have a table that lists both state names and city names, you can group the data by the state name. You select the name of the column by which you want to group your data. Text Optional
Basic Order by You can use this field to tell the system in which order to display the data, and it is based on column. In a table of US State names, you could display the names of the states alphabetically. You select the name of the column by which you want to order your data and whether you want to order it in ascending or descending order. Text Optional
Basic Having The HAVING clause in SQLite enables you to specify conditions that filter which group results appear in the results. You can enter HAVING clauses in this field and use the + buttons to add additional clauses. Text Optional
Basic Default limit This field allows you to set the number of rows the system will display on the page. If the number of rows in the table is larger than the number of default rows you specify, the system will display buttons that allow the user to go to other rows. (H) Text Optional
Basic Additional filters This field allows you to add additional SQLite statements to further filter the data the system displays in the grid. You use the + buttons to add multiple filter statements. Text Optional
Advanced Readonly column name When you enter the name of a column in this field the system displays this column as read-only. Text Optional
Advanced Readonly column hide If you have assigned a read-only column and you check this checkbox, the system will not show the read-only column. Checkbox Optional
Advanced Select callback (js) This field allows you to specify the piece of JavaScript the system will execute when a user selects a record. The system does not pass any parameters. Text Optional
Advanced Submit callback (js) This field allows you to specify the piece of JavaScript the system executes when the user double-clicked and Submit on double click has been enabled. The system will not execute the JavaScript if the user clicks one time. Text Optional
Advanced On Loaded callback (js) This is the JavaScript function the system calls when the grid is drawn completely. Text Optional
Basic Selectable Click this checkbox if you want to allow users to select a row from the table. Users must be able to select a row for them to update, edit, or remove entries. Checkbox Optional
Basic Submit on select If this checkbox is checked, the system submits the form when the user selects a row. Checkbox Optional
Basic Submit on double click If this checkbox is checked, the system submits the form when the user double-clicks a row. To use this feature you must also check the checkbox next to 'Submit on select'. Checkbox Optional
Basic Add If this checkbox is checked, the system displays a button at the top of the grid that allows users to add new records to the grid. (A) Checkbox Optional
Basic Modify If this checkbox is checked, the system displays a button at the top of the grid that allows users to modify existing records in the grid. (A) Checkbox Optional
Basic Delete If this checkbox is checked, the system displays a button at the top of the grid that allows users to delete records from the grid. (A) Checkbox Optional
Basic Copy If this checkbox is checked, the system displays a button at the top of the grid that allows users to copy an existing record and paste it as a new record in the grid. (A) Checkbox Optional
Basic Show grid options (top bar) If this checkbox is checked, the system displays the action buttons (Add, Modify, Delete, Copy) at the top of the grid. If it is not checked, users will not see the action buttons.(A) Checkbox Optional
Style Save button css class You can control the way the save button looks by entering the name of a css class in this field and then defining the class in the css file for your project. Text Optional
Style Cancel button css class You can control the way the cancel button looks by entering the name of a css class in this field and then defining the class in the css file for your project. Text Optional
Basic Show row count If this checkbox is checked the system will calculate the sum of the numbers in a particular column. For the system to do this, however, you must set the row type to "Number" when you define the columns to display in the grid. You do this in the Type field. (B) Checkbox Optional
Basic Totals row caption To have the system calculate the sum of the numbers in a particular column, you enter a caption for that information. The system will add a row to the bottom of the table if that column is defined as Type="number".  Text Optional
Basic Allow export If you click this checkbox the system also displays an additional field called Export-file name, in which you enter the name under which the system will create and download a csv file when the user clicks the Export button the system displays on the top-right of your grid. If the user clicks the export button the system creates a csv export file of the complete contents of the table, assigns it the name you specified, and downloads it to the users computer. (G) Checkbox Optional
Basic Text filter If you click the checkbox next to this option, the system will display a text filter box at the top-right of your grid. Users can enter terms in this field to search for particular terms in the grid. (E) Checkbox Optional
Basic Show reset filter button If you click the checkbox next to this option, the system will display a Reset filters button next to the text filter box. This lets the user remove any filters they applied and see the entire grid. (F) Checkbox Optional
Basic Reset filter on reload The system stores free-text and column filters in the session context by default. However, if you switch to another grid the system would apply the same filters. Selecting this option ensures the system deletes the filters when presenting the grid but keeps them as long as the system does not step forward. Checkbox Optional
Basic Show check boxes If you click the checkbox next to this option, the system adds a column at the front of your grid and inserts a checkbox for each row. This allows users to select multiple rows or one particular row  using the checkbox. It also puts a checkbox in the column heading so users can select all rows. (I) Checkbox Optional
Basic Submit on check If you select Show check boxes, the system shows you this option. If you click the checkbox next to this option, the system passes the status of the checkbox to the server but does not submit the form. Checkbox Optional
Basic Reset checkbox on loading If you select Show check boxes and then click the checkbox next to this option, the system will clear any user-selected checkboxes in the grid when the page is refreshed or reloaded. Checkbox Optional
Basic Data-object for checked rows If you select Show check boxes, this field is where you enter the name of the collection that holds the selections your user makes from the grid.  Data Object Optional
Advanced Custom buttons With this option you can add custom buttons into your options bar (top bar) (B above).
You enter the following options for each button. Their function and setting options are described below.
  • Caption: this is the text the user sees.
  • Data object: this is the name of the data object for the button.
  • Value: this is the value the system registers when the user clicks the button.
  • Auto-submit: if this is set to Yes, the system steps forward on clicking the button.
  • Enabled: the button is active under the following scenarios:
    • always: the button is always active
    • selected:  only active when a record is selected
    • checked:  only active when one or more records checkboxes are checked
    • one-checked: only active when a single row is checked
  • Visible: set to 0 to hide or 1 to show. This can be controlled externally with $(...)
  • Style (if any): set the CSS class for the button
  • Script: used  to set a custom onclick script, otherwise it defaults to the standard
various - see details Optional
Advanced Show loading bar If you click the checkbox for this option the system will display a "loading" bar when the grid is being loaded or is processing data. Checkbox Optional
Basic Sortable Clicking this checkbox enables users to sort each column in the grid by clicking the header row. Checkbox Optional
Basic Show id column When you select this option the system will display the id column of the grid. (J) Checkbox Optional
Advanced Allow edit id column Selecting this option allows users to edit the value in the id column. NOTE: This is the value by which the system identifies the row in the table, and it must be unique. The user will see an error message if they try to change the row id to one that already exists. Checkbox Optional
Basic Filter per column Selecting this option allows users to filter the grid by each column. The system displays a filter dropdown menu at the top of each column. Checkbox Optional
Basic Filter with textbox If you have selected 'Filter per column', you can also click this option and the system will display a filter text field at the top of each column. (K) See Formatting Column Data section below for more information about the search possibilities for numbers, currency and date fields. Checkbox Optional
Basic Show/Hide/columns If you select this option the system will display a dropdown menu at the top of your grid that allows users to select columns to hide. (C) Checkbox Optional
Advanced Show checkbox for NULL values When a user is editing a row, the system displays text input boxes. By default, the system does not differentiate between an empty value and a NULL value--an empty field is treated as a NULL value. If the checkbox next to this option is checked, the user will see a checkbox next to the text input field.
  • If the user checks the checkbox next to the text field the system stores an empty value as "".
  • If the user does not check the checkbox next to the text field the system stores an empty value as NULL/nil.
Checkbox Optional
Advanced Empty Cell Text

When a dbgrid cell is empty the screen will show a '-' unless an alternative is provided.

If the empty cell text input box is cleared, the dbgrid will show nothing in empty cells 

Text Optional
Advanced Do not lazy load By default the system presents a data grid is presented in 2 steps: (1) the system prints to the page the header and buttons; (2) the system prints the table rows. Displaying the table in two steps means the user may see a slight delay. If the user clicks the checkbox next to this options the system loads the whole grid in one step. This prevents flickering as the table is being created but can cause slower loading. Checkbox Optional
Advanced View state data object The system stores the view-state (filters and sorting states) in the session context. Specify a data object here and you can model overrides to the default behavior, including specifying different views based on different roles (when user roles have been specified). 

Override fields include
  • column_filter (collection of columns to display on load)
  • data_limit (number of rows of data per page)
  • order_by (column to sort on initial load)
  • sorted_dir (asc|desc sort order)
  • page_offset (page of data to show on load)
Text Optional

Using Advanced SQLite SELECT Statements in Columns

When choosing columns to show from your data table, it is possible to build more complex select statements using SQLite syntax (or other if using another database such as MySQL) . Examples that work with the default SQLite databases include:

  • use of sum and count by group
  • use of "case when" constructions
  • use of || to join two strings (e.g.  "USD $"||$(price)
  • use of substr to extract partial strings (e.g. select substr(inputdate, 7, 4) as year will select 2020 from the date 06/12/2020)
  • use of printf to customize number format (e.g. currency with no decimal places can be shown with select "$" || printf ("%,d",[cost_var])

For example, the following statement displays the sum of active hazard cases completed before a particular cutoff date for a particular branch (location) and created in 2018.

select sum(case when completed_date<=meeting_date then ifnull(risk_score, 0) else 0 end)
from hazard
where branch_id=$(selected_branch) and status<>'Deleted' and created_date<=meeting_date and created_date>20180000 and item_type='Hazard')

For further information on SQLite SELECT statements, refer to http://www.sqlitetutorial.net/; however, be aware that db_grids3 does not support all syntax.

Formatting  and Filtering Column Data

The system allows you to control the format of your columns, depending on the type of data in that column. The following types and related type-parameters are available. You must set them on the Code tab for your node.

Column Type

Additional Parameters

Description

text / (empty)

-

The system prints the value from the data table without conversion.

number

round, var_format

The system prints the input as value. If Show row count is enabled the system will sum the values. Using round you specify the number of decimals. Use the var_format-parameter to format the output: e.g. "%0.02d"

When using "Filter with Textbox" options, records can be filtered as follows when the column is set to number (or currency).

= 123 will return records that match the value (regardless of how the numbers are presented on screen. ie this will return records that show 123.00)

>123, >=123, <123, <=123 will each return the records meeting the mathematical condition.

Conditions can be stacked by separating each one with a comma.
Entering " >=100, <=200" will return records where the column value is between 100 and 200.

Note that thousand separators, currency symbols should not be included in the search criteria. To search for records greater than $1,000, enter >1000 into the text filter.

Invalid (non-numeric) values are highlighted with a warning icon and tooltip as shown below. The icon can be changed or removed via CSS if your specific use case does not need to show these,

 

date, time, date_time

date_format_str, date_format_str_month_year, date_format_str_year, time_format_str, date_separator, strip_zeros

The system prints the value as Date, Time, or Date/Time values.

When using "Filter with Textbox" records can be limited with partial date strings, e.g.

>2018 returns records from 1/1/2019 on

>=01/2019, < 07/2019 returns 6 months of records

2019/04/05 returns all rows from that day

>01:00, <06:00 returns rows from the 5 hour window

currency

symbol

The system prints a value as currency (with 2 decimals). The default currency symbol is $ and can be overwritten using the symbol parameter.

See section on numbers above for information on using  "Filter with Textbox" with currency columns.

Invalid values are highlighted with a warning icon and tooltip as shown below. The icon can be changed or removed via CSS if your specific use case does not need to show these.

 

collection separator, template The system prints a concatenated string of values of an input collection. The values are separated with the specified separator-parameter and can be controlled using the template-parameter for complex values.
html This allows the cell input to be in HTML.
click action = action|click, popup_parts, display

This setting allows users to click on the db_grid3 cell and associates that click with one of two possible actions: 'download' and 'popup'. If you activate the 'click' type the system adds an onclick event handler that calls the function called db_grid3.cell_selected. This in turn performs the configured part action in the backend.

Popup
The popup part action uses the popup_parts configuration to display content in a popup window. The configuration looks like this:

{
column = "dataobject_cached.options",
record_format = {
action = "popup",
popup_parts = {
"<DIV><DIV style='float:left'>",
{
_mode = "write",
_name = "format_variable",
data_type = "collection",
path = "popup_data",
value_template = "$(caption)$(caption.encoded_string)$(caption.text)"
},
"</DIV></DIV>"
},
type = "click"
},
title = "options",
view_parts = {
"<DIV><DIV style='float:left'>",
{
_mode = "write",
_name = "format_variable",
char_limit = "50",
data_type = "text",
path = "dataobject_cached.options.encoded_string"
},
"</DIV></DIV>"
},
width = "-"
},

Download
The download action is a little more complicated, as it is intended to download binary/file data from a separate database. When a user clicks on a call with the download action enabled, the part action uses the selected cell value as a key to retrieve data from the 'data_storage' database. This key has been previously obtained by storing data in the 'data_storage' database using the data_storage library. The configuration looks like this:

{
column = "session_list.report",
record_format = {
action = "download",
display = "<img src='/lncore/icons/pdf.gif' border=0 style='margin-left: auto;margin-right: auto;'> Assessment Report",
type = "click"
},
title = "PDF Download",
width = "15%"
},

hoursMinutes   The system prints the value (in minutes) in the hh:mm format.
link/URL no_strip_html = 1 The system does not remove the HTML encoding around the URL.

Additional Information

The Formatting Dates article provides additional information that might be helpful if you want to fully customize your data formatting in the data table.

Reply Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
Like Follow
  • 1 mth agoLast active
  • 151Views
  • 4 Following

Home