Call FetchXml Inside Custom Pages Without Flows – The PCF Way

Recently I was working on a CustomPage and came across the need to be more flexible in fetching data from Dataverse.I needed to call complex fetchXmls and CustomAPIs. For this blog I’ll talk only about fetching data, but my solution works also for calling CustomAPIs.

For fetching data, the requitement is similar with the one shown by Scott Durow in his blog “Perform complex Dataverse FetchXml queries using Power Fx (from a Canvas App)“. He shows us how to use a flow (Power Automate) to execute a complex FetchXml, and then parse the data using the new (experimental) ParseJSON PowerFx function. His approach works for both Canvas Apps and Custom Pages.

But since Custom Pages have some extra features over Canvas Apps, I was looking for a more simple approach for Custom Pages. The feature I’m relying on: the PCFs inside Custom Pages are able to make webAPI calls. In this blog I’ll show two ways to get the data from a fetchXml using a (I call it) “technical PCF”: a PCF which doesn’t have any UI. The only job of this PCF is making a fetchXml request.

In a few words, my two ways are

  • Way 1:
    • use a PCF to retrieve data based on a fetchXml, and return the result as a stringified JSON (the result is similar with the flow result)
    • inside the Custom Page use the ParseJSON and define the schema of the returned collection
  • Way 2:
    • use a PCF to fetch the data, but the result is this time a dynamic output “object”
    • this allows us to also generate the schema of the result and let the Custom Page directly use the schema.

Let’s see how it works.

The use-case for this blog

The example I’ve took for this blog: get the count of activities for all accounts, grouped by industryCode. I’m using this fetchXml:

<fetch distinct='false' mapping='logical' aggregate='true' >
  <entity name='activitypointer' >
    <attribute name='activityid' alias='activity_count' aggregate='countcolumn' />
    <link-entity name='account' from='accountid' to='regardingobjectid' >
      <attribute name='industrycode' alias='industrycode' groupby='true' />     
    </link-entity>
  </entity>
</fetch>

We can try this out, using the great FetchXML Builder by Jonas Rapp:

Here is the result in the JSON-Web API Format

Way 1 – JSONParse based on a PCF with an output string property

This solution is based on JSONParse, similar with the “Flow” way. The only difference, is that I use a PCF for making the requests.

The PCF code

The code is pretty straing forward.

I’ve declared 3 properties in my manifest: fetchXml and entityName are actually input property while the “output” contains the string with the fetched records.

<property name="fetchXml" of-type="SingleLine.Text" usage="bound" required="true" />
<property name="entityName" of-type="SingleLine.Text" usage="bound" required="true" />
<property name="output" of-type="SingleLine.Text" usage="output" required="false" />

And of course I need to declare in the manifest that I’m using the webAPI

<feature-usage>      
   <uses-feature name="WebAPI" required="true" />    
</feature-usage>

Inside the index.ts all I have to do is to execute the fetch. We won’t render any UI (use React.Fragment).

public updateView(context: ComponentFramework.Context<IInputs>): React.ReactElement {    
    if(context.parameters.fetchXml.raw && context.parameters.entityName.raw 
         && (context.parameters.fetchXml.raw != this.fetchXml || context.parameters.entityName.raw != this.entityName)){
         this.fetchXml = context.parameters.fetchXml?.raw ?? "";
         this.entityName = context.parameters.entityName?.raw ?? "";
         fetchRecords(this.fetchXml, this.entityName, context).then((records) => {
             this.response = JSON.stringify(records);
             this.notifyOutputChanged();
         });
     }
     return React.createElement(React.Fragment);
}

Of course, we take care to avoid unnecessary requests, by checking in updateView if the fetchXml or the entityName were changed.The result will be saved as a string using “JSON.stringify” . This will be returned in getOutputs.

The “fetchRecords” function will make the fetch request using the webAPI feature of the code component, so it contains

context.webAPI.retrieveMultipleRecords(entityName, "?fetchXml=" + fetchXml)

The result would look like this

We have there also the “FormattedValues” which we will need to show inside CustomPage. But I don’t like to work with that complicated names inside my CustomPage, so I’ve decided to rename that attributes in <attribute>_formatted and <attribute>_logicalName.

 export const fetchRecords = async (fetchXml: string, entityName: string, context: ComponentFramework.Context<IInputs>) => {  
   //I can use metadata calls to add more data to the response; 
    //here for the optionset(choice) colors
    let colors = new Map();
    try {
      const metadata = await context.utils.getEntityMetadata("account", ["industrycode"]);
      colors = new Map((metadata.Attributes.get("industrycode")?.attributeDescriptor.OptionSet ?? []).map((option: any) => {
        return [option.Value.toString(), option.Color];
      }));
    } catch (e) {
      console.log(e);
    }  
    try{    
        const res = await context.webAPI.retrieveMultipleRecords(entityName, "?fetchXml=" + fetchXml);
        //parse the response, adding formatted and logicalNames
        const records = res.entities.map((entity: any) => {        
            const entityEntries = Object.entries(entity).map(([key, value]) => {            
                //extracting <attribute>_logicalName            
                const index = key.indexOf("@Microsoft.Dynamics.CRM.lookuplogicalname");
                if(index>0){
                    return [key.substring(0, index) + "_logicalName", value];                            
                }
                //extracting <attribute>_formatted
                const index1 = key.indexOf("@OData.Community.Display.V1.FormattedValue");
                if(index1>0){                    
                    return [key.substring(0, index1) + "_formatted", value];                            
                }
                return [key, value];
            });          
            //adding the industrycode_color
            if (entity.industrycode != null) {
                entityEntries.push(["industrycode_color", colors.get(entity.industrycode.toString())]);
              }
            return Object.fromEntries(entityEntries);
        });    
     return records;
    }
    catch(e){  //this will provide fallback data inside the designer
        if(e instanceof Error){
            if(e.name === "PCFNonImplementedError"){
                return [{"industrycode": 1, "industrycode_formatted": "Accounting", "activity_count": 10, "industrycode_color": "#ee0000"}];
            }
        }
        throw e;
    }
}

Since I’m at it, I can also call the metadata and get the colors customized for the industrycode column inside the Account table. Then I add the “industrycode_color” to the returned records. Ok, I’ve cheated, since the the name is hardcoded; a real generic fetchXml PCF would have to solve it more elegant.

The result returned by the PCF will look like this:

Notice the highlighted code lines 38-45, where I catch the Exception “PCFNonImplementedError”. That’s because the webAPI doesn’t work inside the Custom Page designer (but it does inside the Power Apps Runtime). Using the try..catch like this, I will see the “PCFNotImplemented” error inside the designer, but I can work with the fallback data returned from the “catch”. That way I am able get feedback inside the designer, and I can work with the returned schema ( I don’t need to blindly write the CustomPage).

The Custom Page

In the Custom Page I’ve imported the PCF, placed it on the page, and defined the fetchXml and entityName using some text boxes. The PCF is just an empty “div”.

In the CustomPage settings, I’ve activated the Experimental features “Named formulas” and “ParseJSON function and untyped objects”

Then I’ve defined the named formula “FetchResult”

FetchResult = ForAll(
    Table(ParseJSON(FetchXmlPCF1.output)),
    {
        industrycode: Value(ThisRecord.Value.industrycode),
        industrycode_name: Text(ThisRecord.Value.industrycode_formatted),
        activityCount: Value(ThisRecord.Value.activity_count),
        color: Text(ThisRecord.Value.industrycode_color)
    }
);

The heart of the formula is “ParseJSON” applied to the PCF “.output”. Since it’s an array, I create a Table, and I look with “ForAll”, in order to extract the schema for the properties that I need. Inside the ForAll I extract the Value() or the Text() for “ThisRecord.Value” and the needed property.

Now I can add a gallery where the items are the FetchResult formula:

Here I can access the properties defined in the formula:

The designer knows about the properties and the intellisese helps me:

And I can even use the colors got with the metadata call inside the PCF:

Notice that even if I get the notification “retrieveMultipleRecords: Method not tmplemened”, the PCF works and I can design my gallery based on the dummy data returned from “catch”.

And it works: 🙂 I can see for each IndustryCode how many activities were defined.

The code for this PCF can be found in my github repository: https://github.com/brasov2de/CustomPages_webAPI_calls/tree/master/FetchXmlPCF

The solution containing the Custom Page can be found inside the Demo_CustomPages folder (have a look to the App.fx.yaml and to the Screen.fx.yaml): https://github.com/brasov2de/CustomPages_webAPI_calls/blob/master/Demo_CustomPages/CustomPages_WebAPI_Calls/src/CanvasApps/src/diana_fetchxml_ebb54/Src/App.fx.yaml

Way 2 – PCF using a dynamic output object

The “Way 1” is nice, but if I change the fetchXml, I need to change the schema/formula. And I need to know how the properties were named. PowerFx relies on the delaration made for the formula. Would be nice, if we can get the definition from the PCF too.

Lucky me, the PCF just got this feature lately: dynamic output property. Scott Durow had an awesome blog on this too: First look at PCF dynamic schema object outputs. I recommend to read it carefully, it helped me a lot to understand how it works.

The property of type “object” is not documented yet, but we can find already an example in the PowerApps-Samples: ObjectOutputControl

The PCF code

The manifest have now one more property “outputSchema”. The “output” is not of type “Object” and we have a “property-dependency” where we specify that outputSchema is a “schema property” for “output” property.

<property name="fetchXml"  of-type="SingleLine.Text" usage="bound" required="true" />
<property name="entityName" of-type="SingleLine.Text" usage="bound" required="true" />
<property name="output"  of-type="Object" usage="output" required="false" />
<property name="outputSchema" of-type="SingleLine.Text" usage="bound" />
<property-dependencies>      
  <property-dependency input="outputSchema" output="output" required-for="schema" />
</property-dependencies>

Inside the index.ts the code is pretty much the same like the one from “Way 1”. With a small change: the response is not a string anymore. We pass directly the records. Also we need to generate the JSON schema for the response. Since we don’t know how the records looks like, I’ve used a npm package to-json-schema. This will take the response and generate the schema for me:

npm install to-json-schema
npm install -d @types/to-json-schema  

Now I can use it inside my updateView:

public updateView(context: ComponentFramework.Context<IInputs>): void
   {
    if(context.parameters.fetchXml.raw && context.parameters.entityName.raw 
       && (context.parameters.fetchXml.raw != this.fetchXml || context.parameters.entityName.raw != this.entityName)){
          this.fetchXml = context.parameters.fetchXml?.raw ?? "";
          this.entityName = context.parameters.entityName?.raw ?? "";
          fetchRecords(this.fetchXml, this.entityName, context).then((records) => {
             this.response = records;
             if(this.outputSchema == null){
                 this.outputSchema = toJsonSchema(records);
             }
             this.notifyOutputChanged();
         });
     }
 }

The PCF class had a new public method now. Besides the “init”, “updateView”, “getOutputs” and “destroy” we have now also “getOutputSchema”, where we can pass the schema definition:

public async getOutputSchema(context: ComponentFramework.Context<IInputs>): Promise<any> {
            return Promise.resolve({
                output: this.outputSchema
            });
        }

The Power Apps Sample has a description for the getOutputSchema: “It is called by the framework prior to a control init to get the output object(s) schema“. The problem is that I inside the “init” I don’t know how the schema is looking like. But I’ve learned from Scott’s blog, that we can pass the outputSchema property in getOutputs. The value won’t be used, but when it changes, the getOutputSchema will be triggered. That way the PowerFx inside my CustomPage knows how the response is looking like, regardless which fetchXml will be triggered.

 public getOutputs(): IOutputs
    {
        return { 
            output: this.response, 
            outputSchema : JSON.stringify(this.outputSchema)
         };
    }

The fetchRecords fucntion is exactly like the one from “Way 1”

The Custom Page

The Custom Page is now straight forward.

I need to import the PCF inside the CustomPage and set the FetchXml and the EntityName

Notice the OutputSchema which is generated by the PCF

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "industrycode": {
                "type": "integer"
            },
            "industrycode_formatted": {
                "type": "string"
            },
            "activity_count": {
                "type": "integer"
            },
            "industrycode_color": {
                "type": "string",
                "format": "color"
            }
        }
    }
}

Again, I can work inside the designer, since I have a “try…catch” inside my PCF fetchRecords function. But now I don’t need to declare the columns for my collection: the designer just knows 🙂

And also I can use the color from the customizing:

And that’s all:

The whole code can be found in the same repository as above, inside the folder “FetchXmlPCFDynamicSchema”: https://github.com/brasov2de/CustomPages_webAPI_calls/tree/master/FetchXmlPCFDynamicSchema
and the CustomPage inside the Demo_CustomPages: https://github.com/brasov2de/CustomPages_webAPI_calls/tree/master/Demo_CustomPages/CustomPages_WebAPI_Calls/src/CanvasApps/src/diana_fetchxmldynamicschema_c3e71/Src

Conclusion

Using a PCF is not the low-code way (compared with a flow), but it’s a great example of fusion development. The PCF has to be build only once, and it gives the CustomPages-maker the maximum of performance and productivity. It reduces the number of requests, can also cache the data, increases the response performance and it makes the Solution transport (ALM) much cleaner and easier. It also allows to add even more metadata information, without the need to make tricks or more requests.

I love the “Way 2” – using the new dynamic output property, since the implementation inside the CustomPage gets really easy, clean and less error prone. I can imagine using it also to call CustomAPI requests (a small problem is that the “webAPI.execute” method is still not documented for PCFs, but it works).

In order to make the implementation of the PCF really generic, I need to take care of a little more details like: find a safer way to work with formatted values, work with paging and sorting, add a way to call the metadata only if needed. But the PCF version implemented with a few lines of code can cover already almost all fetches.

The background for the Photo by MESSALA CIULLA: https://www.pexels.com/photo/notebook-with-blank-pages-942872/

Advertisement

3 thoughts on “Call FetchXml Inside Custom Pages Without Flows – The PCF Way

Add yours

  1. This is great, Diana.

    I am looking forward to seeing if I can get it working without being a developer 🙂

    I have been looking for a solution like this for model-driven apps for a long while – together with many others from the community.

    https://powerusers.microsoft.com/t5/Power-Apps-Ideas/Binding-FetchXML-to-Subgrid-with-reference-to-RecordID-to-filter/idi-p/675538

    https://pcfgallery.userecho.com/communities/1/topics/80-dynamic-sub-grid-with-contextual-awareness-using-fetchxml

    Would it be possible to build a similar PCF that can be used in a model-driven app? E.g. to be used as a sub-grid where you can pass a parameter from the form to the FetchXML query.

    1. Hi Niels,
      I totally understand the need of a flexible subgrid (related with the hosting form, maybe 2-3 levels deeper).

      But if we make a dataset PCF based on a fetchXml we loose all the platform interaction ( no ribbon interaction, view-switching,…).
      I think it’s best when we rely on views as a definition for the subgrid. I have a blog where I explain that this should be possible: https://dianabirkelbach.wordpress.com/2022/07/07/subgrid-relationship-strategies-for-dataset-pcf/

      There are a lot of requirements in the idea-link you’ve posted. There could be several approaches.
      – for a hierarchy I think there should be a dedicated PCF, based on the view containing a fetchXml operator “under”.
      – for the rest, there could be a PCF bind to a view “Records: All record types”. The PCF could filter based on a defined relationship. The problme: it could take a lot of effort to implement all the details: the column-filter, make it editable for all data types, paging, sorting

      I’m still hopping that Microsoft will provide ways to get the flexibility we need.
      Actually right now we could make a Custom Page, using the Details List from Creator Kit (https://learn.microsoft.com/en-us/power-platform/guidance/creator-kit/overview) because there we are completely free to define the data. And if the connectors for the Custom Pages are not enough, we can still use a fetchXml as shown in this blog.
      The problem: we can embed only Canvas Apps inside the forms. Actually it works also with Custom Pages (instead of the id of a Canvas App – use the name if a Custom Page), but this is probably not really supported for now.

      I hope we’ll get good solutions for that… just that we are not there yet.

  2. Thank you for taking the time to reply – and doing it very thoroughly. It is much appreciated 🙂

    The Custom Page combined with the Creator Kit solution was also the best alternative I can think of. Then the user experience will be as close as possible to using the actual grids in model-driven apps.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Website Powered by WordPress.com.

Up ↑

%d bloggers like this: