D365fo join table as csv column in view/data entity

Below is some sample code for joining a table to a view as a calculated field. Useful when you have data represented as 1:n, but you need only one record returned for the parent datasource. My need for this came when enhancing the product search feature to include external item ids (custVendExternalItem). Using a normal join on the existing view created duplicate product RecIds on the parent data source which caused the full text index rebuild to fail on index violations. Considering the below scenario:

InventTable CustVendExternalItem

Item1 = Customer1Item

= Customer2Item

We want the view to return the following:

ItemId ExternalItemIds

Item1 Customer1Item, Customer2Item

Step1: Create an extension class for the view you are editing (or place the method directly on your custom view). Here is a sample of what worked for me. Ultimately, this will pass TSQL to the compiler. The SysComputedColumn methods are used to fetch the literal strings SQL will be using when the view runs for the morphX objects.

[ExtensionOf(viewStr(MCRProductSearchView))]

final class MCRProductSearchView_Extension

{

  public static str externalItemCSV()

  {

    return strfmt('( STUFF ( (Select \',\' + Z.ExternalItemId from CustVendExternalItem Z where Z.ModuleType in (4,6) and Z.ItemID = %1 and Z.DataAreaId = %2 FOR XML PATH(\'\'), TYPE).value(\'.\', \'VARCHAR(MAX)\'), 1,1,SPACE(0) ) ) ',

      SysComputedColumn::returnField(tableStr(MCRProductSearchView), identifierStr(InventTable), fieldStr(InventTable, ItemId)),

      SysComputedColumn::returnField(tableStr(MCRProductSearchView), identifierStr(InventTable), fieldStr(InventTable, DataAreaId)));

  }

}

Step2: Add the column to your view. Create a new String computed column on your view. If you are extending an existing view, add the following to the Method property: MCRProductSearchView_Extension::externalItemCSV  . If you are adding it to your own view, just place the method name in the "View Method" property. Note the property you use is different based on if this is an extension.

Step3: Build and Sync, done.