Detail tables
A detail table is a field in the Data Model that contains a record set instead of a single value. Detail tables contain transactional data. They are created when an Extract step is added within a Repeat step; see Extracting transactional data.
In the most basic of transactional communications, a single detail table is sufficient. However, it is possible to create multiple detail tables, as well as nested tables. Detail tables and nested tables are displayed as separate levels in the Data Model (see The Data Model).
Renaming a detail table
Renaming detail tables is especially useful when there are more detail tables in one record, or when a detail table contains another detail table. For this detail table, ‘products’ would be a better name.
- On the Data Model pane, click one of the fields in the detail table.
- On the Step Properties pane, under Extraction Definition, in the Data Table field, you can find the name of the detail table: record.detail by default. Change the detail part in that name into something else.A detail table’s name should always begin with ‘record.’.
- Click somewhere else on the Step Properties pane to update the Data Model. You will see the new name appear.
Creating multiple detail tables
Multiple detail tables are useful when more than one type of transactional data is present in the source data, for example purchases (items with a set price, quantity, item number) and services (with a price, frequency, contract end date, etc).
To create more than one detail table, simply extract transactional data in different Repeat steps (see Extracting transactional data).
The best way to do this is to add an empty detail table (right-click the Data Model, select Add a table and give the detail table a name) and drop the data on the name of that detail table.
Else the extracted fields will all be added to one new detail table with a default name at first, and you will have to rename the detail table created in each Extract step to pull the detail tables apart (see Renaming a detail table).
Nested detail tables
Nested detail tables are used to extract transactional data that are relative to other data. They are created just like multiple detail tables, with two differences:
- For the tables to be actually nested, the Repeat step and its Extract step that extract the nested transactional data must be located within the Repeat step that extracts data to a detail table.
- In their name, the dot notation (record.services) must contain one extra level (record.services.charges).
Using
nested detail tables in the Designer module requires scripting, as described in this How-to: Cloning your way through nested tables.
Example
An XML source file lists the services of a multi-service provider: Internet, Cable, Home Phone, Mobile. Each service in turn lists a number of "charges", being service prices and rebates, and a number of "details" such as movie rentals or long distance calls.
The services can be extracted to a detail table called record.services.
The "charges" and "details" can be extracted to two nested detail tables.
The nested tables can be called record.services.charges and record.services.details.
Now one "charges" table and one "details" table are created for each row in the "services" table.