Subgrid Dataset PCF: Programmatically Extend Relations

The last blog was about how to programmatically extend a dataset PCF with relations, and how to filter it. The main purpose was to avoid hardcoding WebApi requests. In this blog the story goes on with examples: let’s talk about how to use the dataset “linking sdk” to make PCFs for subgrids containing extended relationships (for table not directly linked with the hosting form).

The starting point is the demo made in my GitHub Repository DatasetExample. This is the starting branch; for each use-case there is another branch. That way you can compare the changes going from one branch to another. If you you prefer videos, you can find here how to write the code for the starting point:

and you can watch the important parts of the blog in the video format:

#1. Single-Dataset with Linking

Let’s consider the following use case:

So we are on an account form, and would like to have all order-products from all orders for this account.

We start with all OrderProducts, and programmatically link them with the Orders, and then need to filter the Orders, to get only the ones related with this account.

The manifest

To have the PCF generic, I’ve extended the manifest:

Notice the “property-set” added to the dataset: lookupId. This will serve me in two ways:

• it will allow the maker to choose the lookup to the parent table. That is a flexible way to decide which relationship is needed. For the account-order-orderproduct use-case that would be “diana_orderid”
• it will automatically add that column in the dataset, even if it’s not inside the view.

I’ve also added the name of the parent table: parentEntityName (in my case “diana_order”). As an optimization for later, this could be retrieved using the metadata.

The last parameter is “parentLookupName”. That’s for the relation order-account; this way I’ll know on which lookup should I filter the records.

Index.ts – init method

In the “init” method of my PCF, I’ll add the relation (linking). Of course this could have been done only in my react component, if I take care to do it only once.

const parentEntityName = context.parameters.parentEntityName.raw;
const parentId = context.parameters.dataset.columns.find((column) => column.alias==="lookupId");
if(parentEntityName!=null && parentId!=null){
   context.parameters.dataset.linking.addLinkedEntity({
      name: parentEntityName,
      from : `${parentEntityName}id`,
      to:  parentId?.name,
      linkType: "inner",
      alias: "ParentRelation"
      })    
}

The react component

My react component, will get two more props

export interface IDatasetExampleComponentProps {
  dataset: DataSet;
  entityId ?: string;
  parentLookupName ?: string | null;
}

Inside my function component I’ll add a new “useEffect”. This will get triggered each time the entityId of the form will be changed. This is usefull only for the case where the form is in create-mode at first. As soon the form is saved we’ll have an entityId which won’t get changed later. After the filter is set, I need to call the dataset.refresh().

I don’t need to clear the filter, because I know that the entityId can be changed only once, and I set the filter only if it’s not null.

 React.useEffect(()=>{
    if(entityId!=null && parentLookupName!=null){
      dataset.filtering.setFilter({
        filterOperator: 0,  
        conditions: [
          {
            attributeName: parentLookupName, // "diana_accountid",
            conditionOperator: 0, //equal
            value: entityId,
            entityAliasName: "ParentRelation"
          }
        ],
        filters:[]
      });
      dataset.refresh();
    }
  }, [entityId]);

As an optimization, I’ll calculate the columns and the items for my FluentUI DetailsList, only if the filter is set and the dataset is not in loading mode:

 React.useEffect(() => {
    if((entityId==null || dataset.filtering.getFilter() == null || dataset.loading===true){
      return;
    }
   ...
          
  }, [dataset]);  

Index.ts updateView

Inside the index.ts, pass the new props:

 public updateView(context: ComponentFramework.Context): React.ReactElement {
          const props: IDatasetExampleComponentProps = { 
            dataset: context.parameters.dataset,
            entityId: (context as any).page.entityId,
            parentLookupName: context.parameters.parentLookupName?.raw
        };
        return React.createElement(
            DatasetExampleComponent, props
        );
    }

Now we have all products from all orders in one list:

The complete code can be found in the branch “2_simpledataset_extended_relationship” of the DatasetExample GitHub repository:

#2. Multiple-Datasets with Linking

We’ll try to get the same data as in the example 1: all products ordered by an account, but this time we’ll use 2 datasets. The first dataset will contain related orders to account (the filter is set using the subgrid customizing). The second will contain all orderproducts; we’ll need to filter them corresponding to the dataset for orders.

The customizing

The first dataset will be filtered directly by the subgrid, using related records, while the second dataset needs to be filtered programatically:

Now the customizing experience is metadata guided, and the maker can choose even the lookup to the parent dataset (Orders in our case) from a dropdown with lookups

The manifest

The definition and implementation of the PCF is now more straint forward. I’ve started with the same version of the PCF just like in example #1: branch 1_SimpleDataset.

Now we only need to define two datasets. For the second dataset, we can choose the “lookupId” column, which will automatically add the column to the dataset even if it’s not in the view. The property-set will also allow us to know which column should be used for filtering the second dataset.

The react component

First of all we’ll rename the dataset prop of the react component into childDataset; because I render the products which are now in the second dataset.

I’ve use a react function component. Now I only need to add the filter on the childDataset, every time the dataset changes.

So we need a React.useEffect with a dependency on the first dataset. Each time the dataset changes, we need to filter using the childDataset.filtering.setFilter, with the operator “in” containing all ids from the first dataset:

  React.useEffect(() => {
    if(dataset.loading===true){
      return;
    }
    const parentId = childDataset.columns.find((col)=> col.alias==="lookupId");        
    if(parentId!=null){       
        childDataset.filtering.clearFilter();    
        childDataset.filtering.setFilter({
            filterOperator: 0, 
            conditions: [
              {attributeName: parentId?.name, 
              conditionOperator: 8, //in
              value : dataset.sortedRecordIds             
            }
            ],
            filters: [          
            ]
          });                      
     childDataset.refresh();       
    }
  },[dataset]);

The name of the lookup to the parent can be found by using the property-set; in code we look for the column with the alias “lookupId”.

As an optimization we don’t calculate the items and columns of the grid, as long the childDataset is not filtered. So on the useEffect with dependency on the childDataset, we add this condition on the top:

   if(childDataset.filtering.getFilter() == null || childDataset.loading===true){
      return;
    }

Index.ts

The change in the index.ts is minimal: we only need to pass the two datasets to the component:

  const props: IDatasetExampleComponentProps = { 
            dataset: context.parameters.dataset,
            childDataset : context.parameters.childDataset
        };
        return React.createElement(
            DatasetExampleComponent, props
        );

That’s it. The result is exactly like in the example 1.

The complete code can be found in the branch “3_multipledatasets_extended_relationship” of the DatasetExample GitHub repository:

Which was is better?

Compared with the webAPI way of retriving data, we have one disadvantage: the data is loaded first without a filter. Only after we set the linking and the filter programmatically, we’ll get the dataset filtered. The advantage of this approach though, is a generic component, easy to customize. But, even more important, we also get the advantage of the interaction with the ribbon, search-box and the view-switcher.

The two ways of filtering showed in this blog have pretty much the same result. But there are small differences, which might make a difference if you plan to bet on one or another way.

Customizing

While in example #2 the maker experience is guided, based on the metadata, in the example #1 the customizer needs to define field names. It can be mispelled. Also it’s a little hard to explain what’s expected there, so the meaning of the properties can be misunderstood. In example #2 the purpose of the parameters is clean, and I can choose the field from a drop-down, respecting the metadata.

Paging

In example #2 the filter is applied directly on the loaded records from the first dataset. So if the first dataset needs paging, we are able to filter the second dataset based on the records for the first dataset.

In example #1 the paging applies directly on the dataset. If we want to group the data, we need to sort on the “LookupId”, otherwise the paging might mess up the groups.

Platform interaction

Probably the most important difference.

Platform interaction on example #1

In example #1 the search box, the view switcher and the ribbon-bar applies to OrderProducts.

I can search both on products and orders, by configuring the quick find. Here is the generated fetchXml if we search on “*ord”.

    
     ...
        
            
        
        
            
            
            
        
       ...
        
        
            
        
    

Regarding the ribbon interaction: the user can interact only with the ribbon commands for OrderProducts.

Platform interaction on example #2

In example #2, the search box, the view switcher and the ribbon-bar applies to orders (parent table).

• It won’t be possible to search on products using the standard search-box. I might need to make an own search box inside the PCF to take control on searching on products.
• The standard ribbon-buttons will be only for the “orders”

The generated fetchXml after using the search-box

    
        ...
        
            
        
        
            
        
        
            
                
            
        
    

But the fetchXml for products won’t be affected by the search box:

    
       ...
        
            
        
       ...
        
            
                6726521b-dcfa-eb11-94ef-000d3a2bd78c
                442b818c-880a-ec11-b6e6-000d3a4a8f80
            
        
    

Conclusion

It’s possible to use the dataset instead of hard-coding the WebAPI, except for many-to-many relations (as shown in my previous blog). The examples in this blog are only about extending the relations with one level. I suppose we can extend even on more levels, by using more datasets and by combining the two examples above. The approach chosen might have implications on interaction with the platform.

Stay tuned

While in the example #1 we cannot interact with the ribbon buttons for OrderProducts , the example #2 seems to allow us to interact only with the Orders. But actually we can break out of this limitations. That will be the subgect for the next blog.

Photo by Torsten Dederichs on Unsplash