Input Grids

An input grid is a control that allows the user to select and edit a range of items corresponding to a Table Named Range (TNR) from the spreadsheet model. These controls must be bound to a Table Named Range (TNR) (i.e. a two-dimensional range, including a width and height larger than 1 cell) and will contain an amount of rows and columns equivalent to the target range. Please note that input grids are limited to 10,000 cells by default to prevent any performance issues. If your table named ranges contain more than 10,000 cells (i.e. a grid with 10 columns and 1000 rows) you will get an error. This limit can be changed in server and private cloud licenses.

Table Named Range

A list of all Table Named Ranges (TNR) defined in your spreadsheet model (i.e. a two-dimensional range, including a width and height larger than 1 cell).

These named ranges may correspond to both input (user-entered) and output (formula-based) cell values.

This list will exclude Table Named Ranges (TNR) that are already bound to existing input controls.

Upon selecting a target named range, a process will begin to generate all of the metadata for the cells contained within this named range. Once this process has completed, a list of detected columns will be present below, including a Confirm button to validate the existence of each detected column.

Users should confirm the existence of the columns and then subsequently Submit the control in order to save and update the column properties for subsequent edit.

Note: To mitigate detrimental performance side effects, the maximum allowable cells within a target range is configurable by the system administrator in the Configuration Settings section of the Designer. By default, the maximum cells allowed in a given Table Named Range is 1000.

As a result, users attempting to generate grid controls using named ranges exceeding this value will not be able to generate columns for these controls, preventing submission. To exceed the maximum target cell count, please reach out to your system administrator.


Has Header

Select this feature if the selected named range contains headers in its first row.

This has an important effect upon calculations and can also aid in the auto-generation of columns for this grid control, so ensure that this value is updated appropriately


Hide Header

Enable this feature if you’d like to hide the column headers in the first row.


Type of Input Method

This option governs the way a user can edit data displayed in the input grid.

There are three types of input styles:

  1. Inline: Allows editing each grid cell separately by clicking the cell (or navigating with cursor keys on your keyboard into a cell from another cell) and typing the desired value.
  2. Form Based: An this mode you can edit grid data row by row, instead of doing this cell by cell. There are three buttons located above the grid that control Form Based grid actions:
    • New: Adds a new row through a special modal window that contains a form (hence the name of this style) containing input controls, representing each corresponding field. Adding new rows is only possible if the user has previously removed any rows from the grid, because the grid can contain a maximum number of rows equal to the maximum height of the corresponding Table Named Range (TNR). For example, if the corresponding Table Named Range (TNR) defined in the Excel model has a maximum 10 rows, and the grid is already displaying 10 rows, then clicking the New button will not allow adding another row. Instead, you will see an error message.
    • Edit: The entire row can be edited at once, through a modal window containing a form. To edit a row, you must first select it by clicking, then click the Edit button and make any necessary changes. You can also edit multiple rows at once. To do this, you need to select multiple rows in the grid before clicking the Edit button. You can use the SHIFT or CONTROL buttons to select multiple rows.
    • Delete: Once the row(s) is selected, you can remove them from the grid by clicking the Delete button.
  3. Form Based Start Empty Grid: Works the same way as the Form Based grids, except that the input grid will be empty (regardless of the Table Named Range (TNR) contents) once the application is loaded. The table can be populated with data manually through a modal window containing an input form.

Responsive

This feature will determine whether or not to collapse columns that exceed the width of the viewable screen under the record’s row.

This is advised for output grids, as it can aid in displaying information to end users that are accessing the application from mobile devices.

This is not advised for input grids, as it can cause undesirable column resizing and recalculation.


Scrollable

This option is available only for input grids where the Type of Input Method is set to Form Based or Form Based Start Empty Grid. If this option is enabled, the modal form that is displayed when adding or editing a row will have a fixed size on the page, and a scroll bar. This feature is intended to help with screen real estate in tables with too many columns.


Pagination

Enable this feature to allow for pagination on your grid. All functions will work appropriately across pages, such as sorting, searching, and copy/paste.

This is particularly useful for large grids that often cause significant performance detriments in the user interface due to grid rendering.

Only default pagination options are currently available, but future releases will enable more user control over these features.


Enable Copy to Clipboard

Selecting Yes will add a Copy button to the top of the input grid table which can be used to copy the entire table into the clipboard. You can then paste this data into other sources or documents.

 


Enable Print Button

Selecting Yes will add a Print button to the top of the input grid table which will allow users to get a printout of the entire table from the user interface. All existing data will be carried over to the export file.


Enable Export to CSV

Selecting Yes will add a CSV button to the top of the input grid table which will allow users to get an export of the entire table from the user interface in CSV format. All existing data will be carried over to the export file.


Enable Clear All Button

Selecting Yes will add a Clear All button to the top of the input grid table that will allow users to clear the grid’s contents. The behavior of this action will depend on the input style of the grid:

  1. Inline and Form Based: will set an empty value to each of the cells.
  2. Form Based Start Empty Grid: will remove all rows, resetting the input grid to its initial state.


Use Metadata Formatting

This option configures the grid to handle data formatting per-cell rather than per-column, which allows each cell of a table utilize a different value format.

The information for these cells will be pulled from the target Table Named Range (TNR), based on the information found within the Excel model. For example, a cell that has a Date type with formatting MM/dd/yyyy will automatically become a calendar-based input and will display the associated date in the target format.

Note that data entry will still comply with the pre-existing formats (as if you were to have set the column to Date). Thus, the input mask (while entering the input’s value) may differ from the output format (the value that is displayed once the user has submitted or modified the value).

This feature may not be available for very large tables – this is dependent upon the size of the Table Named Range (TNR) and your server configuration. If the table’s cell count exceeds configuration setting (called MaximumCellCount, defaulted to 1000), then the server does not generate a full set of the required metadata for these ranges. Thus, the Use Metadata Formatting feature cannot be utilized in these cases.

If you want to use the Use Metadata Formatting feature for large tables, then you may want to consider increasing the MaximumCellCount configuration setting to an appropriate value. For example, if the Table Named Range (TNR) represents a table having 10 columns and 200 rows, then the MaximumCellCount should be set to at least 2000 (10 x 200).

Disabled

The default mode (when this feature is disabled, or Use Metadata Formatting is unchecked) allows the user to set a Mask Type for each column separately. In this case, all cells in that column will use the same value formatting. For example, all data in the column are displayed either as percentages, dates, currencies, etc. For more information, see the Mask Type section on the Input Grid Column help page.

Enabled

Once you have enabled the feature (Use Metadata Formatting option is checked), each cell will be able to receive its own data formatting. This feature is called metadata formatting because it is based on the metadata information that is primarily collected from the provided spreadsheet model.

In this mode, the input grid table will allow each cell to use the same format that is set in the spreadsheet file, meaning that the values in each cell will be displayed in the same way as they appear in Excel.

Moreover, these formats can also be overridden in the Metadata Editor (Names) when required. For example, if you want to modify a cell’s format from decimal to integer but prefer to leave the cell’s format in the spreadsheet file as-is, you can simply set the new format for that cell in the Metadata Editor.

Also note that – in this mode – the column Mask Type options are not available, since the grid will be formatted on a cell-by-cell basis, rather than column-by-column.


Header Alignment

This feature will determine the alignment of all text within the header row of the grid.


Content Alignment

This feature will determine the alignment of all non-header text within the body of the grid.


Visible

Indicates whether the input grid control is visible.

This can be configured as Always or Never to indicate whether the input grid is statically visible or not.

Alternatively, if any Boolean Single Named Ranges (SNR) exist (i.e. ranges pointing to a single cell that evaluates to TRUE or FALSE), then the visibility of the input grid can be configured to reflect the value of that named range using the ‘By Value Of’ setting.


Trigger Calculations

If Trigger Calculations is selected, changing the value in any cell within this control will trigger a calculation. Calculations perform a server call with all of the inputs from the user interface and populate the output controls with the values from the updated calculation.

In workbooks with complex calculations, enabling calculations may lead to delays in the user experience.

The best practice when determining whether an input should trigger calculations should take into account the following factors:

  1. Does this input affect any calculated values? If the field is simply informational, then there is no reason to trigger a calculation.
  2. Does this input affect my user interface? If the field can change the visibility or enabled-ness of pages or other controls, then it is a good idea to trigger calculations when changes occur to the input.
  3. Will something else trigger calculations before this input is relevant? If the user won’t see the results of the calculations until reaching a different page, then you can hold off on triggering calculations until a button press or a different control’s value is changed.

Regenerating Columns

When the width of a Table Named Range (TNR) has been modified, it is important to perform the Regenerate Columns action in order to reconfigure the columns within the grid control.

Without performing this action, the values sent to the calculation model may not be represented in the appropriate location.

Jean has a table named range in her workbook:

Name Age HasPets
George 26 TRUE
Jim 42 TRUE
Sam 45 FALSE

In a new iteration of her workbook, she adds an additional column called PetCount in order to quantify the number of pets that each individual has. She inserts the column after HasPets and uploads the new model to the Designer:

Name Age HasPets PetCount
George 26 TRUE 2
Jim 42 TRUE 3
Sam 45 FALSE 0

Now, Jean needs to Regenerate Columns on her grid; otherwise, the new column for PetCount will not be visible from the user interface.

If you try to publish an application with a grid that has a column count that does not match the column count of the underlying named range, then you will be presented with a validation message that prompts you to regenerate columns for the target table.


Editing Column Properties

Below the Regenerate Columns button is a set of buttons, each representing a single column from the grid. Clicking the button corresponding to a column will allow editing the column’s properties.


Changing the Column Order

To change the order of the grid columns, drag the (≡) buttons next to a given column to the desired position.


Actions

This section includes a series of buttons that are useful for applying changes to your control.


Reset Column Widths

Clicking this button will reset the grid’s column widths to their default values (i.e. column widths will be automatically adjusted according to their contents).


Distribute Column Widths

Clicking this button will set the column widths of all active columns to Percentage and will evenly distributed the values (i.e. all columns will have the same width). For example, a grid with four columns will have each column set to 25% of the total width.


Grid Helper Classes

You can use the class names below to change the formatting of input grids.

  1. input-grid-header-{PageId}-{ControlId} Added to the “<thead>” tag of input grid
  2. input-grid-header-row-{PageId}-{ControlId} Added to each child “<tr>” tag of “<thead>” tag
  3. input-grid-table-body-{PageId}-{ControlId} Added to the “<tbody>” tag of input grid
  4. input-grid-table-row-{PageId}-{ControlId}-{RowIndex} Added to each child “<tr>” element of the “<tbody>” tag
  5. input-grid-table-cell-column-{PageId}-{ControlId}-{RowIndex}-{ColumnIndex} Added to each child “<td>” element of the “<tr>” tag

For example, if you want to change the header color, and the 8th column of an input grid. Go to
Designer Home Page / StyleSheets / Add Style Sheet and add the text below into the stylesheet editor
This will change the header color of the input grid to orange, and the header background will be red. The background of the 8th column will be blue.


Copy & Paste support

The input grid control includes support for copying and pasting.

Copy (CTRL+C)

If you want to copy contents into your clipboard, you will first need to make a selection. Input grid controls allow you to select either a rectangular cell range (Cell Selection) or multiple columns (Column Selection).

  1. Cell Selection You may select a range of cells by using the SHIFT key and clicking two cells. This will define the diagonal dimension of the rectangular range. This behaves similarly to selecting a cell range within Excel.For example, hold the SHIFT key and click a starting cell which will indicate the beginning of a rectangular selection. Subsequently, holding SHIFT and clicking on another cell will mark the diagonal corner of the desired rectangular cell selection. In this sense, the start and end cell selection define the top-left and bottom-right corners of a rectangular range and all cells belonging to the rectangle will become selected. Each consecutive SHIFT and click of any cell will redefine the rectangular selection to include the new cell as the end cell, retaining the original starting cell.Note that if you do not hold the SHIFT key while clicking a cell, it will instead activate Edit mode for that cell (rather than Select mode). When a cell is already in edit mode, holding SHIFT and clicking on another cell will turn the original cell into the start cell of a new selection, with the end cell being the clicked cell. This way, you do not need to SHIFT and click two cells, since the Start cell is already assumed to be the one that was originally in Edit mode.
  2. Column Selection Another type of selection supported by input grids is the Column Selection. You can select multiple columns by holding the SHIFT key and clicking on two column headers, which will select a range of adjacent columns. Alternatively, you can hold the CTRL key and click multiple column headers to select a set of non-adjacent columns.For example, holding the SHIFT key and clicking a column will indicate the first column in a column range selection. Subsequently, holding SHIFT and clicking another column will mark the end of the column selection. All columns between the first and the last clicked column will become selected. Each consecutive click of any column (with the SHIFT key pressed) will redefine the column selection up to the most recently clicked column.When selecting columns, you can also use the CTRL key instead of SHIFT key. The CTRL key also allows you to add and remove a desired column from the current column selection. For example, if a column is already selected and you hold CTRL while clicking the column header, then the column will become deselected.Note that if a column is sortable (the Support Search option is enabled for the column), it will not sort the table if you click a column’s header while holding the SHIFT or CTRL key.

Once you make a selection (either Cell Selection or Column Selection), hold CTRL and press C to copy the selected contents into the clipboard. This copied data can be pasted to external programs, such as Notepad or Excel, as well as to an input grid in the designer.

Paste (CTRL+V)

For pasting multiple cells or columns into an input grid table (assuming that the data is already copied into the clipboard), you must simply click a cell, hold CTRL and press V. The data will be pasted with the selected cell constituting the first cell (top-left) of the pasted data set.

If the data stored in the clipboard does not include multiple cells, then the value will be pasted into the currently selected cell. You need to have at least two cells copied in the clipboard to have the values pasted into different cells of the input grid.

The data being copied and pasted mimics the way that the input grid is currently displaying the data. For example, if you sort the table by clicking a sortable column, then copying and pasting will utilize the sorted data, rather than the original data. For the same reason, hidden rows and columns are never copied and cannot be pasted into.

While you can copy from disabled cells and columns, you cannot paste into them – their original contents will stay intact.

When pasting data from cells and columns of one type into cells and columns of another type (e.g. from Decimal columns into an Integer) the pasted values will always be matched against the type of the target cell, which may result in modification of the pasted data. In the noted example, a decimal value of 1.23 being pasted into an integer cell will result in the decimal part of the number being stripped out (leaving a value of 1). In other cases, when the copied values cannot be represented in the target cell, a blank value will be pasted instead.


Input Grid Column

An input grid is a control that allows the user to select and edit a range of items corresponding to a Table Named Range (TNR) from the spreadsheet model. An input grid column constitutes a single column of that input grid, which – in turn – refers to a single column of the corresponding Table Named Range (TNR).


Properties

The properties of an input grid column are outlined below.


Title

Sets the header label for the column in the input grid.


Tooltip

Sets the tooltip text value that displays in a bubble when the end-user hovers over the column header.


Support Sorting

Defines whether or not the column supports sorting. If sorting is allowed for the column, an additional property – Sort Type – will become visible.

Sort Type defines the type of sorting that will apply to the values in that column. This includes Default, Numeric, DateTime, and Natural sorting options.

  • Default sorting is a standard character-based sorting operation.
  • Numeric sorting is a numeric value-based sorting operation.
  • DateTime sorting is a date-based sorting operation
  • Natural sorting is a chunk-based alphanumeric sorting operation, in which alphabetical and numeric chunks are split out of the string value and compared individually until a greater-than or less-than comparison can be performed. For example, natural sorting will produce [“1 Main St”, “2 Main St”, “11 Main St”] whereas default sorting will produce [“1 Main St”, “11 Main St”, “2 Main St”].

Support Search

Support Search allows you to define whether or not the column is searchable. If any single column is searchable, the input grid will be rendered with a search box in the upper-right corner. This allows the end user to filter down the grid to applicable rows based on a search operation that takes into account the sortable columns.


Mask Type

The Mask Type determines the format of the column values, as well as the acceptable values that can be entered into an editable columns.

This option is available only when the input grid has the Use Metadata Formatting feature disabled.

The following mask types are valid:

  • None – Freeform string entry is accepted, irrespective of numeric or alphabetical entry.
  • Boolean – A checkbox will be rendered in each cell within the column, corresponding to a TRUE or FALSE value in the workbook cell.
  • Decimal – Only numeric values, including decimals, constitute valid user data entry in this column.
  • Percentage – Only numeric values, including decimals, constitute valid user data entry in this column. The values will be presented with a percentage mask. The underlying values will be decimals (e.g. 50 % => 0.50).
  • Currency ($) – Only numeric values, including decimals, constitute valid user data entry in this column. The values will be presented with a currency mask (i.e. $#,##0.00). This is the mask to use for USD ($) format.
  • Currency (€) – Only numeric values, including decimals, constitute valid user data entry in this column. The values will be presented with a currency mask (i.e. €#,##0.00). This is the mask to use for EUR (€) format
  • Currency (€) – Only numeric values, including decimals, constitute valid user data entry in this column. The values will be presented with a currency mask (i.e. £#,##0.00). This is the mask to use for GBP (£) format
  • Time – Only valid time values can be entered in this column.
  • US Date – Only valid date values can be entered in this column. Selecting a cell from this column will pop up a datepicker control where the user can select a date from a calendar. This is oriented towards United States users, where the common date format include the month first, followed by the day (MM/DD).
  • EU Date – Only valid date values can be entered in this column. Selecting a cell from this column will pop up a datepicker control where the user can select a date from a calendar.This is oriented towards global users, where the common date format include the day first, followed by the month (DD/MM).
  • List – This special mask is no longer available in Mask Type dropdown. Instead there is a new option Include Dropdown described below.
  • Telephone: You can use “Telephone” type for entering telephone style inputs. Default telephone mask will be “+111 (1111) 111111 1111111”. User need to use space character to navigate between sections.

Include Dropdown

This option designates that entry in cells from this column must be selected from values within a list. Selecting this mask will show a List Named Range dropdown in the page, where you must designate a correspondingList Named Range (LNR) from the workbook. When the user selects a cell from this column, values from the corresponding List Named Range (LNR) will be visible for selection.


Is List Static

This value is determined automatically based on the contents of the associated list named range. If that range has formulas, then the list is considered static. Do not override this behavior unless you have clear instruction or understanding of this functionality.

When this option is enabled, the system assumes that the selected list for this dropdown control is a range containing solely static values. This means that the list does not have values that are dynamically controlled by formulas. As a result, the system will not re-retrieve and populate this list on each request. Having this option enabled will improve calculation performance for applications with large, static lists. However, it will also prevent the list’s options from updating dynamically.


Show Formatted List Options

This option is available only when When Include Dropdown is selected. When enabled, this feature displays the formatted value of the list options in the dropdown, as they would be displayed in Excel.

The value that is submitted for calculations is still the underlying, non-formatted value.


Enable Row Visibility By Value

This option allows showing or hiding a row based on the outcome of a Boolean field. Mask Type must be set to Boolean for this option to be available.

When enabled, this option only shows a row based on the value of the selected Boolean field. A True value indicates that the row is visible, while False hides the row.

If more than one column has this option enabled, the corresponding row will be visible if at least one such column has value True in the row.


Enable Row Editability By Value

This option allows disabling a row based on the outcome of a Boolean field. Mask Type must be set to Booleanfor this option to be available.

When enabled, this option disables a row based on the value of the selected Boolean field. A True value indicates that the row is enabled and editable, while False disables the row and prevents the row from being edited. If the grid is Form-Based, then this will also prevent the row from being removed.

If more than one column has this option enabled, the corresponding row will be disabled if at least one such column has value False in the row.


Precision

The precision constitutes the number of values post-decimal for numeric fields that support decimal input. This is only available if the column’s mask type is Decimal, Percentage, or Currency.


Is Editable

Indicates whether or not this column is editable by the user; if not, then the user is not permitted to enter values into the column. Since input grids support columns containing both formula-based cells and input cells, it is strongly suggested that you make formula-based (or output) cells non-editable.


Has Formulas

Indicates whether or not this column contains formulas in any of cell belonging to a column. It is important to set this value appropriately as it may affect grid display behavior.


Visible in the Grid

Indicates whether the column is going to be visible.

Inline Grids

Inline Grids setting can be configured as Always or Never to indicate whether the column is to be permanently visible or hidden.

Alternatively, if any Boolean Single Named Ranges (SNR) exist (i.e. ranges pointing to a single cell that evaluates to TRUE or FALSE), then the visibility of the column can be configured to reflect the value of that named range using the By Value Of setting.

Form Based grids

Form Based Grids can be configured as Yes or No to indicate whether the column is to be made permanently visible or hidden.


Column Sizing Type

Determines how the column is to be partitioned in the input grid. You can select from 3 options to determine column width.

Auto

Setting the Column Sizing Type to Auto will make the system automatically set the column width based on the input properties and other columns present.

Pixel

Selecting Pixel for the Column Sizing Type will create a new option called Column Sizing Value. You can enter the desired number of pixels into the Column Sizing Value field to resize the column. For example, entering 100 will make this column take up 100 pixels in the input grid.

Percentage

Selecting Percentage for the Column Sizing Type will create a new option called Column Sizing Type. You can enter the desired ratio (0-100%) into the Column Sizing Value field to resize the column. For example, entering 20 will make this column take up 20% of the entire input grid.


Visible on the Form

Please note that this field is only available for the Form Based and Form Based Start Empty grids. This setting can be configured as Always or Never to determine whether the column is to be made visible or hidden on the modal input form, when adding a New row or Editing an existing one.

Alternatively, if any Boolean Single Named Ranges (SNR) exist (i.e. ranges pointing to a single cell that evaluates to TRUE or FALSE), then the visibility of the field on a modal input form can be configured to reflect the value of that named range using the By Value Of setting.


Required

Please note that this field is only available for the Form Based and Form Based Start Empty grids. However, the Is Editable option also must be enabled to activate this setting. This option can be configured as Always or Never to determine whether the column value is required on the modal input form when adding a New row or Editing an existing one.

By Value Of Column option allows configuring whether the column is to be made required based on the value of another column in the same row.

Alternatively, if any Boolean Single Named Ranges (SNR) exist (i.e. ranges pointing to a single cell that evaluates to TRUE or FALSE), then the required-ness of the field on a modal input form can be configured to reflect the value of that named range using the By Value Of setting.