Support of SQL views in data grids

This article describes in detail the support of view-based grids in the (Undefined variable: CompoVariables_WA.Data) of the beqom application.

Introduction

In versions of the beqom application before 9.2, it was possible, in the (Undefined variable: CompoVariables_WA.Data), to open view-based data grids. However, these grids were read-only and for information purposes only.

Starting with version 9.2, beqom has implemented an edition mechanism for view-based grids in order to enable authorized users to edit the data of the base table(s) underlying the view.

Assumptions

Views are SQL database objects; as such, they are governed by SQL principles. The following assumptions are to be taken into account when working with views in the beqom application:

• Any modification, including UPDATE, INSERT and DELETE statements must reference columns from only one of the base table(s) of the view

• The columns of the base table being edited in the view must directly reference the underlying data in the table columns. I.e. the columns cannot be derived in any other way, including, without being limited to, the following:

  • An aggregate function, such as AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, VAR and VARP.

  • A computation; the column cannot be computed based on an expression that uses other columns. Columns formed using set operators UNION, UNION ALL, CROSSJOIN, EXCEPT and INTERSECT are considered a computation and, as such, cannot be updated.

• The columns being edited are not affected by the statements GROUP BY, HAVING or DISTINCT

• The statement TOP is not used anywhere in the select_statement of the view together with the WITH CHECK OPTION clause

Configuration

In order for a view to be editable from the beqom application, the view must be properly configured in the (Undefined variable: CompoVariables_AP.Admin) of the beqom Admin Portal, in the Synchronization & Data Security page.

Prerequisites

Three conditions must be met in order to be able to define a view as editable:

• The SQL object must be a view

• The type must be "Import"

• At least one column must be specified as unique

• SQL column names must not contain any spaces. Use <column_name>, rather than <column name>.

Importing and Configuring the View

To configure a view as editable in the beqom application, proceed as follows:

1. Open the beqom Admin Portal interface and then navigate to ADMIN>Synchronization & Data Security. A grid containing all the objects already synchronized in the application is displayed in the center panel.

2. Click the import icon in the toolbar located in the lower-left corner of the grid. A sub-panel is opened, below the grid.

3. Locate, in the list of available objects, the view that you want to import. Make sure that you only select objects whose type is "View".

4. In the Select Type drop-down list, select Import.

5. Click the + icon, in the lower-left corner of the center panel to import the object. Once the operation is performed, a success message is displayed.

6. Click the view in the list of imported object to display its properties. A sub-panel that enables you to configure the view is displayed below the grid.

7. Open the "Fields" tab of the view configuration panel. The list of all available columns is displayed.

8. Select, in the list, the column that you want to mark as unique. The settings of the field are displayed on the right of the list.

9. Check the Is unique box.

10. Open the "Properties" tab of the view configuration panel.

11. Check the Is Editable box.

12. Click the save icon located in the lower-left corner of the center panel. The view is now configured as editable in the beqom application. If you have not configured a column as unique, you will not be able to save the view configuration.

Configuring a data grid Based on a View

Once you have imported the view and properly configured it, edition options will be available for individual fields (depending on their type) when you can create a data grid based on the view, just like for any other grid based on a single table.

To create a data grid based on the newly imported view, proceed as follows:

1. Open the beqom Admin Portal interface, and then navigate to DATA>Administration>Grid Editor. The list of all available data grids is displayed.

2. Click the + icon located in the lower-left corner of the center panel to create a grid. A new sub-panel is opened below the object grid.

3. In the "Properties" tab, define the basic parameters of the grid, specifically, for this use case:

   • Enter a name of the grid in the Grid Name field

   • In the Table/View field, select the view that you have previously imported and configured

   • Define the permissions related to the grid, in the "Permissions" section

4. Click the save icon located in the lower-left corner of the center panel.

5. Open the "Columns" tab.

6. Select the column that you want to configure in the "Fields" list.

7. In the "Configurations" tab, check/uncheck the following options as required:

   • Editable

   • Mandatory

   • Bulk Apply

8. Configure the rest of the options and columns as required.

9. Open the "Properties" tab again.

10. Click the save icon located in the lower-left corner of the center panel. The data grid based on the view is now configured with edition options. In the interface, the edition options will be available for the grid as configured.

This is an attention message.

The Editable, Mandatory and Bulk Apply options are available only if the view has been properly imported and synchronized using the procedure described in Importing and Configuring the View.

Using View-Based data grids in the beqom Interface

When a view has been imported and a grid has been properly configured, the following features are available in the beqom user interface for view-based grids:

• Data edition

• Row addition

• Row deletion

• Bulk apply

• Export with editable cells

• Import

• Use as attached object