ERP 2.50:Developers Guide/Concepts/AD/Dataset
|ERP 2.50:Developers Guide|
The Dataset concept allows you to define sets of data from different tables and export this data in one step. Datasets are especially usefull for managing reference data for a module, for example tax rates or default data in new tables added by a module. The reference data is packaged, distributed and installed together with the program code implementing the module.
The content of a Dataset is defined by its Dataset Tables and Dataset Columns. The first defines which tables are exported, the second which columns of each table are executed.
A Dataset is a grouping of different tables (entities) which are exported together. Datasets are defined through the Dataset menu in the Application Dictionary. Below is an example of a Dataset.
Some important things to note:
- A Dataset belongs to a module, so modules can add Datasets and define their own Datasets.
- Data Access Level: filters the tables which can be selected for this dataset, only tables with the set data access level can be included in the data set (see here for more information on data access level).
If the export allowed column is flagged then an export button is displayed. When clicking this button the data is exported to the modules directory of the module to which the Dataset belongs:
The DatasetTable defines which data of one table is to be exported. It defines both the records (through a where clause) and the columns per record. A Dataset can have zero or more Dataset Tables, a Dataset Table belongs always to exactly one Dataset.
Some important aspects:
- A Dataset Table has a module, so you can add a new Dataset Table to an existing Dataset (of another module).
- The SQL where clause is a HQL where Clause. The properties which can be used in the clause are the properties of the table (= entity) of the DatasetTable. See the next subsection for more information.
- Include all columns: if flagged all columns are exported, except the ones excluded in the DataSet Column, if not flagged then the DatasetColumn definition is used.
- Exclude Audit Info: only relevant if include all columns is flagged, in that case this field can be used to exclude audit info.
- IsBusinessObject: if flagged then also the 'child-records' of the table are exported, for example if the Dataset Table is defined for the C_Order table and this field is flagged then also the related C_OrderLines are exported. See here for more details on how business object structures are defined.
DatasetTable where clause
To select specific objects from the table to be included in the data set it is possible to define a where clause in the DatasetTable. The SQL where clause is a HQL where Clause. The properties which can be used in the clause are the properties of the table (= entity) of the DatasetTable. The entity and property names gives an overview of all properties by their entity. There are two standard parameters which can be used in the HQL clause:
- ClientID: denoting the current client of the user exporting the data
- moduleid: the id of the module for which data is exported
The syntax of using these parameters in the where-clause is the same as for named parameters in HQL in general.
Here are some examples of where clauses:
- client.id = :ClientID, export all objects from the current client
- client.id = '0', export only objects from the zero client
- processed = true and chargeAmount > 0, export only objects which have been processed and for which the charge amount > 0
- client.id in ('0', '1000000'), select all objects with a client 0 or 1000000
- name like '%Product%', select objects with a name which contains the string Product
The clause can contain inner-selects and other more advanced HQL features. However, order-by, group-by and having clauses are not supported, so the content of this field should just be the where-clause and nothing more.
The Dataset Columns defines the columns/properties which are exported for a certain business object (= Dataset Table). A Dataset Column always belongs to one Dataset Table, and a Dataset Table can have zero or more Dataset Columns. The Dataset Column concept can be used in two ways:
- check the 'Include all columns' in the Dataset Table and define exclusions in the Dataset Column
- do not check the 'Include All Columns' in the Dataset Table and define the exact Columns to export in the Dataset Column
The main fields:
- A Dataset Column has a module, so you can add a new Dataset Column to an existing Dataset Table (of another module).
- Excluded: if flagged then this column is defined not to be exported.
Usage of Datasets
The main purpose of Datasets is to define reference data for modules. This function is explained in more detail here. Datasets are exported:
- when clicking the 'Export Reference Data' button in the Dataset window
- when packaging a module: all data sets of a module are exported in this step
The reference data is inserted when a module is applied (build and installed). Or can be imported separately (see next section).
Importing Reference Data on Organization level
The module reference data can be imported into an organization using the Enterprise Module Management function. It is available in the menu here: General Setup > Enterprise > Enterprise module management > Enterprise module management.
Select the organization and the module from which to import the reference data into the organization. Then press ok. After a while the result page is displayed:
ERP 2.50:Developers Guide/Concepts/AD/Element and Synchronize Terminology | ERP 2.50:Developers Guide/Concepts/DB Fundamentals