Worksheet Control

Worksheet control provides Excel experience for creating datasheet. Worksheet control can be used to show relationship data or reference data of the context item as datasheet. Columns in worksheet will be constrained with property data types. It supports nested rows from child relationships.

When ReferenceType option is selected for creating Worksheet control, a dropdown will be shown with all ItemTypes on which context ItemType is defined as item property. If there are multiple item properties of the context ItemType on the selected ItemType, a dropdown will be shown to select the specific item property.

You can navigate cells by navigation keys. It supports Excel type of Sorting and Filtering. You can copy paste between Excel file and worksheet control and vice versa. You can reorder columns by drag & drop operation. You can do all cell copy by dragging.

As part of adding worksheet control to the page, Aras ProAppDesigner shows Settings flyout where you can configure the RelationshipType that you want to associate with the worksheet control. RelationshipType can only be selected at the time of adding worksheet control. If you need to change associated RelationshipType, then you need to delete worksheet control and re-add it. Worksheet control has multiple settings that controls its appearance and behavior at runtime.

You can also configure individual columns on the worksheet control by selecting gear icon from the column. You can set Validation Rules, Default Value, Conditional Formatting, Read Only on the individual column by selecting gear icon from the column that opens Settings flyout. While defining expressions on the individual columns, you will only see properties from other columns of the same worksheet control.

Worksheet will always be shown with scroll to facilitate row reordering with drag-drop. In order to edit any row in the worksheet, you do not have to lock the row, if the context item is in the edit mode, all child items can be edited without applying the lock.

• Name: It is used to uniquely to identify control inside the template.
• Label: It is used to show title on the worksheet. It can be localized by defined Languages and Locales inside innovator.
• Columns: Using Columns settings section, you can add all the required columns to the worksheet. Columns can be added based on the RelationshipType or RelatedItemType properties. Using Column Width option, you can set column widths in percentage or pixels. If you select Column Width as Percentage, you never see horizontal scrollbar, widths of the columns will get adjusted to the available total width of the worksheet. If Column Width option is selected as Pixels, you will see horizontal scrollbar if the combined widths of all columns is more than available worksheet width. In pixel mode, columns can be reordered and resized at runtime. As part of adding column, you need to select the property from either RelationshipType or RelatedItemType, to which you want to bind the column. Columns added with RelatedItemType properties will be shown with (RI) in the column name. After adding columns, you can edit Width of the column, or you can reorder column using drop-drop icon from the Actions columns. You can hide or show column by selecting the checkbox under Hide column. You can delete existing column by selecting delete icon from the Actions column. By setting Freeze Column option, you can fix columns that are always visible and don’t move along with horizontal scrollbar. Freeze Column option is shown only in the Column Width Pixels mode.
• Insert Filter: It allows you to define the filter criteria for Insert Flyout. As an admin, you can define filter criteria that is used when the Insert Flyout is opened. As an admin, you can also enforce the filter, in that case end-user can see the applied filter on the Insert Flyout, but can’t change the filter criteria. If enforce filter is not set, end-user can change the filter criteria at the runtime.
• Insert Columns: It allows you to define the columns that need to be shown on the Insert Flyout. You can also define the width of the Flyout in percentage with respect to main page.

• Commands: Using Commands section, you can show or hide standard commands shown on the Worksheet toolbar. You can also define custom command by selecting Type as Button or DropDown and entering command name. For each custom command, you can set On Click, Can Show, On Load options.
  Each custom command can be configured with ‘On Click’ handler to perform certain action. It can show a shortcut to the existing Innovator actions like Structure Browser, Where Used, Workflow Signoffs etc. It can also be configured to call a custom script. By selecting Script option in the dialog, you can associate custom script that contains code to interact with other Worksheet columns or controls in the Wizard. Script can also have code to interact with server by sending a request.
  Each custom command can be configured with ‘Can Show’ option to define custom JavaScript that will return a boolean value. If returned value is true, command will be shown otherwise command will be hidden.
  Each custom command of type ‘DropDown’ can be configured with ‘On Load’ option to load options for the DropDown at runtime.
  For each command, you can configure the icon. You can also show or hide the command by selecting checkbox under Hide column.

  

  

  

  

• Cascade Delete: It defines how the rows in the worksheet should be handled when the context item is deleted.
  
  • Cascade Delete: Yes
    If Cascade Delete is set to Yes, when the context item is deleted, all the child items in the worksheet will get deleted.
  • Cascade Delete: No
    If Cascade Delete is set to ‘No’, deleting the context item won’t delete child items in the worksheet.

• Height: Worksheet control can be rendered in three different mode for the Height.

  

  • Height: Explicit
    In this mode, ‘Height’ should be supplied to limit the height of the worksheet in the page. Worksheet is rendered with the given height upfront, if it grows beyond this height then it will start showing scrollbar for the body of the worksheet.
  • Height: Fit to Content
    In this mode, worksheet control grows its height without showing any scrollbar. If it does not get required height by the page, then page will start showing scrollbar.
  • Height: Automatic
    In this mode, worksheet controls auto grows until it reaches available page height, after that it shows scrollbar for the body of the worksheet. This mode can be set on the worksheet control only when the page that contains worksheet control has only simple form fields apart from this worksheet control.
• Related Option: It controls how new rows can be added to the worksheet. This option is defined on the RelationshipType inside innovator, and it is read-only.
  There are three options available:
  
  • Pick only: You can select the Item which is already Present.
  • Create only: You need to create the Item.
  • Pick and Create: You can either select the existing Item or create a new Item.
• Show Label: It controls whether Label can be shown at the top of the worksheet control.
• Access: It allows you to define roles which can access the control. Roles (in the form group identities) can be at Show and Edit level. When control is hidden because you do not have defined role, relationship data associated with this control is not even fetched from server.
• Read Only: Based on the option selected for Read Only, control can become read-only even if you can edit the properties used inside the page with lock. You can select one of the options like ‘Yes’, ‘No’, ‘Expression’, or ‘Script’. Expression allows you to define a Boolean expression using properties from the current and previous pages (in case of multi-page application) to conditionally make the control read-only if expression is evaluated to true. Expression will be evaluated based on the cached property values on the client. Script allows you to write custom JavaScript code that should return Boolean value. If returned value is true, the control will be read-only.
• Display Condition: Based on the option selected for Display Condition, the control will be shown or hidden in the wizard at runtime. You can select one of the options like ‘Always Show’, ‘Always Hide’, ‘Expression’, or ‘Script’. Expression allows you to define a Boolean expression using properties from the current and previous pages (in case of multi-page application) to conditionally display the control if expression is evaluated to true. Expression will be evaluated based on the cached property values on the client. Script allows you to write custom JavaScript code that should return Boolean value. If returned value is false, the control will be hidden.
• Font: It allows you to set the required font and size for the cells including column headings of the worksheet control.
• Margin: It allows you to set the required top, right, bottom, left margins with respect to adjacent controls.
• On Before Load: OnBeforeLoad event allows you to write custom logic that will be executed before the data in the control is loaded. This will be useful to set the filter expression before loading the data. Please refer to the Template Customization section for more details.
• On Load: OnLoad event allows you to write custom logic that will be executed after the data in the control is loaded. Please refer to the Template Customization section for more details.
• On Insert: On Insert event will be launched after Item has been inserted in the Worksheet control. Event handler will receive inserted item details via eventData, this object will be available in the scope of the event handler code. Please refer to the Template Customization section for more details.
• On Insert Filter: On Insert Filter event will be launched after hitting the search button in Insert Item flyout. Admin can configure a custom script to modify entered filter expression or assign entirely new filter expression string to the eventData. EventData object is available in the scope of the event handler code. Please refer to the Template Customization section for more details.
• On Select: On Select event will be launched after selecting the item in the Worksheet control. Event handler will receive selected item details via eventData, this object will be available in the scope of the event handler code. Please refer to the Template Customization section for more details.