Edit form - DataGrid

1. General information

1.1. Description of the control

DataGrid is a view table that displays database records retrieved by a query from a database, with the possibility search and filtering of records, and with the possibility of data exports and imports.

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 after its name located in the left column of the edit form, and then dragging it to the desired place 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
• Details – Settings for columns displayed in details
• 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 the two bytes 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 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 |<img src="Images/Help.gif">“.

2.1.3. Dictionary

• The dictionary displays a list of names of other controls, forms, view pages, categories, applications, and application groups used throughout the 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

• Show “New Record...” icon
  • Checking this box determines whether an icon should be displayed in the header of the view table, which redirects the user to a specific edit form in order to create a new record in the database.
• Deny customization
  • Checking this box determines whether individual users should be disabled from changing the column settings of this view table.
• Allow data import
  • Checking this box determines whether an icon should be displayed in the header of the view table, which will allow data to be imported into the displayed database table from format files.
    • XML
    • XLS (X) (Excel)
    • CSV (Excel)
    • MDB (Access)
    • ZIP – zipped variant of the above formats
  • Attachments are imported only with their content (for example the XML format exported including attachments). Importing an attachment by the path to a file on the server or by the ID of an existing attachment is reserved for users with administrator privileges; the path must lead into the NET Genium installation directory unless the import is run from the local computer.
  • The import does not run the save scripts of the edit form and does not queue the records for synchronization; the change history is written unless the edit form has it disabled. Allowing the import gives the user the same options as an administrator: importing by the ID column updates existing records or creates them with the given ID, and the link to the parent record (PID, PForm) and “Read-only” controls can be imported as well.

• Allow MS Excel import
  • Checking this box determines whether the “MS Excel Import/Export” icon should be displayed in the header of the view table. After clicking the icon, the options “Export data” and “Import data” are shown, allowing data to be exported to an MS Excel file or imported into the database table using an XLS file. The exported XLSX file is prepared for a re-import – it contains a hidden row with the column identifiers and a hidden column with the record ID, and it does not include ID columns, password columns, attachment columns or encrypted columns. If “Deny data export” is checked, only the import is offered.
  • This feature is only available for view tables that do not use joins.

• Deny data export
  • Checking this box hides the icon in the header of the view table that allows the displayed data to be exported to files. The denial is checked on the server as well; users with administrator privileges can still export data. A detailed description of data export from DataGrid is provided in a separate guide DataGrid data export.
• 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 changes
  • 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.
    • This function is applied only to controls that have the “Allow multiple changes” check box selected on the “General” tab.
    • 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.
  • Checking this box determines whether the “Assign value...” item should be available in the drop-down list of individual column filters, which is used to assign a value to all marked or filtered records in the view table.
    • This function is applied only to controls that have the “Allow multiple changes” check box selected on the “Filters” tab.
    • Multiple changes can only be made by a logged-in user.
    • If specific records are marked in the view table, only the selected records will be assigned a value. If no records are marked, all filtered records will be assigned a value.
    • Assigning a value and deleting work over the whole condition of the view table, i.e. also over records that are not displayed in the table because of the “Load only first” setting.
    • If “Show search results only” is checked, a value cannot be assigned and records cannot be deleted without an entered search term or composed filter.
  • Checking this box determines whether the “Delete records in view table” icon should be available in the view table header, which is used to delete marked or filtered records in the view table.
    • Users with administrator privileges – if specific records are marked in the view table, only the marked records will be deleted. If no records are marked, all filtered records will be deleted!
    • Users without administrator privileges can only delete marked specific records, they do not have permission to delete all filtered records.
    • Users with administrator privileges see the icon in every view table that does not read data from a database view.
    • A regular user sees the icon also when only “Activate marking records” is checked, and does not see it if the edit form has the “Delete” button hidden. A regular user can delete at most 1,000 records at once, and only those the user is allowed to change.
  • Checking this box determines whether the “Mark records” icon should be available in the view table header, which is used to mark individual view table records in order to delete them or assign a value using a filter.
    • To the list of marked records – primary key values (“id” column) separated by a semicolon – it is possible to access using variables “#datagrid1#”, “#datagrid2#”, “#datagrid3#” etc. in the script. The index indicates the order of the view table in the edit form.
• Activate marking records
  • Checking this box determines whether a check box should be automatically displayed at the beginning of each row of the view table to mark a record in order to delete it or assign a value using a filter, without using the “Mark records” icon located in the view table header.
    • The list of marked records – primary key values (column “id”) separated by a semicolon – can be accessed using the variables “#datagrid1#”, “#datagrid2#”, “#datagrid3#”, etc. in the script. The index indicates the order of the view table in the edit form.
• Show sums of column
  • 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 data types that do not have the “Hide column total in view table” check box selected on the “General” tab.
• 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.
  • 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.
• 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 “View” icon is clicked or clicking on the tab in which the view table is located.
• 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

• Deny data export
  • Checking this box hides the icon in the header of the view table that allows the displayed data to be exported to an XLSX file; the export runs without the export dialog. Users with administrator privileges can still export data.
• 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 given in the separate guide Database query designer.
• 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

• Deny data export
  • Checking this box hides the icon in the header of the view table that allows the displayed data to be exported to an XLSX file; the export runs without the export dialog. Users with administrator privileges can still export data.
• Fill time gaps
  • Checking this box determines whether time continuity should be ensured in the resulting data set in the column according to which the data is grouped. Where aggregated rows are missing, they will be automatically populated with all other columns set to “0”.
• 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.
• 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 given in the separate guide Database query designer.
• Snap to the 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. Options

• Show upper navigation toolbar
  • Checking this box determines whether a toolbar with icons for switching between pages should be displayed in the header of the view table.
  • By default, this toolbar is displayed in the footer of each view table if the number of data displayed is greater than the number of records on the page.
• Show filters
  • Checking this box determines whether the view table should allow filtering of database records.

2.2.2. Columns

• Define the default settings of view table columns.

2.2.3. The number of 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. “Details” tab

2.3.1. Options

• Show details
  • Checking this box determines whether the view table should display the details of each record at the bottom of the view table.
• Show tooltip
  • Checking this box determines whether the view table should display the details of individual records in the tooltip when the mouse hovers over individual records.

2.3.2. Columns

• Define the default settings for detail columns.

2.4. “Search” tab

2.4.1. Options

• Activate search
  • Checking this box determines whether a row with a search box and a “Search” button should be automatically displayed in the header of the view table.
• 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.
  • This box also determines where the term entered in the search box is evaluated. When it is checked, the search term becomes part of the database query and the search result is therefore decided by the collation of the database. When it is not checked, the records are read from the database without the search term and the term is evaluated on the records already read – such a search is case insensitive but accent sensitive, regardless of the database settings.
  • The sorting of the view table is evaluated on the records already read as well, so it does not depend on the database settings either. When the number of displayed rows is limited, however, the collation of the database decides which records are read in the first place.
  • Searching regardless of diacritics is not set here, but on the individual controls using the “Search without diacritics” box.

2.5. “Help” tab

2.5.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.6. “Rights” tab

2.6.1. Rights

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

2.6.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 “Subordinate object rights” tab in the edit form settings.

2.7. “Administration” tab

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

2.7.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.7.2. Other

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

2.8. “Save”, “Save to all users”, “Copy”, “Delete”, and “Close” buttons

• “Save” button – Saves changes to the control settings together with the individual settings of the currently logged-in user.
  • The individual settings of this datagrid for the current user will be deleted so that, upon the next display of the datagrid, the default settings defined in this dialog are applied.
  • Individual datagrid settings can be opened using the gear-icon, which is available in the icon bar in the header of every datagrid.
  • Individual datagrid settings include the following options:
    • Options in the “General” tab of the individual datagrid settings
      • “Sort by”
      • “Records per page”
      • “Show upper navigation toolbar”
      • “Show filters”
      • “Show grid”
      • “Show sums of columns”
      • “Show details”
      • “Show tooltip”
    • Column settings in the “View table” tab of the individual datagrid settings
    • Column settings in the “Details” tab of the individual datagrid settings
  • All other users will retain their individual datagrid settings.
• “Save to all users” button – Deletes the individual settings of this datagrid for all users so that, upon the next display of the datagrid, the default settings defined in this dialog are applied.
• “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. Behavior of the view table

3.1. Loading data, paging and sorting

• The view table loads the whole result of the database query, and paging takes place only over the loaded data. The range of loaded data is limited by the “Load only first” setting in the database query designer.
• If “Show search results only” is checked and the query does not have “Load only first” set, at most as many records are loaded as set in “Records per page”. The view table then displays and exports at most one page of results.
• Records with a duplicate value (“Remove records with duplicate”) are removed only after the data is loaded, so the table may display fewer records than “Load only first” allows.
• Without a valid license key, the row query of the view table is limited to 100 records; a statistical view table is not limited.
• Clicking a column header sorts the table by that column, clicking it again reverses the sort direction. The “Default sort” button restores the sorting of the database query.

3.2. Column sums

• Sums in the footer are calculated from all filtered records, not only from the current page.
• With 1,000 or more records, the sums are calculated by the database. Such a sum does not reflect values modified by an external function or the “Load only first” limit.

3.3. Opening records

• A record is opened by the link at the beginning of the row and, in user mode, also by double-clicking the row; on a mobile device, clicking the row is enough.
• “Deny opening records” only hides the links in the view table. Opening a record by the direct address of the edit form is governed by the rights of the edit form.

3.4. Individual settings

• Individual settings (sorting, current page, records per page, search term, filters and columns) are saved separately for each user, with the database query of the view table.
• Individual settings are not saved for the anonymous user, who has neither view table customization nor the composed filter available.
• Columns to which the user has no rights are removed from the user's individual settings when they are saved.
• “Show grid” is part of the individual settings only; the default settings of the control do not determine it.

3.5. Composed filter and values in the table

• Values entered into the composed filter are inserted into the condition literally. Server functions are not evaluated in them and only date variables (e.g. #today#, #now#) are replaced.
• A check box displayed in the view table can always be switched – even for a “Read-only” control and for a record of another user. Other controls can be changed in the view table only if the user is allowed to change the record and the control is not “Read-only”.

4. Examples

4.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));

4.2. Variables

• #datagrid1#
• #datagrid2#
• #datagrid3#