Determines if multi-instance fields are expanded into separate rows.

Data Type

bool

Default Value

false

Remarks

The behavior of this option depends upon the service you are connecting to.

Refer to ExpandMultiValueLimit to understand the performance implications of enabling this connection property.

WQL

The following applies when ConnectionType is set to WQL.

WQL uses multi-value columns to reference multiple rows from another table, such one journal entry referencing multiple journal line items.

By default, the server displays these as an array of JSON dictionaries, each representing a single row from another table, which must be parsed to get individual values.

workdayID	orderNumber	lineItem
01234	1	[{"id": "123abc", "descriptor": "..."}, {"id": "234bcd", ...}, {"id": "345cde", ...}]
56789	2	[{"id": "456def", "descriptor": "..."}, {"id": "567fea", ...}]

When this connection property is set to True, the server returns one row for each comma-separated JSON dictionary in the array.

Each of these rows splits the JSON dictionary into an ID column and a descriptor column. It also generates a new field called workdayIDIndex that counts the number of rows expanded from one WQL row.

The workdayID and workdayIDIndex form a composite primary key, instead of the workdayID being the primary key on its own.

workdayID	workdayIDIndex	orderNumber	lineItem.id	lineItem.descriptor
01234	1	1	123abc	...
01234	2	1	234bcd	...
01234	3	1	345cde	...
56789	1	2	456def	...
56789	2	2	567fea	...

The server only expands rows that are included in the SELECT clause of the query.

If more than one multi-value column is selected, its values are combined with the other multi-value fields using a CROSS JOIN. This ensures that all combinations are included in the output so they can be used in WHERE or JOIN conditions.

Reporting

The following applies when ConnectionType is set to Reports.

The reporting API behaves similarly to the WQL API, with three main differences:

1. Two extra columns are added instead of just one. One is called RowIndex and it behaves the same as workdayIDIndex. The other is called RowNumber and it counts the number of original rows from the report. It is included because reports do not have a built-in primary key like WQL data sources do.
2. Reporting supports the ExpandIDTypes option. By default, single-value IDs are treated as multi-value fields and expanded in the same way. This is because even single-value references can have a separate value for each type of ID. If ExpandIDTypes is set to True, then each type of ID gets its own column and only multi-value references are expanded across rows.
3. The RaaS API supports two main types of reports: advanced reports and other types of reports. The next section explains the differences.

Advanced and Non-Advanced Reports

Most types of reports only include fields from their primary business object, with related business objects included using their IDs.

When reading a non-advanced report with this connection property set to True, the server expands ID values across multiple rows the same way it does when ConnectionType is set to WQL.

Advanced reports can include fields from both the primary business object and directly related business objects. Setting this connection property to True only expands one level of repeated values.

For example, the expenses reports object has the following structure. An advanced report includes fields from both the expense reports themselves as well as the related line items and approver:

• Expense Report
  • Is Approved (boolean)
  • Expense Report Line Items (multi-valued relationship)
    • Line Amount (number)
    • Tax Codes (multi-valued relationship)
  • Approver (single-valued relationship)
    • Name (text)
    • Related Workers (multi-value relationship)

With this connection property set to True, the server exposes this report as a view with these columns. Notice that Tax_Codes is left as an aggregate containing all ID types, while Approver.Related_Workers.ID is expanded and includes one type per row.

• RowNumber (int)
• RowIndex (int)
• Is_Approved (boolean)
• Expense_Report_Line_Items.Line_Amount (decimal)
• Expense_Report_Line_Items.Tax_Codes (string, XML aggregate containing all related codes)
• Approver.Name (string)
• Approver.Related_Workers.Descriptor (string)
• Approver.Related_Workers.ID.type (string)
• Approver.Related_Workers.ID (string)

SOAP

The following applies when ConnectionType is set to SOAP.

The SOAP API exposes a more complex data model with different types of repeated values:

1. Multi-value references that are similar to WQL and reporting multi-value references.
2. Basic multi-value fields like Address_Line_Data in the Workers_Address_Data table.
3. Complex multi-value structures that contain multiple fields.

This connection property only applies to the first two types because complex repeated structures are always exposed as child tables. The other two types behave similarly to WQL and reporting APIs with a few exceptions:

• Multi-value references only have an ID. No descriptor is included.
• If the table normally has a primary key, the server only adds an IDIndex column, which behaves like workdayIDIndex.
  • Otherwise, the server adds a row counter called RowID in addition to the IDIndex column.