Names (Metadata Editor)

The named range metadata editor is a designer module that allows users to override internal system assertions, down to the cell-level. This allows the system to be flexible enough for the user to make modifications to some of the system's core assertions regarding cell information within the designer, rather than requiring direct alterations to the workbook model.

What is a Named Range?

A named range is an Excel concept that indicates a unique name that typically refers to a set of cells from within the workbook. There are also named ranges that do not point to a specific set of cells, but these are not supported for mapping from within the Designer platform.

For example, you may have a named range called HelloWorld that, in turn, points to a set of cells 'Sheet1'!$A$3:$A$4. When referring to named range HelloWorld, we are actually referring to the underlying set of cells from within the workbook.

Throughout the system, the Designer will make reference to named ranges, as this is the primary construct by which the designer interacts with the spreadsheet model.

To utilize a cell or series of cells in the Designer, you must first define that range as a Named Range in Excel.

As a result, it is prudent to go through your workbook and identify inputs and outputs before beginning the development process. Of course, if you need to add additional ranges in the future, you can simply modify your workbook and then re-upload the new version from the Edit Application page of the Designer.

In many portions of the system, we identify named ranges by their respective sizes:

• Single Named Ranges (SNR) are ranges that refer to a single cell. These ranges are 1 x 1 (Width x Height) and have clearly identifiable types. For example, you may see reference to Boolean Single Named Ranges, which are SNRs that evaluate into TRUE or FALSE. These are the baseline for binding any single columns on a database table or single inputs, such as Textboxes or Calendars.
• List Named Ranges (LNR) are ranges that refer to a one-dimensional set of cells. These ranges are N x 1 or 1 x N (Width x Height) and are most commonly utilized in defining lists of various types throughout the system, such as those associated with Radio Button Lists or Dropdown selection values.
• Table Named Ranges (TNR) are ranges that refer to a two-dimensional set of cells. These ranges N x M (Width x Height) and are most commonly utilized when binding grid-type inputs, such as Output and Input Grids, or defining ranges for One-to-Many Secondary Tables.

What is Named Range Metadata?

If named ranges are an Excel-based construct that ties unique names to a set of cells, then it follows that named range metadata is a set of data about those named ranges.

Named range metadata is the core of all Designer-based applications. This information, auto-generated from the target Excel model, is utilized throughout the system when mapping various module functions back to the calculation model.

For example, when you map a single named range to a textbox, that tells the system that the values that are set by the user at runtime within that textbox should be mapped to the corresponding named range in the workbook. Likewise, when you associate a radio button's list property with a list named range, that identifies to the system that the options available in that radio button should be pulled from that named range at runtime.

For the system to operate efficiently and effectively, it must be able to capture more than just the basic sizing information regarding the named range. All throughout the system, there are hooks that aim to help the user with their mappings, such as information regarding the default values within given single-named ranges, suggestions for control naming, and grid column header and type detection.

For this purpose, the system generates a set of data regarding each of the named ranges within the workbook, including properties such as Type, Format, List Reference, Default Value, and more.

What is the purpose of editing Named Range Metadata?

Since this information is already being captured, what is the purpose of being able to modify it?

Simply, the goal of the Designer is to be as smart as possible while still allowing for total user flexibility. Excel files - in general - can contain a lot of ambiguity, especially concerning the contents of a given cell. In some instances, the cell can be a Text type while in others a Number. Very often, the contents of a given cell are defined by user entry within the cell itself. For example, consider a Blank input cell where the value is meant to be numeric for the calculations to succeed.

While the system would like to make strong assertions about how named ranges can be bound in order to make it easier for users while building their applications, it is not always plausible to do so as a result of these ambiguities. For example, indicating that only a single named range with a type of Date can be utilized for a calendar control is not possible since the cell may begin as empty (no format or default value) but should ultimately include a date or time value.

In order to guide the application development process with smarter rule sets, the Designer must also include a means to override these determinations. This is the purpose of the named range metadata editor: to allow users to tell the system concretely how named ranges should be handled, in the case that the detected metadata does not show the full picture.

How does it work?

At the top of the metadata editor, you will notice a dropdown labeled Named Range - this will allow you to select from a full list of applicable named ranges that were detected within the updated workbook. Selecting a name from the list will populate several pieces of information:

• The selected named range's name, as well as its target reference cells, will be displayed in the panel below.
• The dimensions of the selected named range will appear below the named range's reference information.
• A grid, containing all of the metadata for the target named range, will appear at the bottom of the page.

The Grid

The grid containing the named range's metadata represents a full data set of information regarding each independent cell within the associated named range. The metadata is categorized as follows:

• Cell (Non-Editable) : The specific cell within the workbook that is being referred to. This is the common cell reference (e.g. A1, B5).
• Type (Editable) : An internal type that is associated with the target cell. This is often used to filter down named ranges throughout the system for only specific types (e.g. 'Boolean' named ranges are single named ranges where the single cell's type is Boolean). The possible values are Undefined, Boolean, Number, String, Date, and List.
• Format (Editable) : An internal format type that is associated with the target cell. This is often used during suggestion determination (e.g. what type a column should be in a grid). This is partially contingent upon the Type for the cell.
• Format String (Editable) : The Excel-based format for the given cell. This may be a custom format, or one of Excel's many built-in formats. This is primarily utilized when displaying the Text output from a given named range, as will be the case in the upcoming Use Cell-based Formatting input grid option.
• Default Value (Editable) : The default value in the cell when the workbook is first loaded up. This is often used for initializing a runtime instance, defaulting the Value property of a given control, or for the Pull Default From Metadata option that exists in many single named range controls.
• Coordinates (Non-Editable) : The relative positioning of the cell within the target named range. For example, the cell at the top-left corner of the named range is at position [0,0]. This is often used for uniquely identifying cells within a given named range and merging named range metadata information between workbook revisions.
• Is Formula (Non-Editable) : This indicates whether a formula was detected within the cell. This is utilized for system optimization, such as static-list detection for dropdowns and radio buttons and editability of columns in input grids.
• List Reference (Editable) : This indicates whether a cell is associated with list data validation, as well as which list it is tied to. The values here only show up when the Type of the cell is List. This is often used for auto-detection of list named ranges during control binding (e.g. radio buttons, dropdowns, input grid list columns).

Editing Metadata

To edit a piece of metadata information, simply click on the respective cell and override the value (either via) a dropdown or through standard text entry. Note that some properties are contingent upon other factors (such as list reference requiring Type to be set to List for the cell).

Additionally, you can utilize the Search option to filter down to a specific cell, row or column. For example, a search value of [0, will provide all of the cells within the first column of a named range (based on the Coordinate column).

Large named ranges will occupy multiple pages and you can navigate between then using the navigation bar in the bottom-right corner of the grid. Additionally, each column is sortable and the total entries shown on the screen is configurable in the top-left corner of the grid.

Upon overriding any particular property, the background of that value in the grid should appear in highlighted yellow, to make it clear that it was overwritten.

Clear Overrides

The Clear Overrides button, located above the grid, can be utilized to clear out all of the overridden cell metadata. Overrides can be cleared row by row using the red action button in the Actions column for each row having an override. Note that all data for the given named range will be restored with the system-detected metadata upon activating this function.

Tips

• When setting format strings, you must use the Culture specific formats. For example #,##0.0 is an acceptable format for a decimal in the "Thousands Comma Delimiter, Decimal Period Delimiter" Culture. On the other hand, for "Thousands Space Delimiter, Decimal Comma Delimiter" Culture setting, # ##0,0 format string will have a similar effect.