FetchXml DetailsList

Description

This PCF Control generates a FluentUI DetailsList for subgrids loaded via a custom FetchXml query and column layout. This extends the query capabilities beyond the standard Model-Driven App subgrid. You need to include an ID Placeholder which is replaced at runtime with the current record id. It is also possible to pass in an id from the current record to be replaced in the same fashion.

---

This solution was created to meet some recent challenges. Specifically, I have fairly complex data models that vary based on some data points. I am able to create multiple subgrids and switch between them via simple form JavaScript. Another challenge was when Microsoft removed the Contract entity making it no longer possible to view in the modern UI. So until we are able to migrate all of the data (and the super complex data models) to a new entity, we need to be able to navigate to Contract records. This solution allows us to render links to Contracts using the classic web interface.

I had searched in vain for a similar FetchXml driven subgrid control so this seemed like a good enough reason to roll up the sleeves and try my hand at a PCF control. It's far from perfect and uses some hacks, but it does solve some real issues for us in the meantime.

Features

• Dynamic queries can be more complex than model driven apps views allow, for example with many more linked entities. You can even include links to entities that are no longer available in the new user interface (i.e., Contract).
• Uses FluentUI DetailsList with a familiar look and feel - similar to model-driven read-only subgrid, supporting basic sorting and resizing of columns.
• Double clicking a row navigates to the base record and supports navigation to linked entities.
• Customization options for each column include date formatting, toggleable entity linking, absolute URLs, relative URLs, Combined Fields, and so on.
• Debug mode shows all data returned from FetchXml query for building the column layout.
• Uses Placeholder to filter by a record id. This defaults to the current record. But this can be overridden with another lookup on the current form.
• Quick rendering, even for larger datasets.

---

Quick Start using Pre-Built Solution

A Managed or Unmanaged Solution is available to download and install in your development environment.

1. After installing, you add the control to your form via the legacy or modern designer (I use legacy due to field length issue described later). Simply add any text field and bind this control to it.

2. Be sure to hide the label.

3. Go to the Controls tab and pick the FetchXml DetailsList.

4. Set the radio buttons so the control is visible, and set the Input Parameters.

Set Up Notes

Both the new and legacy designers will likely not allow you to paste in text long enough for more elaborate FetchXml queries and Column Layouts, so you have to use the legacy designer and a workaround to extend the field length.
Essentially you use the legacy designer and hack the input box via F11 dev tools to set the maxlength to something like 9999 instead of the default 2000 if your text doesn't fit.

---

Input Parameters (Properties)

The grid has input parameters which must be set.

• <code>FetchXml</code> is the full FetchXml with a placeholder for the Record Id in place. In this example we can show all contracts where the current Account is the Customer or the Billing Customer.

<fetch>
  <entity name='contract'>
    <attribute name='contractnumber' />
    <attribute name='contractid' />
    <attribute name='title' />
    <attribute name='statuscode' />
    <attribute name='createdon' />
    <attribute name='activeon' />
    <attribute name='expireson' />
    <attribute name='duration' />
    <attribute name='modifiedby' />
    <attribute name='customerid' />
    <attribute name='billingcustomerid' />
    <attribute name='totalprice' />
    <attribute name='ownerid' />
    <attribute name='mcaogs_contractlink' />
    <filter type='or'>
      <condition attribute='customerid' operator='eq' value='[RECORDID]' />
      <condition attribute='billingcustomerid' operator='eq' value='[RECORDID]' />
    </filter>
    <link-entity name='systemuser' from='systemuserid' to='owninguser' alias='owninguser'>
      <attribute name='internalemailaddress' />
    </link-entity>
  </entity>
</fetch>

• <code>RecordIdPlaceholder</code> is the placeholder text. This will be replaced with the current record id.
  i.e. <code>[RECORDID]</code>

• The <code>Record Id</code> is read from the current record in a bit of a hack at the moment as it's not super easy to get this in the Power Apps framework. This can also be overridden with another lookup on the current form. Simply set the <code>OverriddenRecordIdFieldName</code> to a lookup field on the current form and this id will be used instead of the id of the current record.

• <code>ColumnLayoutJson</code> is a collection of columns used for the table layout. See details below.

• <code>ItemsPerPage</code> is defaults to 5000 as paging is currently not implemented. // [NOT SUPPORTED CURRENTLY] ItemsPerPage is how many items to show per page. For now this is set at 5000 since paging and sorting seem to be at odds with each other.

• <code>CustomButtonConfig</code> adds one or more custom buttons to the command bar. Accepts either a single button object or an array of button objects. Each button can open a Custom Page or call a web resource function. See <a href="#CustomButtons">Custom Buttons</a> below for complete documentation.

• <code>HideNewButton</code> hides the built-in <code>+ New</code> button when set to <code>On</code>.

• <code>HideRefreshButton</code> hides the built-in <code>Refresh</code> button when set to <code>On</code>.

• <code>HideExportButton</code> hides the built-in <code>Export</code> button when set to <code>On</code>. Note: The Export button is automatically hidden when the query returns no results, regardless of this setting.

ColumnLayoutJson

This is a list of IColumn from the FluentUI DetailsList. Simply include all of the required fields for each column your data grid. The options <code>data</code> object can be helpful for extra customization.

| Field Name | Required | Type | Description | | --- | --- | -- | -- | | key | Yes | String | Unique key for data item | | fieldName | Yes | String | Column Label | | name | Yes | String | Field name matched from the returned Xrm Data | | minWidth | Yes | Number | Minimum field width (ie. 50) | | data | No | Object| Data Object with special stuff. See definition below. |

data Object

| Field Name | Required | Type | Description | | --- | --- | -- | -- | |dateFormat | No | String | You can force a date into a particular format by specifying this. This uses date-fns format strings i.e. <code>yyyy-MM-dd</code> | |entityLinking | No | Boolean | Set to <code>False</code> to prevent navigation to linked entities. Otherwise links are enabled. | |url | No | String | Absolute URL. Or can be relative from the <code>[BASE_ENVIRONMENT_URL]</code> path. You can include the current record id by using the <code>[ID]</code> placeholder.|

ColumnLayoutJson Example:

[
  {
    "key": "contractnumber",
    "fieldName": "contractnumber",
    "name": "Contract #",
    "minWidth": 60,
    "maxWidth": 100
  },
  {
    "key": "contractid",
    "fieldName": "contractid",
    "name": "Contract Link",
    "minWidth": 60,
    "maxWidth": 70,
    "data": {
      "url": "[BASE_ENVIRONMENT_URL]/main.aspx?etc=1010&pagetype=entityrecord&id=[ID]",
      "urlLinkText": "Contract Link"
    }
  },
  {
    "key": "title",
    "fieldName": "title",
    "name": "Contract Title",
    "minWidth": 100,
    "maxWidth": 170
  },
  {
    "key": "statuscode",
    "fieldName": "statuscode",
    "name": "Contract Status",
    "minWidth": 50,
    "maxWidth": 70
  },
  {
    "key": "createdon",
    "fieldName": "createdon",
    "name": "Created On",
    "minWidth": 50,
    "mmaxWidth": 60,
    "data": {
      "dateFormat": "yyyy-MM-dd"
    }
  },
  {
    "key": "activeon",
    "fieldName": "activeon",
    "name": "Contract Start Date",
    "minWidth": 50,
    "mmaxWidth": 60,
    "data": {
      "dateFormat": "yyyy-MM-dd"
    }
  },
  {
    "key": "expireson",
    "fieldName": "expireson",
    "name": "Contract End Date",
    "minWidth": 50,
    "mmaxWidth": 60,
    "data": {
      "dateFormat": "yyyy-MM-dd"
    }
  },
  {
    "key": "duration",
    "fieldName": "duration",
    "name": "Duration (Days)",
    "minWidth": 30,
    "data": {
      "type": "number"
    }
  },
  {
    "key": "_modifiedby_value",
    "fieldName": "_modifiedby_value",
    "name": "Modified By",
    "minWidth": 100,
    "mmaxWidth": 120,
    "data": {
      "entityLinking": true
    }
  },
  {
    "key": "owninguser.internalemailaddress",
    "fieldName": "owninguser.internalemailaddress",
    "name": "Owning U