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.

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.
Be sure to hide the label.
Go to the Controls tab and pick the FetchXml DetailsList.
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.

FetchXml 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>

RecordIdPlaceholder is the placeholder text. This will be replaced with the current record id.
i.e. [RECORDID]
The Record Id 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 OverriddenRecordIdFieldName to a lookup field on the current form and this id will be used instead of the id of the current record.
ColumnLayoutJson is a collection of columns used for the table layout. See details below.
ItemsPerPage 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.
DebugMode can be set to On or Off. When enabled, this will write extra details to console, break when entering the main control, and break on handled exceptions.

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 data 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. `yyyy-MM-dd`
entityLinking	No	Boolean	Set to `False` to prevent navigation to linked entities. Otherwise links are enabled.
url	No	String	Absolute URL. Or can be relative from the `[BASE_ENVIRONMENT_URL]` path. You can include the current record id by using the `[ID]` 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 User Email",
    "minWidth": 100,
    "mmaxWidth": 120
  },
  {
    "key": "mcaogs_contractlink",
    "fieldName": "mcaogs_contractlink",
    "name": "Contract Link",
    "minWidth": 100,
    "data": {
      "url": "[USE_VALUE]",
      "urlLinkText": "[USE_VALUE]"
    }
  }
]

Initial Configuration Tips

If you have DebugMode turned on you can see in the console log three important items: DynamicDetailsList fetchXml (with the RecordIdPlaceholder replaced), DynamicDetailsList columnLayout , and webAPI.retrieveMultipleRecords : this._allItems which shows the records returned.

Initial Setup for Building Solution

Ensure you have Node.js installed,
Clone this repository.
Navigate into the project directory in terminal.
```
cd FetchXmlDetailsList
```
Install the dependencies
```
npm install
```
Demo the subgrid with sample data and column layout in PCF Test Harness. Linking is disabled since this is not allowed in the tester.
```
npm start  
```

Build and Deploy

You can build and deploy to your currently configured DEVELOPMENT Environment using the CLI PAC PCF PUSH by running: buildAndDeploy.ps1. Note that the CLI requires connecting to your development org first. See the documentation for more details. You will need to ensure you have installed the Microsoft PowerApps CLI. You will need to do a pac auth create before running this script to get you connected to your DataVerse environment.

buildAndDeploy.ps1 will build the component, add it to a temporary solution (PowerAppsTools_YourOrg) , import to your DEV environment and Publish All. Prerequisite is to make sure you can connect to your DEV environment using the CLI tools.

buildAndDeploy.ps1

Build Managed and Unmanaged Solutions

To rebuild the managed and unmanaged solution in the solution folder, you need the msbuild command available in your path. You can go to a Visual Studio developer prompt if you have that available.

The first time you need to also do a restore:

msbuild /t:build /restore

To build Debug solutions

msbuild

To build Release solutions

  msbuild /p:configuration=Release

I have been unable to get the MSBUILD to make the ZIP files for some kind of strange errors. So lately I have been using the MSBUILD that comes with the dotnet framework. So you can try the script here:

C:\Projects\Web\FetchXmlDetailsList\solution\buildSolutions_Dotnet.ps1

You may have to change the path to match where your MSBuild.dll lives. For me it is currently here: C:\Program Files\dotnet\sdk\7.0.304\MSBuild.dll.

Notes

Response Details

The response to the FetchXml get multiple query should have details in it which we need for the rendering to work. Essentially the Xrm Web Api sets the headers and returns details we can work with. prefer: odata.include-annotations="*" Enable DebugMode and check the F11 console log. This will give you a good idea how to include columns for the ColumnLayoutJson.

Issues

Dynamics-Web-Api library initial set up issue

For ease of use, this control can be switched to use the dynamics-web-api library. You can reference the field name with the _Formatted suffix. But the default is to just use the out of the box xrm web api.

But there seems to be an issue with this dynamics-web-api 3rd party library. Initially it may give a strange crypto error:

ERROR in ./node_modules/dynamics-web-api/lib/utilities/Utility.js 2:14-31 Module not found: Error: Can't resolve 'crypto' in 'C:\Projects\Web\FetchXmlDetailsList\node_modules\dynamics-web-api\lib\utilities'

I am considering removing dynamics-web-api support as the out of the box Web Api works fine. For now you can fix it by editing the webpackConfig.js for pcf-scripts to tell it to ignore it.

../FetchXmlDetailsList/node_modules/pcf-scripts/webpackConfig.js#L61

Change this:

resolve: {
        // Tell webpack which extensions to try when it is looking for a file.
        extensions: ['.ts', '.tsx', '.js', '.jsx'],
},

to this:

resolve: {
        // Tell webpack which extensions to try when it is looking for a file.
        extensions: ['.ts', '.tsx', '.js', '.jsx'],
       fallback: { "crypto": false },
},

Errors

If you get runtime errors you may need to use a _Formatted field. For example, here it seems to be having a hard time with the date. Objects are not valid as a React child (found: Wed Dec 31 9000 00:00:00 GMT-0600 (Central Standard Time)). If you meant to render a collection of children, use an array instead. Another option if it's a date issue is to be sure to use a dateFormat in the column layout data object.

New Features

Combined Fields

If you have a need to "coalesce" multiple fields and group multiple fields into one field, you can try this feature. This is useful for data such as Connections where you may be joining multiple entity (table) types. It technically will also allow joining the data from multiple fields too if you nave a need for that.

Essentially you can set up a CombinedField in your column layout like the following. The data.joinValuesFormTheseFields is a list of all field names that are grouped and shown for that column. If there are values in more than one of the fields, then we show them all (delimited by semicolons -- for now anyhow). Of course, for mutually exclusive fields (like on the To side of Connections), you won't see more than one show up.

     {
        "key": "CombinedNameField",
        "fieldName": "CombinedNameField",
        "name": "Combined Names",
        "minWidth": 200,
        "data": {
          "joinValuesFromTheseFields": ["contact.fullname","systemuser.fullname"]
        }
      },
      {
        "key": "CombinedEmailField",
        "fieldName": "CombinedEmailField1",
        "name": "Combined Emails",
        "minWidth": 200,
        "data": {
          "joinValuesFromTheseFields": ["contact.emailaddress1","systemuser.internalemailaddress"]
        }
      },
      {
        "key": "CombinedModifiedOnField",
        "fieldName": "CombinedModifiedOnField",
        "name": "Combined ModifiedOn",
        "minWidth": 80,
        "data": {
          "joinValuesFromTheseFields": ["contact.modifiedon","systemuser.modifiedon"],
          "dateFormat": "yyyy-MM-dd hh:mm:ss"
        }
      },

The sample FetchXml starts on an Account record and shows any connected Contacts or SystemUsers.

<fetch top="50">
  <entity name="connection">
    <attribute name="record1id" />
    <attribute name="record2id" />
    <attribute name="record1objecttypecode" />
    <attribute name="record2objecttypecode" />
    <link-entity name="account" from="accountid" to="record1id" alias="account">
      <attribute name="emailaddress1" />
      <attribute name="name" />
      <attribute name="modifiedon" />
      <filter>
        <condition attribute="accountid" operator="eq" value="[RECORDID]" />
      </filter>
    </link-entity>
    <link-entity name="contact" from="contactid" to="record2id" link-type="outer" alias="contact">
      <attribute name="emailaddress1" />
      <attribute name="fullname" />
      <attribute name="modifiedon" />
    </link-entity>
    <link-entity name="systemuser" from="systemuserid" to="record2id" link-type="outer" alias="systemuser">
      <attribute name="internalemailaddress" />
      <attribute name="fullname" />
      <attribute name="modifiedon" />
    </link-entity>
  </entity>
</fetch>

Alternate Sample Dataset has two options

You can choose between a sample Contract dataset or Connections dataset by swapping out the lines in the \src\GetSampleData.ts.

// Use these two lines for Sample Contract dataset
//import * as sampleResponseData from './data/sample.Contracts.Response.webapi.json';
//import * as sampleResponseColumnLayout from './data/sample.Contracts.columnLayout.webapi.json';

// Use these two lines for Sample Connections dataset
import * as sampleResponseData from './data/sample.Connections.Response.webapi.json';
import * as sampleResponseColumnLayout from './data/sample.Connections.columnLayout.webapi.json';

export function GetSampleData() {
    // Use following for Sample Contract dataset
    // return { dataItems : sampleResponseData.value, columns : sampleResponseColumnLayout, primaryEntityName : 'account' };
    
    // Use following for Sample Connections dataset
    return { dataItems : sampleResponseData.value, columns : sampleResponseColumnLayout, primaryEntityName : 'connection' };

  }

TODOs:

The DetailsList is inside a FluentUI ScrollablePane to allow the subgrid to expand and scroll correctly, but it doesn't fit into it's parent container correctly in the vertical aspect. It just overlays the rest of the elements after it. So for now, add the subgrid on a separate tab by itself. This is very evident in the test harness. I am looking into how to fix this. There is a commented out alternate layout that keeps the subgrid inside it's section, but it doesn't grow when you have more than 4 rows. So using the overlay version for now.
Improve documentation.
Paging! Paging is not implemented yet. Page size is locked at 5000 for now.
Perhaps allow styling via input parameter. i.e. alternate row color enable/disable, etc.
When not using the Dynamics-Web-Api 3rd party library, don't include (require) it. This will make the final bundle.js smaller.
Export is very rudimentary. It would be much better if the header was the actual column name instead of column fieldName.
If you have fewer fields, the column widths are not right. But with enough fields, it seems to space them out fine.

donschia/FetchXmlDetailsList