Dbgrid (Release 7.4+)

Type/
Options

• When you specify group_by, you can use the aggregate functions MIN and MAX for selecting the record with the column with the lowest or highest value in the set of grouped records.
• You can use the COUNT function for counting the total numbers of records per group.
• You can use the AVG and SUM function for calculating the average or the sum of the values in a column of the records per group.
• You can use the GROUP_CONCAT for concatenating all the values of a column of the records per group. For the latter, you can alter the default separator (,) by setting the record_format.separator for the column.

• 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 prior to this node to do the conversion).

When you enter the name of a column in this field the system displays this column as read-only.

To make additional columns read-only, add the following to the target column(s) after the column definition (via the code tab - Expert Only)

column  = "your column name",
modify_parts = {
            {
              readonly = "1"
            }
          },
...

• 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.

Specify a wildcard for use in the column filters. Using a custom wildcard overrides the default behavior and permits search of strings starting or ending with the user entry. For example, a custom wildcard of '%' will allow:

• searches for text starting with.... (e.g. enter ABC% in the filter to find 'ABCDE' but not 'anythingABC')
• searches for text ending with.... (e.g. enter %XYZ in the filter to find 'ABCXYZ' but not 'ABCXYZ123').

Default setting (no wildcard specified in the part editor) performs a basic string search (%ABC%) anywhere in the column (e.g. enter ABC in the filter to find 'ABCDE' and 'anythingABC')

• 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 1, the system steps forward on clicking the button, otherwise set to 0.
• 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

• 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)

round_truncate,
round, 
decimal_separator, 
var_format, 
(negative numbers)*

round_truncate [CODE EDIT]
This allows you to set the rounding and truncation functions for a number. The possible values are:

• round: Rounds the number to the specified number of decimals
• truncate: Truncates the number to the specified number of decimals
• round_whole_number: Rounds the number to the specified number of digits to the left of the decimal point (no digits after the decimal)

record_format = {
            round_truncate = "round",
            decimals = 3,
            type = "number"
          },

round [CODE EDIT]    
If round_truncate is set to "round_whole_number", "round" specifies the number of digits to the left of the decimal point that need to be rounded.

 record_format = {
            round_truncate = "round_whole_number",
            round = 3,
            type = "number"
          },

decimal_separator
This identifies the symbol to use to identify decimal places: comma or period. You set this on the Advanced tab.

var_format [CODE EDIT]
Use the var_format parameter to format the output: e.g. "%0.02d" will display numbers with two digits, including a leading zero for numbers less than 10.

 record_format = {
            type = "number",
            var_format = "%0.02d"
          },

brackets_around_neg_numbers [CODE EDIT]
This option puts round brackets around negative numbers; e.g. $ -1000 becomes ($ 1000). The possible values are:

• system default
• 0 (no brackets)
• 1 (brackets)

record_format = {
    brackets_around_neg_numbers = "1",
    type = "number"
    },

Filtering
When using Filter with Textbox option, you can filter records in the grid as follows:

If 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; i.e., this will return records that show 123.00).
• >123, >=123, <123, <=123 will each return the records meeting the mathematical condition.

You can stack conditions by separating each one with a comma. For example, if you enter " >=100, <=200" the system returns those records where the column value is between 100 and 200.

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

The system highlights invalid (non-numeric) values with a warning icon and tooltip as shown below. You can change or remove the icon through CSS if your specific use case does not need to show these.

• >2018 returns records from 1/1/2019 forward
• >=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

symbol
This is the currency symbol the system will display next to the value. You set the symbol on the Advanced tab. When the symbol is '$' the following are fixed:

• decimal_separator: .(dot)
• thousands_separator: ,(comma)
• symbol_location: prefix

symbol_location [CODE EDIT]
This specifies the location of the currency symbol. Possible values are:

• system default
• prefix
• suffix

record_format = {
    symbol_location = "prefix",
    type = "number"
    },

decimal_separator [CODE EDIT]
This is the symbol used to separate integer from the fractional part of the number. The possible values are: 

• system default
• , (comma) 
• .(dot)

record_format = {
    decimal_separator = ",",
    type = "number"
    },

thousands_separator [CODE EDIT]
This is the symbol used to separate groups of thousands. Possible values are: 

• system default
• no thousands separator
• , (comma) 
• . (dot)

record_format = {
    thousands_separator = ".",
    type = "number"
    },

Popup [CODE EDIT]
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 = [ignore LineFeed]
    "$(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 = "-"
},

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

Type/
Options

For more sophisticated FROM clauses that are not supported by the table editor in the dbgrid part, use this field in place to write a custom from clause, starting with the first table. [FROM is not required].
Examples of possible constructions include:

• LEFT JOIN
• FROM (select * from ...)