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:
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.
|
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.
|
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
|
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. 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. >=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 { Download { |
| 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.