CS

Edit form - AutoSum

Home / Documentation / Administrator guide / Edit form /

Edit form - AutoSum

CS

1. General information

1.1. Description of the control

AutoSum is a one-line text field of any width, which is used to automatically evaluate the aggregation function on predefined data from the database. These aggregation functions include number, sum, average, maximum, and minimum. The aggregation function is evaluated when the record is opened in the edit form (for an existing record, the calculated value is saved to the database straight away), after a button script in the edit form is run, or by the server function “UPDATEAUTOSUM”. When the edit form is saved, the value calculated by the server is saved; a value sent by the web browser (for example, changed by JavaScript) is ignored. If the calculation fails, the database value “null” is saved, both in the edit form and in the “UPDATEAUTOSUM” function.

1.2. Create a new control

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.
  • Deleting an autosum results in the irreversible deletion of the database column associated with that autosum in the database, and thus in the deletion of its values in all records in the database table.

2. List of tabs in the control settings dialog

  • General – Setting general properties
  • Help – Settings for help text that can be displayed on the right side of the edit form
  • Filter – Setting the filter that can be displayed in the view table in the column header
  • Rights – Rights settings
  • Administration – Setting other properties

2.1. “General” tab

2.1.1. Name

  • The name of the control displayed in the edit form in a separate column with a colored background to the left of the text box itself.

2.1.2. Abbreviated name

  • Optional control name displayed in the view table header if the control is selected as one of the view table columns.
  • If an abbreviated name is specified, the full name of the control is displayed in the tooltip above the abbreviated name.
  • If an abbreviated name is not specified, the full name of the control is displayed in the header of the view table.
  • The abbreviated name “ ” (fixed space) can completely hide the name of the control displayed in the view table.

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

  • The unit specifies a symbol (such as “%”) that appears everywhere in the application, along with the value of the control. The symbol is always placed to the right of the value itself, separated by a space.
  • The unit may also include a decimal format that can be used to force automatic rounding of the value (for example, “{0.00}” or “{0.00}%”).
  • Along with the unit, it is possible to define the width of the space designated for the unit in the edit form so as to maintain a space between the unit located to the right of the text box and the help of the control located to the right of the unit.
  • A calculated value with the “Decimal number” or “Currency” data type is displayed in the edit form with thousands separated (for example “1,234,567.89”) automatically – unlike the TextBox control, this behavior is not configurable.

2.1.5. Width

  • The width of the control, expressed in pixels.

2.1.6. Calculate

  • The “Calculate” field specifies the aggregation function that the autosum calculates.
  • The drop-down list contains the following types of aggregation functions:
    • Count
    • Sum
    • Average
    • MAX
    • MIN
  • The aggregation functions are evaluated as follows:
    • Count – the number of filled-in values of the column,
    • Sum – the sum of the values; for a date column, it returns the number of filled-in values,
    • Average – the sum of the values divided by the number of all matching records, so empty values count as zero; for a date column, it returns the date halfway between the smallest and the largest value,
    • MAX and MIN – the largest or the smallest value.
  • The result of the aggregation function can be multiplied or divided, or a numeric constant can be added or subtracted to it. The input field to the right of the drop-down list is used to define this calculation, see the following examples:
    • “+123”
    • “-123”
    • “*123”
    • “/123”
  • The input field can contain any expression appended to the calculated value, including variables (#ng_...#). The endings “+CEILING”, “+FLOOR” and “+ROUNDn” (n is the number of decimal places) override the “Rounding” setting. If the result is a date, neither the expression nor the rounding is applied.

2.1.7. Of the column

  • Definition of the column that autosum uses to calculate the value.

2.1.8. Of the database table

  • Definition of a database table that contains a column that autosum uses to calculate a value.

2.1.9. With conditions

  • Definition of conditions that limit the set of data from which the resulting value is calculated.

2.1.10. Joins

  • Definitions of joins that are built using the “JOIN” clause.

2.1.11. Rounding

  • Definition of rounding of the calculated value.
  • The drop-down list contains the following types of rounding:
    • Do not round
    • Round Up
    • Round Down
    • Whole number
    • 1 decimal place
    • 2 decimal places
    • 3 decimal places
    • 4 decimal places
    • 5 decimal places
    • 6 decimal places
    • 7 decimal places
    • 8 decimal places
  • “Round Up” always rounds away from zero and “Round Down” always toward zero – a negative number is therefore rounded to the lower whole number by “Round Up” and to the higher one by “Round Down”.

2.1.12. Alignment

  • Alignment determines the position of the text inside the text box itself in the edit form, and the position of the control values in the view table if the control is selected as one of the columns in the view table.
  • The drop-down list contains the following types of alignment:
    • Left
    • Centre
    • Right (default)

2.1.13. Options

  • Add up only nested records
    • Checking this box determines whether the resulting value should be calculated only from nested records to the currently open record in the edit form.
    • Nested records are selected only by the parent record (the “pid” column), regardless of the parent form (the “pform” column). For a new record that has not been saved yet, the resulting value is 0.
  • Hidden field
    • The hidden field setting determines whether the control should be part of an edit form, but should not be visible in user mode.
    • The hidden control, including the filled value, can be found in the source code of the web page. It cannot therefore be used as part of security measures, the rights to the control are used for this.
  • Hide the sum of the column in the view table
    • Checking this box means that the sum of the control values from all filtered view table entries is not displayed in the footer of the view table.
    • In order for column totals to be displayed in the view table, the view table must have column totals displayed. The default settings for displaying columns are taken directly from the properties of the view table, however, each user can change these settings themselves in their personalized settings of the view table.
  • 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.
    • If the control snaps to the previous one, the offset width from the previous control can be defined
      • The indent is defined in pixels in a separate text box, which is located to the left of the “Width” text box.
    • A detailed description of snapping to controls is provided in the separate guide Layout settings.

2.2. “Help” tab

2.2.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.2.2. Notes

  • Notes are used to enter any text intended for the application administrator.

2.3. “Filter” tab

2.3.1. Options

  • Show filter in the view table
    • Checking this box determines whether the view table should be allowed to filter records by the values present in the database in the column associated with this control. The filter drop-down list is located in the header of the view table under the name of the control.
  • Allow multiple assignments
    • For the AutoSum control, this option is always unavailable – the value calculated by the autosum cannot be assigned in bulk.

2.3.2. Records per page

  • The number of records per page determines the maximum number of unique values that appear in the filter based on the analysis of the read data in the view table in the column associated with the filter. If there are more occurrences of unique values in the data than the defined number of records on the page, the analysis of unique values in the given column will be interrupted, and the item “Load values...” will be displayed in the filter. All unique values in the given column and read data in the view table.
  • The view table itself can read a large amount of data, or the filtered column can contain a large number of unique values, which affects the speed of compiling a list of these values. In cases where it is not important to analyze the values at all when generating the view table, and it is sufficient to use the function “Load values...”, it is advisable to set the number of records on the page to the value “0”.

2.4. “Rights” tab

2.4.1. Rights

  • Rights define a list of user groups and users who have permission to display a control on an edit form and to display the values of that control elsewhere in the application.
  • Users who do not have control rights
    • they do not see the control or its value in the edit form, and the control is not rendered into the source code of the page in a web browser either,
    • they cannot display the column associated with this control in a view table or other visual control intended for viewing data.
    • they can create an entry in the edit form in which there is a control to which they do not have rights, the value of these controls is then set to the database value “null”,
    • by default, they cannot delete a record in an edit form containing a control to which they do not have rights; enabling “Always allow deleting to the owner or co-owner of the record” in the “ActionButtons” control is an exception.
  • An application administrator can cause a logical error when a javascript function refers to a control that may not be accessible from a rights perspective for some users. The variable “ng_” is then replaced by the value stored in the database in this control instead of a reference to the object of the control itself. In most cases, this error stops loading the edit form, it disables itself, and the user is shown a message that he should contact the application administrator. This type of error can be partially eliminated using the report “Controls (JavaScript + insufficient rights)”. The substituted value is part of the page source code – therefore, the identifier of a control with a sensitive value should not be used in JavaScript or in the “HTML” control.

2.4.2. Show value

  • The display of the control value can be limited only to the users who created the database record, or are set as co-owners of all records in the edit form settings.
  • The radio button contains the following types of value display:
    • To all users – the control, including its value, will be displayed to all users who have the right to the control
    • Only to the owner or co-owner of the record – the control, including its value, will be displayed in the edit form only to users who have the right to the control and at the same time may edit the record – a user with administrator rights, a co-owner, and the owner of the record if “Editing rights” is set to “Owner or co-owner of the record”. The control cannot be displayed as a view table column. A detailed description of how the value is displayed in other controls is provided in the separate guide Edit form.

2.4.3. 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.5. “Administration” tab

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

2.5.1. Identifier

  • The identifier specifies the name of the database column in which the values entered through the control in the edit form are stored in the database.
  • The identifier is derived from the name of the control. It starts with the prefix “ng_”, and also contains the alphanumeric characters used in the control name (system controls do not include the prefix “ng_”). When changing the name of the control, the “Correct” button is available, which is used to correct the identifier, and thus to rename the database column in the database. For clarity and to eliminate possible misunderstandings, it is recommended to keep the identifier in accordance with the name of the control element. If external applications also access the database and, for example, read or write data to this column, it is necessary to schedule the change of the identifier until a suitable time.

2.5.2. Full identifier

  • The full identifier of the control also contains the identifier of the edit form, and is used in printing templates.

2.5.3. Index

  • Checking this box determines whether the column associated with this control should be indexed in the database. In the text field located below the check box, it is also possible to define the names (identifiers) of other columns that are to be in the associated index with this database column – they should be part of the index key.
  • For the MSSQL database, it is also possible to define “INCLUDE” columns to be attached to the index. However, they are not part of the index key, but the values of that key. The Firebird database does not allow this functionality.

2.5.4. Search in the view table

  • The search determines whether records in the view table should be searched using the “equals” operator after entering a search term or phrase in the database in the column associated with this control.
    • Deny – Searching in this column is disabled, and there is no check box in the view table to include the table column in the list of columns to be searched.
    • Allow – Searching in this column is enabled, and a check box appears in the view table, which can be used to include the table column in the list of searched columns.

2.5.5. Other

  • Change type to
    • The drop-down list offers the types of controls to which conversion of the control is supported, including automatic conversion of the data present in the database table.

2.5.6. Statistics

  • Statistics display aggregated information about control usage across the entire application.
  • Using the “Statistics” button, a detailed report of all places where the control is used or referenced is displayed, including the possibility to open the settings of such a place or control.
  • Before each change of control type or data type, or before each deletion of a control from the edit form, it is recommended to thoroughly go through the control statistics, and eliminate any misunderstandings.
  • Before each deletion of the control from the edit form, the evaluation of the statistics starts automatically. If the control is used or referenced somewhere in the application, it is necessary to confirm the deletion of the control again.

2.6. “Save”, “Save and continue editing”, “Copy”, “Delete”, and “Close” buttons

  • “Save” button – Saves changes to the control settings.
  • “Save and continue editing” button – Saves changes to the control settings and keeps the settings dialog open for further modifications.
  • “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

3.1.1. General functions

  • var s = control_GetValue(#ng_#);
  • var n = jsN(control_GetValue(#ng_#));
  • var n = jsN(#ng_#.value);

3.1.2. Integers

  • var s = jsNumberInWords(12345678, 'cs');

3.1.3. Decimal numbers

  • var s = jsNumberInWords(12345.678, 'cs');
  • var s = jsFormatDouble(1100.5412, 'kg');

3.1.4. Currencies

  • var s = jsNumberInWords(12345.678, 'cs');
  • var s = jsFormatCurrency(1100.5412, 'CZK');

3.2. Server functions

3.2.1. General functions

  • UPDATEAUTOSUM(id, form)
  • UPDATEAUTOSUM(1;2;3, form)

3.2.2. Integers

  • SUBSTRACTINTEGERS(10, 1)

3.2.3. Decimal numbers

  • COMPAREDOUBLE(1, <, 2, Ano, Ne)
  • FORMATDOUBLE(1000)
  • FORMATDOUBLE(1000, EUR)

3.2.4. Currencies

  • FORMATCURRENCY(1000)
  • FORMATCURRENCY(1000, EUR)