PCF: Dataset Relationships (Linking) in Model-Driven Apps

What do you do when it doesn’t seem to be possible to provide the needed data to a dataset PCF? We can bind a dataset PCF to a table, a view or to a subgrid. But maybe you need to consider a table linked to another one-to-many relationship. For instance think to a subgrid, where the data is related to another 1:N relationship, like in the example account -> orders -> orderproducts below.

It’s only an example, it could be completely different relationships

What if you are on an “Account” form, and want a subgrid with all orders and all products ordred? You can have a subgrid with all orders for an account, but how do you get the products?

You have three possibilities:

1. write your own XMLHttpRequests
2. declare the WebAPI feature in the PCF manifest, and use it to retrieve the related data
3. make use of the dataset sdk possibilities

If I have to choose between the option 1 and 2, I would stick with the WebAPI feature, because that will automatically work in offline mode too; at least for not that complicated requests. There might be some edge cases where I need to go with option 1. For instance when I the need to know the etag, the row version, in order to update only if nothing changed since the data was retrieved (at least for now I don’t now a supported way to achieve that with webAPI, but maybe that will change too).

But with XMLHttpRequest or with WebAPI I would start to have hard coded requests. That will make my PCF not so generic. Then I would start to add parameters to my PCF: for the relation name, for the field names to retrieve, for the columns I want to show. Even parameters for what should happen when I click somewhere on the record. But I get everything out-of-the box if I go with the options 3: the PCF dataset possibilities. There I can work with views: which means the data, the columns, the column width, the filter can be defined by the maker. Every maker can change the views and the columns, and this process provided options corresponding to the metadata, allowing only the tables (related or not), columns of a specific data type, and so on. And by adding the “property-set” inside a dataset, we’ll get the columns added to the dataset, even if they are not in the view: the platform will do that for us.

In this blog I would like to go through the possibilities we can use inside the dataset PCF. I mean: what can we do if the view is not exactly what we need. What can we do, and what we cannot do. In the next blog I would go through the use-cases I’ve tried out.

Linking

The Linking features are available only on model-driven apps. It has methods for working with linked tables. If you have a look to the sdk, you’ll find very few methods:

• addLinkedEntity : adds a new linked table relationship with the existing query primary table.
• getLinkedEntities: returns all the linked tables information.

That’s it. We cannot remove a specific linked table, we cannot clear all linked tables we’ve added. I guess it’s ok to add linked tables in the “init” method of the index.ts. But of course we can add linked tables also in the React mounting methods or with an useEffect(()=>{}, []) which should execute only once.

To understand the possibilities, it’s even more important to notice that we can add a linked table only for the primary table. We cannot go further than that. For instance we cannot build a fetchXML like the following: the highlighted line would be out of scope.

<fetch>
  <entity name="account" >
    <link-entity name="diana_order" from="diana_accountid" to="accountid" >
      <link-entity name="diana_orderproduct" from="diana_orderid" to="diana_orderid" />
    </link-entity>
  </entity>
</fetch>

Let’s consider we have a dataset with orders and want to add a linked table to orderproducts:

  dataset.linking.addLinkedEntity({
        name: "diana_orderproduct",
        from: "diana_orderid", 
        to: "diana_orderid", 
        linkType: "outer", 
        alias: "childRelationship"
      }
    );           

Since I’m linking using a 1:N relationship (one-to-many), I use the linkType: “outer”, because I want to get the orders also when there are no products.
Having a look in the network protocol, we’ll see that the highlighted lines were added to the fetchXml.

<fetch version="1.0" mapping="logical" returntotalrecordcount="true" page="1" count="10" no-lock="false">
    <entity name="diana_order">
        <attribute name="statecode"/>
        <attribute name="diana_name"/>
        <attribute name="createdon"/>
        <order attribute="diana_name" descending="false"/>
        <filter type="and">
            <condition attribute="statecode" operator="eq" value="0"/>
        </filter>
        <attribute name="diana_productid"/>
        <attribute name="diana_orderid"/>
        <attribute name="diana_count"/>
        <attribute name="diana_orderproductid"/>
        <link-entity name="diana_orderproduct" from="diana_orderid" to="diana_orderid" link-type="outer" alias="childRelationship">          
        </link-entity>
    </entity>
</fetch>

Important: By adding this kind relation to my query, the fetchXml will return one line for each combination of order-orderproduct, but inside dataset.records I get only one record per order. They are grouped for the main dataset. For instance if I have 1 order with 4 products: the fetchXml will return 4 records, but the dataset.records will have only 1 record. It makes sense to make this king of relations only in the other direction: fot many-to-one relatioships (lookups).

Here an example for the result of the fetchXml above, seen in the network protocol (I have 10 records):

But inside my PCF, the dataset has only 5 reocrds; one record for each id in the main dataset.

Another restriction is that we cannot directly interact with the linked table. At least not by addLinkedEntity. So let’s see how we can add columns to the added table, and how we can filter on columns from the linked table.

Filtering

The Filtering provides methods for filtering in a dataset. The filters will be added on top of the filters defined in the view. This functions work for both model-driven and canvas apps, and gives us methods to get, set and clear the filter. When we clear the filter, we’ll clear only the filter defined using code, not the filter defined in the view.

To set a filter, we need to call dataset.filtering.setFilter(expression), where expression needs to contain the conditions, filterOperator and filters. For example, let’s filter a dataset pointing to the Orders, to show only the orders for one account (only related orders):

dataset.filtering.setFilter({ 
     filterOperator: 0, 
     conditions: [
            {
            attributeName: "diana_accountid", 
             conditionOperator: 0, //equal
             value : accountId 
              }
       ],
       filters: []
});

Of course you can make combinations of “and” and “or” conditions. For example if you would like to filter the orders where date1 or date2 are inside an interval (between start, end):

 dataset.filtering.setFilter({
      filterOperator: 0, //and
      filters: [{
        filterOperator : 1, //or  
        conditions: [           
          {attributeName: "date1", conditionOperator: 4, value:start}, //27=OnOrAfter , 4=greaterequal
          {attributeName: "date2", conditionOperator: 4, value:start} //26 = OnOrBefore
        ]
        }, 
        {  
        filterOperator : 1, //or  
        conditions: [           
          {attributeName: "date1", conditionOperator: 5, value:end}, //27=OnOrAfter , 5=lessequal
          {attributeName: "date2", conditionOperator: 5, value:end} //26 = OnOrBefore
        ]  
      }], 
      conditions: []
    });

Filter on a set of ids

Another interesting example is filtering the records on a set of ids. That will allow us later to filter a dataset based on parent ids from another dataset.

Let’s cosider that we have a dataset with Orders and we have a second dataset will all OrderProducts. We want to filter the OrderProducts for the orders in the first dataset:

orderProductsDataset.filtering.setFilter({
     filterOperator: 0, 
     conditions: [
         {
          attributeName: "diana_orderid", 
          conditionOperator: 8, //in
          value : ordersDataset.sortedRecordIds             
          }
      ],
      filters: []
});                

So we simply use the operator “in”, and filter on the array “sortedRecordIds”. In the network protokol we’ll see the generated fetch:

<fetch version="1.0" mapping="logical" ... no-lock="false">
    <entity name="diana_orderproduct">
        <attribute name="statecode"/>
        <attribute name="diana_name"/>
        <attribute name="createdon"/>
        <order attribute="diana_name" descending="false"/>
        <filter type="and">
            <condition attribute="statecode" operator="eq" value="0"/>
        </filter>
        <attribute name="diana_productid"/>
        <attribute name="diana_orderid"/>
        <attribute name="diana_count"/>
        <attribute name="diana_orderproductid"/>
        <filter type="and">
            <condition attribute="diana_orderid" operator="in">
                <value>c5b7dfd9-3d34-ec11-b6e6-6045bd87c102</value>
                <value>b9eb2c19-3f34-ec11-b6e6-6045bd87c102</value>
                <value>7ebf6e46-b5d6-ec11-a7b5-000d3abb98ab</value>
                <value>6726521b-dcfa-eb11-94ef-000d3a2bd78c</value>
                <value>442b818c-880a-ec11-b6e6-000d3a4a8f80</value>
            </condition>
        </filter>
    </entity>
</fetch>

The first highlighted filter is the one from my view associated with the dataset. The second highlighted filter is the one generated using the “in” operator in the code.

Filter on related table

We cannot directly work with the related table, but we can still define a filter for a linked table. For that we need to work with the alias which we define for the linked table.

We want all OrderProducts for all Orders for an account. Let’s consider a dataset for OrderProducts (red). We’ll link with the table Orders (blue) and will filter on an accountid (green).

dataset.linking.addLinkedEntity({
     name: "diana_order", 
     from: "diana_orderid", 
     to: "diana_orderid", 
     linkType: "inner", 
     alias: "ParentRelation"
});           
dataset.filtering.setFilter({
     filterOperator: 0, 
     conditions: [
          {
           attributeName: "diana_accountid", 
           conditionOperator: 0, //equal
           value : accountId ,
           entityAliasName : "ParentRelation"
           }
     ],
     filters: []
});                      

The important part is to specify the entityAliasName for the condition inside the setFilter. That will generate this fetchXml

<fetch version="1.0" mapping="logical" returntotalrecordcount="true" page="1" count="10" no-lock="false">
    <entity name="diana_orderproduct">
        <attribute name="statecode"/>
        <attribute name="diana_name"/>
        <attribute name="createdon"/>
        <order attribute="diana_name" descending="false"/>
        <filter type="and">
            <condition attribute="statecode" operator="eq" value="0"/>
        </filter>
        <attribute name="diana_productid"/>
        <attribute name="diana_orderid"/>
        <attribute name="diana_count"/>
        <attribute name="diana_orderproductid"/>
        <link-entity name="diana_orderproduct" from="diana_orderid" to="diana_orderid" link-type="outer" alias="childRelationship">
            <attribute name="diana_name"/>
            <attribute name="diana_productid"/>
        </link-entity>
    </entity>
</fetch>

AddColumn

The dataset allows us also to add columns to the dataset: addColumn. For the cases where the columns are inside the view, we don’t need that, because inside the manifest we can declare “property-set”s. The property-set adds the column to the dataset, even if it’s not in the view.

<data-set name="dataset" ...> 
   <property-set name="lookupId" display-name-key="Lookup to parent" description-key="Lookup To parent" of-type="Lookup.Simple" usage="bound" required="true" />      
</data-set>

Add columns for related table

It starts to get interesting when we can add columns corresponding to the linked table. The definition of addColumn in the sdk is not accurate, but the typescript definition helped me to make it work:

(dataset as any).addColumn("diana_name", "childRelationship");
(dataset as any).addColumn("diana_productid", "childRelationship");

In the example above, I’ve added the columns diana_name and diana_productid from the table orderproduct. I had to use the alias defined in the addLinkedEntity: “childRelationship”.

By simply using dataset.addColumn, I’ve got a typescript error. Honestly I didn’t understood why typescript had a problem with calling addColumn, but I’ve ignored that by using:
(dataset as any).addColumn(…)

And here is the fetchXml generated, containing added columns

<fetch version="1.0" mapping="logical" returntotalrecordcount="true" page="1" count="10" no-lock="false">
    <entity name="diana_orderproduct">
        <attribute name="statecode"/>
        <attribute name="diana_name"/>
        <attribute name="createdon"/>
        <order attribute="diana_name" descending="false"/>
        <filter type="and">
            <condition attribute="statecode" operator="eq" value="0"/>
        </filter>
        <attribute name="diana_productid"/>
        <attribute name="diana_orderid"/>
        <attribute name="diana_count"/>
        <attribute name="diana_orderproductid"/>
        <link-entity name="diana_orderproduct" from="diana_orderid" to="diana_orderid" link-type="outer" alias="childRelationship">
            <attribute name="diana_name"/>
            <attribute name="diana_productid"/>
        </link-entity>
    </entity>
</fetch>

The result in network protocol is looking like this:

And inside the PCF I can get the attribute by using the “<entityAlias>.<columnName>” notation (but only for one of the children).

It works exactly the same if you link through a many-to-one relationship.

Related many-to-many relations: doesn’t work

[Edit] In this example I go through linking to a many-to-many relationship. But even if the generated fetchXml returns all the associated records, inside th dataset the results are grouped per dataset-id. That means that we won’t get all the associated combinations, but only the “distinct” ids from the dataset-view.

In the following example we have 2 datasets: the “dataset” is pointing to related orders to an account. The second dataset is pointing to all users, and we want to filter only on the users associated to the orders (through a many-to-many relationship: Order-User)

For all associated users (to the orders) we need to link the Users to the N:M (many-to-many) table (blue), filter on orderid from the order dataset (green) and add the column from order-user (that way we’ll be able to see which user is associated to which order).

//linking to n:m relationship
userDataset.linking.addLinkedEntity({
      name: "diana_diana_order_systemuser",      
      from: "systemuserid",
      to: "systemuserid",
      alias: "associatedRelationship", 
      linkType: "inner"
    });

//adding columns from n:m relationship
(userDataset as any).addColumn("diana_orderid", "associatedRelationship");

//filter on orderids from the main dataset
userDataset.filtering.setFilter({
      filterOperator: 0,
      conditions: [
        {
          attributeName: "diana_orderid",
          conditionOperator: 8, //in
          value: dataset.sortedRecordIds, 
          entityAliasName: "associatedRelationship"
        }
      ],
      filters: []
    });

The generated fetchXml has all the needed elements:

<fetch ....>
    <entity name="systemuser">
        <attribute name="fullname"/>
        <attribute name="systemuserid"/>
        ....
        <link-entity name="diana_diana_order_systemuser" from="systemuserid" to="systemuserid" link-type="inner" alias="associatedRelationship">
            <attribute name="diana_orderid"/>
        </link-entity>
        <filter type="and">
            <condition entityname="associatedRelationship" attribute="diana_orderid" operator="in">
                <value>c5b7dfd9-3d34-ec11-b6e6-6045bd87c102</value>
                <value>b9eb2c19-3f34-ec11-b6e6-6045bd87c102</value>
                <value>7ebf6e46-b5d6-ec11-a7b5-000d3abb98ab</value>
                <value>6726521b-dcfa-eb11-94ef-000d3a2bd78c</value>
                <value>442b818c-880a-ec11-b6e6-000d3a4a8f80</value>
            </condition>
        </filter>
    </entity>
</fetch>

BUT unfortunately if we try to read the dataset records, we will get only one record per userid, while the other positions returned by the fetchXml will be ignored. We are able to get all the users associated with orders corresponding to the account, but we are not able to say which users were associated to which order.

Conclusion

Now we know how to use the sdk to work with related tables: linking & adding filter and columns on linked tables. It works well to link and filter on many-to-one relationships, but it doesn’t work on one-to-many or many-to-many ones. For one-to-many relations we can workaround by using a second dataset, which we can filter on parentIds. For many-to-many relationships we’ll probably have to use WebAPI.
In the next blog I would like to go through some approaches for subgrid PCFs, and how to use the dataset linking/filtering sdk, instead of making webAPI requests.

Photo by BARBARA RIBEIRO