This main steps for defining a dataset are:

  1. Define a temp-table for each table you want to use in the dataset.
  2. Define the dataset by specifying the temp-tables and their relationships (if any).

Define the temp-tables

To define a temp-table you use the DEFINE TEMP-TABLE statement. When defining a temp-table manually, you specify each field and index in the temp-table definition. You can define each field as a specific data-type, as you would in a DEFINE VARIABLE statement. To define a temp-table with specific fields and indexes, use this syntax:

DEFINE TEMP-TABLE table-name [ NO-UNDO ] BEFORE-TABLE before-table-name     
     { FIELD field-name  AS data-type }     
     { INDEX index-name [ IS [ UNIQUE ] [ PRIMARY ] ] { index-field } }.

The before table is used when a dataset is updated so that the original and updated data can be stored in the dataset before it is committed to the database.

Here is an example of the DEFINE TEMP-TABLE statement:

/* ttOrder.i */

DEFINE TEMP-TABLE ttOrder NO-UNDO BEFORE-TABLE bttOrder 
  FIELD OrderNum AS INTEGER
  FIELD OrderDate AS DATE
  FIELD ShipDate AS DATE
  FIELD PromiseDate AS DATE
  FIELD OrderTotal AS DECIMAL
  INDEX OrderNum IS UNIQUE PRIMARY OrderNum.

Define the dataset

After you define the temp-tables, you use the DEFINE DATASET statement to define the dataset that is composed of those temp-tables. Similar to defining temp-tables in their own include files, you define a dataset in its own include file. The DEFINE DATASET statement is a complex statement with multiple components. You use it to:
  • Name the dataset and specify the temp-tables that comprise the dataset.
  • Define any data-relations between those temp-tables.

To define a dataset, you use the DEFINE DATASET statement. Here is the simplified syntax:

DEFINE DATASET dataset-name FOR temp-table-name [,temp-table-name ]...
     [ DATA-RELATION [data-relation-name] FOR parent-temp-table-name, child-temp-table-name  
     RELATION-FIELDS (parent-field1, child-field1 [, parent-fieldn, child-fieldn ]...) ]
dataset-name
Specifies the name of the dataset.
temp-table-name
Specifies the name of the temp-table(s) in the dataset.
data-relation-name
Specifies a data-relation object. See ProDataSet relations for more detail.
parent-temp-table-name, child-temp-table-name
Identifies the parent and child temp-tables for the data relation.
parent-fieldn, child-fieldn
Define the relationship between fields in the temp-tables. For the most efficient joins, the RELATION-FIELDS fields should be indexed.

The following example defines a dataset called dsOrderOrderLine. It is comprised of two temp-tables called ttOrder and ttOrderLine. It states that the relationship between the two tables is based upon the ordernum fields. In this example the fields in the two tables have the same name, but they don't need to be. You can also specify relation-fields that have different names.

/* dsOrderOrderLine.i */
 
{include/ttOrder.i} 
{include/ttOrderLine.i} 

DEFINE DATASET dsOrderOrderLine FOR ttOrder, ttOrderline 
  DATA-RELATION drOrderOrderLine FOR ttOrder, ttOrderline 
  RELATION-FIELDS (ordernum,ordernum).

You can define more than one data-relation for a dataset and include more than two temp-tables. The following is an example:

/* dsOrderOrderLineItem.i */ 
{include/ttOrder.i} 
{include/ttOrderLine.i} 
{include/ttItem.i} 

DEFINE DATASET dsOrderOrderLineItem FOR ttOrder, ttOrderLine, ttItem 
  DATA-RELATION drOrderOrderLine FOR ttOrder, ttOrderLine 
    RELATION-FIELDS (Ordernum, Ordernum) 
  DATA-RELATION drOrderLineItem FOR ttOrderline, ttItem 
    RELATION-FIELDS (Itemnum, Itemnum).

You can learn more about datasets in the Datasets Guided Journey or Use ProDataSets guide.