Power Platform
Power Apps
Power Automate
Power BI
Power Virtual Agents
Dataverse
AI Builder
Dynamics 365
Sales
Marketing
Customer Service
Field Services
Project Operations
Finance and Operations
Supply Chain
Retail Commerce
Business Central
HR
Microsoft 365
Microsoft Teams
Sharepoint
Office 365
Azure
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:
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.
And of course I need to declare in the manifest that I’m using the webAPI
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): 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
export const fetchRecords = async (fetchXml: string, entityName: string, context: ComponentFramework.Context) => {
//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 _logicalName
const index = key.indexOf("@Microsoft.Dynamics.CRM.lookuplogicalname");
if(index>0){
return [key.substring(0, index) + "_logicalName", value];
}
//extracting _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.
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): 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): Promise {
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/
