Edit form - LiteDataGrid

1. General information

1.1. Description of the control

LiteDataGrid is a view table that displays database records retrieved by a query from a database, without the possibility of personalization, search and filtering records, and without the ability to import data. Data export is offered only by the statistical LiteDataGrid, directly to an XLSX file without the export dialog; the “Deny data export” field is not offered in the control settings. A row LiteDataGrid displays the records in the order of the database query and the user cannot re-sort them; a statistical LiteDataGrid can be sorted by clicking a column header in the same way as a statistical DataGrid, and the “Default sort” button restores the default sorting. The sorting is not saved in a LiteDataGrid. A detailed description of sorting a statistical view table is provided in the separate guide Edit form – DataGrid.

1.2. Create a new control

• A detailed description of creating new controls in the edit form is provided in the separate guide Creating edit form controls.

1.3. Edit or delete a control

• For each existing control, a pencil icon appears on the left side of the edit form to change or delete the control's settings.
• Individual controls can be moved vertically in the edit form using the Drag&Drop method. Moving consists of grabbing the control, preferably behind its name located in the left column of the edit form, and then dragging it to the desired position in the edit form.
• Controls can be moved only with the “Drag&Drop” check box located above the toolbar with controls selected. For security reasons, this check box is cleared after each login to the application, so that a control is not moved by accident.

2. List of tabs in the control settings dialog

• General – Setting general properties
• View table – View table column settings
• Search – Search settings
• Help – Help text settings
• Rights – Rights settings
• Administration – Setting other properties

2.1. “General” tab

2.1.1. Common query

2.1.2. Name

• The name of the control displayed in the header of the view table.
• Placing the control name between two pipes ensures that the view table is completely hidden if no record is the result of the query – for example, “|Records in the Book table|”. The view table is hidden in this way only in user mode.
• The right side can be followed by alternative HTML code, which will be displayed instead of the view table if no record is the result of the query – for example “|Records in the Book table|<img src="Images/Help.gif">”.
• The suffix “++” at the end of the name merges, in the view table, the cells of consecutive rows of the same record that have the same value. The suffix “+++” merges the cells of consecutive rows that have the same value in the first column of the view table and in the further columns within its range, and shows the value of the first column in bold. The suffix applies only to a common query when the view table displays more than one record per page, and only for a common query is it hidden from the name – for an aggregation query it remains in the name; it does not apply in the “Show as details in two columns” mode. If the name is also placed between pipes, the suffix belongs inside the pipes – for example “|Records in the Book table++|”; the text after the right pipe is always taken as alternative HTML code.

2.1.3. Dictionary

• The dictionary lists the names of other controls, forms, view pages, categories, applications, and application groups used across the entire application.
• The dictionary search is used to design the correct control name and to maintain the consistency of the nomenclature throughout the application so that controls with the same meaning located in different places in the application are named in the same way.

2.1.4. Width

• The width of the control, expressed in pixels.
• If this input field is not filled in, the width of the view table will be determined automatically by the web browser.

2.1.5. Data source

• The data source defines the source database records displayed in the view table.
• Data source settings are made in the graphical designer of database queries. A detailed description of the database query designer is provided in the separate guide Database query designer.

2.1.6. Options

• Deny opening records
  • Checking this box determines whether all users should be denied access to the edit form to open the displayed database record using the pencil icon that appears at the beginning of each row of the view table.
• Allow multiple assignments
  • Checking this box determines whether a text box or drop-down list with the selected value should be displayed in the view table instead of the control value itself, with the option to change the value without opening and editing the record in the edit form.
    • For a view table that reads data from a database view, this box does not apply – values cannot be changed in a cell.
    • This function is applied only to controls that have the “Allow multiple assignments” check box selected on the “General” tab.
    • If a control has “DENYCONTROL()” in its “Notes” field, the view table displays only its value, without the option to change it.
    • An anonymous user may change values in the view table under the same conditions as a logged-in user – check boxes always, other controls according to the editing rights of the record. All anonymous visitors, however, share a single user account and are therefore the owners of every record created by any of them. If the owner may change a record, any anonymous visitor may thus change records created by another visitor as well.
• Show only nested records
  • Checking this box determines whether the “PID = ID record in form” constraint should be automatically appended to the database query definition.
  • The condition does not take PFORM into account. If the source edit form stores nested records for several parent forms, a condition on PFORM has to be added to the database query. For a new record that has not been saved yet, the view table displays no records.
  • To evaluate this condition efficiently, configure the “PID” or “PFORM + PID” index on the edit form used as the data source of the view table. Do not configure the index on the form that contains the view table.
• Show sums of columns
  • Checking this box determines whether a row with the sum of the values of the individual columns should be displayed in the footer of the view table.
  • This function is applied only to TextBox controls of numeric, currency and time data types and to AutoSum controls that do not have the “Hide the sum of the column in the view table” check box selected on the “General” tab.
• Show as details in two columns
  • Checking this box determines whether the values should be displayed as a two-column table with the column name in the first column and the value in the second column, instead of the normal table method.
  • In this mode, the view table displays at most 1,000 records. If the database query returns no record, the control is not displayed in the edit form.
• Show empty values
  • Checking this box determines whether columns with an empty value should be displayed in the “Show as details in two columns” mode.
• Hide data source
  • Checking this box determines whether the contents of the view table should be automatically hidden when the edit form is opened. Database records will not be displayed to the user until the “Show” icon is clicked or clicking on the tab in which the view table is located. If the view table is located on the tab that is active when the edit form is opened, the database records are displayed immediately.
• Snap to previous control
  • Checking this box determines whether the control itself should be displayed to the right of the previous one in the edit form, or whether it will be displayed below the previous control in the usual way.

2.1.7. Aggregation query without grouping by date

• Show sums of columns
  • Checking this box determines whether a row with the sum of the values of the individual columns should be displayed in the footer of the view table.
  • The totals row holds a value for each column according to its type: for a count and a sum the sum of the row values, for an average the average of the row values (an empty value counts as 0), for the maximum and minimum the largest or smallest value, where rows without a value are skipped (if no row has a value, the cell remains empty), for a cumulative column the sum of its last value in each group, or of its first value for the cumulation “-1” and “--1” (without grouping columns the value of the last or the first row), and for an expression column the expression evaluated over the values of the totals row – an expression column with the syntax “Column@+sum”, with the NGEF, SQL or SQLTABLE function in the expression, or with a reference to the date by which the query is grouped is summed instead. Grouping, text and date columns remain empty in the totals row; the first column, however, holds the symbol “Σ” if it would otherwise be empty. A column with a displayed value (the third part of the column name) shows this value in the totals row as well. The same applies to an aggregation query with grouping by date.
  • This function is applied only to columns that do not use the command to hide the sum of the column using the syntax “Column@-sum”. A detailed description of the syntax of column names is provided in the separate guide Database query designer.
• Hide data source
  • Checking this box determines whether the contents of the view table should be automatically hidden when the edit form is opened. Database records will not be displayed to the user until the “Show” icon is clicked or clicking on the tab in which the view table is located. If the view table is located on the tab that is active when the edit form is opened, the database records are displayed immediately.
• Snap to previous control
  • Checking this box determines whether the control itself should be displayed to the right of the previous one in the edit form, or whether it will be displayed below the previous control in the usual way.

2.1.8. Aggregation query with grouping by date

• Fill time gaps
  • Checking this box determines whether missing periods should be added to the resulting data set in the column according to which the data is grouped. Periods are added between the first and the last period found for each grouping value, not up to the limits of the time span. The numeric columns of the added rows are set to “0”, the grouping value is taken over and the text columns remain empty.
• Pivot table
  • Checking this field determines whether the view table should write the data out as a pivot table – the grouping values in the rows and the individual periods in the columns.
  • The field applies only to an aggregation query with grouping by date that also has the grouping columns filled in and has no custom multi-row header; otherwise it has no effect.
  • If “Show sums of columns” is checked as well and the table contains more than one period and at least one non-cumulative column of a count, sum, average, maximum or minimum without the syntax “Column@-sum” or an expression column that is summed in the summary column (see below), a summary column “Σ” is added after the period columns – for a count and a sum it holds the sum of all periods, for an average the average per period, and for a maximum and a minimum the highest or lowest value; for a maximum and a minimum, periods in which the group has no record are skipped. For columns with the syntax “Column@-sum” the summary column remains empty; if all columns have this syntax, the summary column is filled in and only the totals row is hidden. Cumulative columns remain empty in the summary column. An expression column with the syntax “Column@+sum”, with the NGEF, SQL or SQLTABLE function in the expression, or with a reference to the date by which the query is grouped is summed over all periods in the summary column; other expression columns are evaluated in it over the values of the summary column.
  • Clicking the header of a period displays only that period and marks its header with the text “(Active)”. The same applies to the header of the summary column “Σ”. Clicking it again displays all periods again. Selecting a period or cancelling the selection resets the sorting of the table to the default. The selection of a period is not saved in a LiteDataGrid.
• Show sums of columns
  • Checking this box determines whether a row with the sum of the values of the individual columns should be displayed in the footer of the view table.
  • This function is applied only to columns that do not use the command to hide the sum of the column using the syntax “Column@-sum”. A detailed description of the syntax of column names is provided in the separate guide Database query designer.
• Hide data source
  • Checking this box determines whether the contents of the view table should be automatically hidden when the edit form is opened. Database records will not be displayed to the user until the “Show” icon is clicked or clicking on the tab in which the view table is located. If the view table is located on the tab that is active when the edit form is opened, the database records are displayed immediately.
• Snap to previous control
  • Checking this box determines whether the control itself should be displayed to the right of the previous one in the edit form, or whether it will be displayed below the previous control in the usual way.

2.2. “View table” tab

2.2.1. Columns

• Definition of view table column settings.

2.2.2. Records per page

• For clarity, the view table uses the paging of the displayed records. This is useful in situations where the view table is retrieving large amounts of data.
• The number of records per page determines the maximum number of records that appear on one page. It is easy to switch between the individual pages using the toolbar, which is contained in each view table in its footer, or optionally in the header.

2.3. “Search” tab

2.3.1. Options

• Show search results only
  • Checking this box determines whether the data in the view table should be displayed only when filling in the values in the form that are referenced by the database query constraints of that view table.
  • Conditions referring to a control of the form that is not filled in (for a ForeignKey control also the value 0) are omitted in this mode, so a single filled-in value is enough for the data to be displayed. In a group of conditions joined by “or”, however, such a condition lets all records through. For an aggregate query, the box does not apply – the conditions are not omitted, they are evaluated in the same way as without the box checked, and the data is displayed immediately.

2.4. “Help” tab

2.4.1. Help

• Help text is displayed to the right of the control in the edit form.
• The help texts of all controls start at the same vertical line across the entire edit form.
• Text – the help text is defined by a manually entered text string in a multi-line text field below the radio button
• From query – the help text is defined by the result of a database query that is evaluated when the edit form is opened
• A detailed description of the help text is provided in the separate guide Layout settings.

2.5. “Rights” tab

2.5.1. Rights

• Rights define a list of user groups and users who have permission to view the control on the edit form.

2.5.2. Replace rights to other controls

• The “Replace rights to other controls” function is used to unify the rights within the entire edit form for controls that are related in terms of rights.
• Thanks to the “Show...” link, it is possible to display a list of all controls in the edit form, including setting the rights of these controls. A check box is displayed for each control to select it. When the control is saved, the same rights are then set for the selected controls.
• An alternative for unifying rights within the entire edit form is to display the report available from the “Rights of child objects” tab in the edit form settings.

2.6. “Administration” tab

• The “Administration” tab is only displayed for existing controls.

2.6.1. Template name

• The template name is used to name the view table with the option to copy it when creating other view tables with the same source database table.
• When creating a new view table, it is first necessary to define the data source using a database query, and then all available templates are available in the “Templates” drop-down list on the “General” tab. After selecting a template, all parameters of the view table will be automatically pre-filled with data from the selected template.
• A list of all view tables that are marked as templates can be displayed using a report. A detailed description of the reports is provided in the separate guide Reports.

2.6.2. Other

• Change type to
  • The drop-down list offers the types of controls to which conversion of the control is supported.

2.7. “Save”, “Copy”, “Delete”, and “Close” buttons

• “Save” button – Saves changes to the control settings.
• “Copy” button – Creates a new copy of the current control with identical settings, but with a newly generated unique identifier.
• “Delete” button – Permanently deletes the control.
• “Close” button – Closes the control settings dialog without saving changes.

3. Examples

3.1. Javascript functions

• dg_Disable();
• dg_Enable();
• dg_HideColumn(DG123, 456);
• dg_HideColumn(el(DG123), 456);
• dg_HideHeader(DG123);
• dg_SearchColumns(DG123, [1, 2, 3]);
• dg_SearchColumns(el(DG123), [1, 2, 3]);
• dg_ShowColumn(DG123, 456);
• dg_ShowColumn(el(DG123), 456);
• dg_VisibleColumns(DG123, [1, 2, 3]);
• dg_VisibleColumns(el(DG123), [1, 2, 3]);
• var array = dg_Controls(el(DG123));