Fetch Related Records with Power Apps Grid Customizer Control

The Power Apps Grid customizer control is a preview feature I really look forward to. In the last two blogs I’ve already shown how to use it for:

• Host your own read-only or editable PCF inside the Power Apps Grid
• Implement calculated columns for offline using the Power Apps Grid

In this blog I’ll push the limits and show how to show related records directly inside the Power Apps Grid cells.

Inside views is not possible to include data from child relationships (one-to-many or many-to-many). But sometimes the customer wants to see that data right there, without having to navigate away first.

Use-case

The Wave 2/2022 announced that the Power Apps Grid control will get “nested grids“. That would solve a lot of these cases. But sometimes we don’t need to go that far. As an example I can think of a few use-cases:

1. We have a table (let’s call it “project”) and want to show all users associated to a project right inside the project list (team members)
2. For each account in the list show how many activities are planned
3. The accounts list should show how many contacts are associated (or notes, or … you choose it)

I’m sure you remember a lot of other use-cases from the past, where you had to make a lot of “gymnastics” to be able to do that. Or you had to say, that you cannot implement it in a supported way. Of course, since the PCF is there, we could do a dataset PCF; there we are free. But a dataset PCF has to take care of a lot of details, and that means a lot of effort.

Using the Power Apps Grid customizer, I’ll show you how to implement such a requirement with less code. I’ve decided to go with the idea 1 in this blog (project team members), and I want it to look something like this (the column “related users” is implemented using my own PCF):

This blog implements a fetch request per row. A more optimized way (fetch in batches) is shown in the next blog: https://dianabirkelbach.wordpress.com/2022/10/29/optimized-fetching-of-related-records-inside-power-apps-grid-customizer-pcf/

Customizing

Since the “team members” is not a “physical” column of the main table, we’ll need a dummy column for that. In Power Apps Grid you can define one PCF control per data type. If you need more control over what is applied, you need to take care in your code, and filter on the column name. I’ve decided to make a dedicated column “Related Users” of type text. This column will always be empty, and I can customize it only on the views where the functionality is needed.

And of course we need to register the PCF for the Power Apps Grid customizer control (by the way, the description in the old customizing needs to be fixed: the name of the PCF should be <PublisherPrefix>_<NameSpace>.<ControlName>)

The PCF implementation

The code can be found in my gitHub repository: https://github.com/brasov2de/GridCustomizerControl/tree/main/RelatedRecords

The Manifest

We have a virtual component and have a property “EventName”. The difference to other Power Apps Grid customizer control, is the definition of “WebAPI” feature (because we need to be able to make requests)

The CellRenderer

We’ll going to need the webAPI, which we get through the parameters of the PCF “init” method. So instead of defining the JSON object contaning the renderer, we need to make a closure, to be able to pass the webAPI

//defining a closure, to be able to use webAPI and the cache
export const generateCellRendererOverrides = 
(webAPI: ComponentFramework.WebApi, peopleCache = {}) => {  
  return  {       
    ["Text"]: (props: CellRendererProps, rendererParams: GetRendererParams) => {             
      const {columnIndex, colDefs, rowData } = rendererParams;         
      const columnName = colDefs[columnIndex].name;     
      if(columnName !== "diana_relatedusers"){ 
        //apply the functionality only for the dummy column
        //by returning null, the default renderer will be applied
        return null;
      }
      const parentId = rowData?.[RECID]; 
      return <People parentId={parentId} webAPI={webAPI} peopleCache={peopleCache}/>
      }        
  }  
}

Another parameter for the closure, is a cache which we create for the control. This way we pass the “peopleCache” to the “People” react component we’ve created.

The cellRenderer is defined only for “Text” columns. Inside the renderer, for all other columns except “diana_relatedusers” we return null. That mean that the default renderer will be applied.

People.tsx – implementing the component

In the cell renderer, we’ve used the react component “People”; a react function component.

The component has an internal “people” state. The initial value is the value from cache corresponding to this record, or null otherwise.

const [people, setPeople] = 
React.useState<Array<any> | null>(peopleCache[parentId ?? ""] ?? null); 

Based on “useEffect” we register the code where we fetch the related users, which will be executed when the parentId changes (of course, only if the cache is empty).

export const People = React.memo(function PeopleRaw({parentId, webAPI, peopleCache}: IPeopleProps){
    const [people, setPeople] = React.useState<Array<any> | null>(peopleCache[parentId ?? ""] ?? null); 
   
    React.useEffect(() => {          
        if(parentId && peopleCache[parentId ?? ""]==null){          
            webAPI.retrieveMultipleRecords("systemuser", ["?fetchXml=", 
                `<fetch distinct="false" mapping="logical" returntotalrecordcount="true" page="1" count="5" no-lock="true">`,
                //...fetch body goes here
            `</fetch>`].join('')).then((result)=>{
                peopleCache[parentId ?? ""] = result.entities;
                setPeople(result.entities);                                    
            }); 
        }       
        }, [parentId]);

    return <div>
        {people == null 
            ? "..." 
            : people.map((person)=> person.entityimage_url 
                ? <img src={person.entityimage_url} style={{height: "32px", width:"32px", backgroundColor: "gray", borderRadius: "15px", margin: "1px"}}/>
                : null
                ) 
        }</div>
});

The react function component returns “…” while the fetch is executed; otherwise, a list with the images will be shown (or an empty div).

People.tsx – check if the component is still mounted

The Power Apps Grid will unload sometimes the cells. When we scroll down or up, the rows that are not visible anymore will be unloaded. Since we make an async request, before we change the state of the component (which will trigger another render of the cell), we need to check that the component is still mounted. If you want to read more about how to check if the component is unmounted, check the Jason Watmore’s blog that inspired me.

For that we need to declare a mounted “ref”, initialized on false. By using useEffect, with an empty dependency, we can detect if the component was monted of unmounted. Then we can decide if the “people” state still needs to be set (or only the cache will be set):

export const People = React.memo(function PeopleRaw({parentId, webAPI, peopleCache}: IPeopleProps){
    const [people, setPeople] = React.useState<Array<any> | null>(peopleCache[parentId ?? ""] ?? null); 
    const mounted = React.useRef(false);

    React.useEffect(() => {
        //component was mounted
        mounted.current = true;
        return () => {
            //component was unmounted
            mounted.current = false;      
        };
    }, []);

    React.useEffect(() => {          
        if(parentId && peopleCache[parentId ?? ""]==null){          
            webAPI.retrieveMultipleRecords("systemuser", ["?fetchXml=", 
                `<fetch ` //fetch goes here
            `</fetch>`].join('')).then((result)=>{
               //cache the result
                peopleCache[parentId ?? ""] = result.entities;
                //only if still mounted, set the state
                if(mounted.current){
                    setPeople(result.entities);                    
                }             
            }); 
        }       
        }, [parentId]);

    return (<div>
        //content goes here
        </div>
});

Here is the complete code

I’ve included some console logs, when the component gets unmounted. The log on line 40, when the fetch is back after the component is unmounted, would have caused an error.

I’ve made a test when I start scrolling down, right after the grid is loaded (but not before the customizer control starts to get applied). If I scrolled fast enough, was pretty easy to reproduce this case. Now our implementation is safe.

Index.ts

The last piece: calling the closure from the init function, and register the cellRenderer

   public init(
        context: ComponentFramework.Context<IInputs>,
        notifyOutputChanged: () => void,
        state: ComponentFramework.Dictionary
    ): void {
        const eventName = context.parameters.EventName.raw;    
        if (eventName) {
            const paOneGridCustomizer: PAOneGridCustomizer = { 
                cellRendererOverrides: 
                    generateCellRendererOverrides(context.webAPI, this.peopleCache)             
            };
            (context as any).factory.fireEvent(eventName, paOneGridCustomizer);            
        }  
    }

Caching

The cell renderer main purpose is to allow us to change the cells content. We work with a react component, so we know that React decides when a component needs to be rerendered or unmounted.

Also the Power Apps Grid is implemented to allow infinite scrolling and show only the needed rows. It will unload the cells while scrolling, changing the filter, and so on. Since we make fetches inside the cells, caching the fetched data is very important. Unfortunately we cannot fetch more records at once, but because of performance reasons and to reduce webAPI consumption, we should cache the data.

I’ve declared my cache as a private member inside my PCF component. For each rowId, I cache the array of retrieved records (systemuser). I pass this cache to the cellRenderer closure. That way all the cellRenderer (and all react components) will use the same cache.

export class PAGridRelatedRows implements ComponentFramework.ReactControl<IInputs, IOutputs> {

    private peopleCache: { [key: string]: any } = {};

….

}

This will cache the data while the user scrolls through the records, while he/she is using the column filters or is changing the sort order. But if I use the filter above the component, my PCF will be recreated, and the cache will be empty again.

I’ve tried to declare the cache as a static variable.

export class PAGridRelatedRows implements ComponentFramework.ReactControl<IInputs, IOutputs> {
    static peopleCache: { [key: string]: any } = {};

That solves the issue of having the cache empty after the user is using the search box, but it introduced another problem: the cache will never be cleaned up. Not even after the user is switching to another table or another view. Maybe that could work for some approaches, where the data never changes, but it would introduce the risk of memory leaks. For data that could change, that it’s not an option at all, since we never know when to refresh the cache.

Here is a short demo on how the cache is used

Conclusion

Implementing fetches inside the Power Apps Grid components has some downsides.

• – We can fetch only row after row.
• – We need to cache the retrieved records.
• – We won’t be able to sort or filter on this column

But it’s amazing that now we are able to implement grids where we can show records related with the main table, which we cannot define in the fetchXml of the view.

What do you think?

Note: This blog implements a fetch request per row. A more optimized way (fetch in batches) is shown in the next blog: https://dianabirkelbach.wordpress.com/2022/10/29/optimized-fetching-of-related-records-inside-power-apps-grid-customizer-pcf/

PS (GitHub Copilot)

Thanks to the Microsoft MVP Program benefits, I’ve got a free license for GitHub Copilot, which has a VSCode extension. I wasn’t sure how much I should trust AI, but see yourself how the Copilot kept whispering me what I should do next, while I’ve implemented the “mounted” feature (the gray code in the screenshots).

I’ve wrote “const mounted = ” and I’ve got

I’ve wrote React.useEffect(()=> { }, []) and it knew I want to implement the mounted stuff

Wrote an “if” after the fetch was returned, and it understood what I wanted to do:

Isn’t that scarry? But also saves a lot of time. I’ve started to love my copilot. I already feel that I should name “him”. Or “her”? Like the way a driver names his/her car.

The hard part in being assisted by the Copilot is detecting the “mistakes”. Sometimes there are “mistakes” hidden inside the code. Of course you need to review what the Copilot proposed. But sometimes it’s hard to detect the parts. Remember when you had to do some math exercises, and it was hard to find the place where you placed a “+” instead of a “-“, because it seemed ok at the first glance. But still.. I need a name 😉