CS

Picker

Home / Documentation / Administrator guide / Edit form /

Picker

CS

1. General information

  • The picker is a dialog box that displays a view table with database records whose values can be copied or pasted into the currently open edit form. The picker is typically used to copy entire database records or to pass the value of a foreign key (ForeignKey).
  • The link to the picker dialog box with the view table and database records is a magnifying-glass icon, displayed on the edit form to the right of the control to which the picker is assigned.
  • The picker is available for the following types of controls:
    • TextBox
    • TextArea
    • RichTextBox
    • ComboBox
    • ListBox
    • MultiListBox
    • Radio
    • CheckBox
    • ForeignKey
    • File
    • Image
    • DatePicker
  • The picker also provides an AJAX-based autocomplete feature, which starts suggesting database records as soon as the user begins typing a value into the text field in the edit form. Autocomplete is available for the TextBox control, the ComboBox control that allows entering a custom value, and the ForeignKey control, and only when “Search for the value entered in the edit form” is checked and “Optimized search” is not.
  • The picker is configured in a separate dialog. A link to this dialog is represented by a magnifying-glass icon located in the lower-left corner of the control settings dialog to which the picker is assigned. The link appears only for a saved control.
  • For the picker icon to appear on the edit form, the picker must have a filled-in assignment table, i.e. at least one row with both a source and a destination selected. Setting the data source alone does not display the icon.
  • The picker icon is not displayed in an edit form that the user is not allowed to change (typically a record without the right to edit it, or a record locked by another user), nor in the bulk changes dialog. The icon is displayed for a “Read-only” control and the picker writes a value into it — this is the usual way of filling fields that the user is not meant to fill in by hand. The exceptions are “Read-only” controls whose value is held by the server — taking over values from the picker does not change them. These are controls with “Disable value assignment using JavaScript” checked and also “Read-only” controls with this way of calculating the default value:
    • TextBox and TextArea – “Current user”, “Current date”, “Current time”, “Current date and time” or “Current value in the database”
    • RichTextBox, CheckBox and ForeignKey – “Current value in the database”
    • ComboBox, ListBox and Radio – “Current user” or “Current value in the database”
  • The picker's view table must contain at least one column other than the “(ID)” column, otherwise the picker dialog box closes immediately after it opens. This most often happens with a foreign key picker whose assignment table contains a single row with the foreign key as the destination and the “(ID)” column as the source. In that case, either assign another column, or add at least one column on the “Help” tab. Columns from the “Activate search” tab are not enough to display the view table.

2. “General” tab

2.1. Tooltip

  • The tooltip determines the help text that appears when hovering over the picker icon in the edit form.
  • The same text is also used as the title of the picker dialog box; three dots at its end are omitted from the title. If the tooltip is empty, the dialog box is titled “Picker”.

2.2. Data source

  • The data source defines the data displayed in the picker's view table. This data is the result of a database query, which is evaluated when the picker window opens or when autocomplete starts suggesting values directly in the edit form.
  • Data source settings are made in the graphical designer of database queries.
  • When the data source changes, the columns of the assignment table are pre-filled automatically by matching control names.
  • The query conditions may contain values of controls of the edit form (#ng_...#). The current values from the open form are substituted into the conditions, including changes that have not been saved yet. An empty control value leads to an “is empty” condition — the condition is therefore not skipped.

2.3. Records per page

  • For clarity, the view table uses paging of the displayed records. This is useful in situations where the view table retrieves large amounts of data.
  • Records per page determines the maximum number of records that appear on one page. It is easy to switch between pages using the tools that each view table contains in its footer.
  • The same number also limits the AJAX autocomplete, which offers at most that many records and appends “...” after them. If the number is empty, 20 records are used.

2.4. Options

  • Search for the value entered in the edit form
    • For the controls TextBox, ComboBox allowing a custom value, and ForeignKey
    • Checking this box determines whether the value entered in the edit form should be searched for in the picker's view table and whether the edit form should offer an AJAX picker.
    • For a TextBox control with a date, time or number picker or with a color picker, the value is not searched for.
  • Optimized search
    • For all edit form controls with picker options
    • Optimized search means that each column of the picker's view table uses its own text box to enter a search term or phrase.
    • A normal (non-optimized) search means that one common text field is used to enter a search term, in which it is possible to list all search terms, and these are then searched in all columns of the view table at once.
    • Optimized search turns off the AJAX autocomplete as well as passing the value from the edit form to the picker search.
  • Show search results only
    • For all edit form controls with picker options
    • Checking this box determines whether the picker's view table should display records only when a search term or phrase is entered. Without a search term, the view table displays the prompt “Please enter a search term”.
    • In this mode, the search runs directly in the database and at most as many records are loaded as set in “Records per page”. Without this mode, the whole query result is loaded and searched only after loading.
  • Replace the value entered in the edit form
    • For the controls TextBox, TextArea, RichTextBox, and MultiListBox
    • Checking this box determines whether taking over the field value from the picker's view table to the edit form should replace the value or add it to the end. Added values are separated by a comma and a space (TextBox), by a new line (TextArea and RichTextBox), or added as another selected item (MultiListBox).
    • The option is evaluated on the control into which the value is taken over — that is, on the destination of the assignment, and for every picker that writes into this control. It can be set only in the picker of the given control, so a control without its own picker always has the value replaced.
    • When a value is being added, the current content of a TextBox control is not passed to the picker search. The AJAX autocomplete searches only for the text after the last comma.
  • Allow creating new records
    • For all edit form controls with picker options
    • Checking this box determines whether a button for entering a new record of the same agenda used as the data source of the view table will be displayed below the picker's view table. A detailed description is provided in the chapter Creating and opening records.
  • Allow opening records
    • For all edit form controls with picker options
    • Checking this box determines whether it should be possible to open individual records from the picker's view table and display the detail of a specific database record in the picker's dialog box directly in its edit form. The record is opened by a link in the first column of the view table.
  • Allow multiple records to be selected at once
    • For all edit form controls with picker options
    • Checking this box allows selecting multiple records in the picker at once. Individual records are selected in the picker by checking the checkbox located on each row of the picker's view table in the first column. In this way, however, it is possible to pick only the “(ID)” column, i.e. the primary key. The selected records are transferred to the edit form as individual primary key values separated by a semicolon. A detailed description is provided in the chapter Selecting multiple records at once.

2.5. Assignment table

  • Columns
    • The column settings determine which column of the picker's view table will be copied into which control of the edit form.
    • The destination can be any database control of the edit form except AutoSum.
    • Picked columns – those that have a source and destination set – are automatically displayed in the picker's view table in the order in which they appear in the assignment table.
    • The columns in the picker's view table can be hidden by selecting the “Hide” command from the drop-down list located to the right of the “Destination” drop-down list. A hidden column is not displayed in the view table and is not searched, but its value is taken over into the edit form.
    • A column is also hidden automatically when its destination is a TextBox, TextArea, CheckBox or ForeignKey control with “Hidden field” checked.
    • For columns in the picker's view table, it is possible to activate the search by selecting the “Activate search” command from the drop-down list located to the right of the “Destination” drop-down list.
    • If no search column is selected, the search is automatically activated for all columns of the picker's view table where this is allowed by the general view table search settings defined in the control's properties.
    • If the “Help” tab is empty, saving the settings ends with an error when all rows of the assignment table are hidden, or when only the primary key is selected in the assignment table — the picker's view table would have nothing to display.
    • An assignment whose source or destination control is deleted is removed from the assignment table — including pickers of other controls that used the deleted column.

3. “Help” tab

  • On the “Help” tab, it is possible to set a list of all other columns to be displayed in the picker's view table, even though none of these columns is picked.

4. “Activate search” tab

  • On the “Activate search” tab, it is possible to set a list of all other columns in which the search is to be activated.

5. “Other” tab

  • Template name
    • Picker settings can be saved to a template so that they can be easily copied when setting up the picker of another control that retrieves data from the same data source.
    • The template selection is offered once a data source is selected, and it contains pickers of controls of the same type whose data source reads from the same edit form and which have a template name filled in. Selecting a template copies the tooltip, the records per page, the picker options and the columns on the “Help” and “Activate search” tabs; it copies neither the data source nor the assignment table.

6. “Save”, “Reset assignment table” and “Hide – all at once” buttons

  • “Save” button – Saves changes to the picker settings.
  • “Reset assignment table” button – Removes all current assignments between the source and the destination across all rows of the assignment table. The values in the Destination and Options columns are reset to an unassigned state. Saving an empty assignment table removes the picker; the data source and the other settings remain saved and are offered again the next time the picker is set up.
  • “Hide – all at once” button – Sets the Hide flag on all rows of the assignment table.

7. Opening the picker

  • The picker is opened by clicking the magnifying-glass icon next to the control, on a mobile device in a new window. In the JavaScript of the edit form, it is opened by the picker_Open() function.
  • If “Search for the value entered in the edit form” is checked, the current value of the control is passed to the picker search and the view table opens already filtered.
  • The picker dialog box loads data only after it opens, because it first takes over the current values from the edit form (see the chapter Data source).
  • The picker's view table offers no export, no customization and no “Hide” icon for hiding the table content, and user changes of sorting are not saved for it. The “Default sort” button restores the sorting of the data source and the paging according to the records per page setting.
  • If the data source has “Time span by” set, a time span selection is displayed above the picker's view table, by default from one month back to the end of the current year.
  • Columns with “Show filter in the view table” checked offer a filter just as in a regular view table.
  • Values in the picker's view table are displayed without links and without units.
  • If a database query error occurs while loading data, the error message is displayed directly in the picker's view table.

7.1. Scope of the loaded data

  • Without “Show search results only” checked, the picker loads the whole result of the database query, and paging as well as searching take place only over the loaded data. For large tables, it is therefore necessary to restrict the data source by conditions, by the “Load only first” setting in the query designer, or to check “Show search results only”.
  • The “Load only first” setting takes precedence over the records per page.
  • The AJAX autocomplete loads at most one record more than the records per page setting. If “Remove records with duplicate” is set in the data source, it loads up to the “Load only first” limit, and if that is not set, the whole query result.

8. AJAX autocomplete

  • Autocomplete starts after the first character is entered into the control, with a short delay after the last keystroke. The Enter and Esc keys and the cursor movement keys do not trigger the search.
  • Offered records are displayed as values of the visible columns separated by a vertical bar, without formatting. Of the TextArea and RichTextBox columns, only the first one is displayed.
  • The Enter key takes over the highlighted record, and if none is highlighted, the first offered one.
  • Autocomplete searches the same columns as the picker's view table, except the hidden ones.
  • Autocomplete always takes over a single record, even for a picker with multiple selection allowed.
  • The “Pick first” button in the picker's view table takes over the first record found; it is displayed when a search term is entered and at least one record exists.

9. Taking over values into the edit form

  • Clicking a row of the picker's view table (or selecting a record in the autocomplete) writes the values of the source columns into the destination controls according to the assignment table. Columns from the “Help” tab are not taken over.
  • After writing, a value change event is raised on each destination control, so the JavaScript bound to the change runs and the form is updated.
  • The taken-over value is saved to the database only when the edit form is saved.
  • The way of writing depends on the type of the destination control:
    • TextBox, TextArea, RichTextBox, DatePicker – the value is written into the field, or added to the end (see “Replace the value entered in the edit form”). For a TextBox control with date and time, the value is split into both fields.
    • ComboBox allowing a custom value – the value is always replaced.
    • ComboBox, ListBox, Radio – the item is selected by value, or by the displayed text. If the taken-over value is not among the items of the control, it is still written into the record and the form is updated.
    • MultiListBox – all values of the source column are taken over, or added to the ones already selected.
    • CheckBox – the box is checked if the taken-over value matches the value that the checked box stores in the database, otherwise it is unchecked.
    • ForeignKey – the record ID is taken over and the displayed text is calculated according to the values query of the foreign key.
    • File, Image – the taken-over attachment is copied as a new attachment of the edited record. The copy is created already when the value is taken over, i.e. even if the edit form is not saved in the end.
  • The value of a TextArea source column with encryption checked is taken over as a password mask, not as the actual value.

10. Selecting multiple records at once

  • Clicking a row only marks the record; the takeover is performed by the “Take over” button below the view table. Without a marked record, the button displays the message “No records selected.”.
  • Primary key values of the marked records separated by a semicolon are taken over, into the destination of the first row of the assignment table.
  • In this mode, the picker settings dialog allows only an assignment table with the primary key as the source; otherwise it displays the message “The multi-record selection mode allows you to take over only the primary key.”.

11. Creating and opening records

  • The “New record” button and the link in the first column of the view table open the edit form of the data source directly in the picker dialog box. The rights to create and edit the record are evaluated in the same way as when the edit form is opened normally — the picker options only display the button and the link.
  • A new record is created as a standalone record without a link to a parent record.
  • An edit form opened from the picker is always displayed in user mode, does not offer the “Save and new” button, and does not perform redirects scheduled by the form's script after saving, deleting or running a script.
  • After saving, the picker returns to the view table with this single record and at the same time updates the edit form from which it was opened. The record is displayed in it only if it meets the conditions of the data source. After deleting, the picker returns to the full list.
  • The “Back” button in the edit form returns to the picker with the search term and marked records preserved; the “Reset filters” link does not preserve them.

12. Rights

  • Only a user with rights to the control to which the picker is assigned can open the picker; otherwise the dialog box closes and the autocomplete offers nothing.
  • An assignment to whose source or destination column the user has no rights is skipped. Of the columns on the “Help” and “Activate search” tabs, only those to which the user has rights are used.
  • Rights to the edit form, view page or records from which the data source reads are not checked — the picker displays all records that meet the conditions of the database query. The range of displayed records can be restricted only by the conditions of the data source.
  • Conditions containing values of controls of the edit form (#ng_...#) are not a security filter, because the values are supplied by the user's browser. A security restriction is therefore written using system variables substituted by the server (e.g. #loginname#, #userid#).
  • The picker settings dialog can be opened only by an administrator who is allowed to change the edit form of the control.

13. Other

  • When a control is copied, its picker is copied too. An assignment whose destination is the copied control writes into the new control in the copy.
  • A note of the edit form containing the text “DEBUGPICKER” writes the progress of taking over values from the picker to the browser console.