Understanding MultiValue Field Types

A multi-valued field is a special database field that holds more than one value.  This differs from a relational database where each field contains only one value.

Imagine that you are creating a Report Source from a customer master file. Each record on the file
holds information about a single customer. Here are the attribute definitions for the first seven
attributes on the file:

• Attribute 1: Customer Name, single-valued
• Attribute 2: Customer Street Address, single-valued
• Attribute 3: Customer City, single-valued
• Attribute 4: Customer State, single-valued
• Attribute 5: Customer ZIP Code, single-valued
• Attribute 6: Phone Type, multi-valued
• Attribute 7: Phone Number, multi-valued
• Attribute 8: Location IDs, multi-valued

Here is an example of how that record might look:

14901:
001: AIRPORT THEATRE
002: 1322 FAST DRIVE EAST
003: SEATTLE
004: WA
005: 98108
006: OFFICEýFAX
007: 206-763-8141ý206-763-8151
008: 15776ý15788ý16651

Notice in this example, attribute 6 has two values (OFFICE and FAX) delimited by the “ý” character, called a value mark.  Attribute 7 also has two values, which are related to the values on
attribute 6. 206-763-8141 is the company’s OFFICE number, and 206-763-8151 is the
company’s FAX number. Attribute 8 has three values, but they are unrelated to the values on
attributes 6 and 7.  

There are four available multi-value field type options:

• Single Value
• Controlling MultiValue
• Dependent MultiValue
• Independent MultiValue
  
  

Single Value
A single-valued field is one which does not include multi-valued data in any record. In the
example above, the single-valued fields are:

• Customer Name
• Customer Street Address
• Customer City
• Customer State
• Customer ZIP Code

If multi-valued data is found in a field that has been configured as a Single Value field, only the
first value will be displayed.

Controlling MultiValue
This is a multivalued field that holds values which are directly related to the values in one or more other
multivalued fields, which would be configured as Dependent MultiValues. For each value in a
Controlling MultiValue field, MITS Report will attempt to retrieve a value for each Dependent
MultiValue field. In the example above, the “Phone Type” field could be assigned as the
Controlling MultiValue field, and then the “Phone Number” field would be assigned as the
Dependent MultiValue field.

Dependent MultiValue
This is a multi-valued field that holds values which are directly related to the values in one or more other
multi-valued fields. The contents of a Dependent MultiValue field is directly related to the
contents of the Report Source’s Controlling MultiValue field. In the example above, the “Phone
Number” field could be assigned as a Dependent MultiValue field. The “Phone Type” would be
assigned as the Controlling MultiValue field for that Report Source.

Independent MultiValue
This is a multivalued field that holds values which do not directly relate to the values in other multivalued
fields. In the example above, the “Location IDs” field would be assigned as an Independent
MultiValue field. It would not be assigned as a Dependent MultiValue field because the Location
IDs do not relate to the values in attributes 6 and 7.

If this field were defined as a Dependent MultiValue field, MITS Report would relate the Location ID in the first segment of attribute 8 (15776) with the Phone Type and Phone Number in the first segment of attributes 6 and 7 (OFFICE and 206-763-8141, respectively), and it would also relate the Location ID in the
second segment of attribute 8 (15788) with the Phone Type and Phone Number in the second segment of attributes 6 and 7 (FAX and 206-763-8151, respectively.) Not only are these Location ID values unrelated to the Phone Type and Phone Number values they would be associated with, but the Location ID in the third segment of attribute 8 (16651) would be omitted from the Report Source entirely.

Independent MultiValue fields are not as flexible as Controlling/Dependent MultiValue fields when it
comes to sorting and filtering on the column. For this reason, if there is only one multi-valued field in
your Report Source, configure it as a Controlling MultiValue field rather than an Independent MultiValue
field...even though there are no Dependent MultiValue fields.