Create a data model with multiple records

Use the New Record Wizard to diagram multiple records with their relationships in the Data Model. The records represents the data to be streamed to the report.

It is recommended that your Data Model diagram consist of a single record. When you have multiple records, you end up with two SQL statements that must run in parallel; the value of a field in the first record causes the SQL of the second record to execute, and this repeats for each record streamed across. This can is detrimental to performance.

Despite the recommendation, you may have situations where you need to diagram the relationship between disparate records. For example, you may have a list of customers and a list of prospects; two records that can be related, but that do not necessarily make a good single record. In such situations, you can create a Data Model with multiple records.

Figure: Multiple records in a Data Model diagram

Screen shot of diagram of two records, orders and lineitems, with one being Master and the relationship shown.

It is assumed that you have created an initial Data Model diagram (.4rdj), following the steps in Create a data model with a single record. The initial record is identified as the Master record.

Add a second business record

There are several ways you can add a second record to your existing Data Model diagram (.4rdj). After opening your diagram, you can:
  • Click the DataControl icon in the Genero Studio tool bar. This opens the New Report Wizard.
  • From the DB Schemas view, select a table (or a set of columns from a table) and drag them onto the Data Model diagram.

Add the relation between records

When you define a relation between two tables, you typically are working with a pair of records that have a one-to-many relationship. For example, one account can have many orders. One company can have many employees. You must first identify which record is the 'one', and which record is the 'many'. You must also identify which field from each of the records will be used to join these data sets.
  1. In the 'many' record, right-click on the field that will be used to fine the unique entry in the 'one' record.
  2. Select Add Relation To.
  3. Click the associated field name in the 'one' record.

The relation is defined, and the primaryFields and foreignFields properties are set.

In the diagram, the relation arrow should point to the Master record.

Tip: You can attempt to auto-populate the primaryFields and foreignFields properties:
  1. Right-click on the record header of the first record.
  2. Select Add Relation To.
  3. Select a second record.
Based on the relationships defined in the database meta-schema file, The tool attempts to determine the primaryFields and foreignFields properties for you.

Change fields in a relation

To change the value of the foreignFields or primaryFields properties in a relation:
  1. Click the ellipse icon.
  2. Select the fields from the field list.