Implement Calculated Columns with Power Apps Grid Control

We can implement calculated columns using Dataverse formula columns (using Power Fx), and that should be the first choice. But that won’t work in offline mode (at least not that I am aware of). Using the Power Apps Grid customizer control, I could get it work offline too.

In case you don’t know about the Power Apps Grid customizer control, have a look to the official blog or visit my last blog (where I have an example of custom cell renderer and custom cell editor).

The Power Apps Grid is a preview feature, which shouldn’t be in production yet.

The use case

Let’s consider that the accounts need to be visited on regular base. We can create a column inside the account table called “ScheduledDate”, which we calculate after each visit. But sometimes there are delays, and the account couldn’t be visited on time. When the employee plans the next visits, he/she should see this appointment-proposal for the next Monday (or take whatever rule you like).

Let’s consider one account was planned to be visited on 6.Sept.2022. But the customer wasn’t visited back then. When I want to plan the next visit, and I open the list of accounts on 16.Sept.2022 , I should see the next proposal on 19.Sept.2022. If I open the list one week later, I should see the appointment proposal on 26.Sept.2022.

# Calculated column using Dataverse formula columns

Based on my ScheduledDate column ( of type DateTime) I’ve define the formula column for NextAppointment:

If( ScheduledDate < UTCToday() , 
DateAdd(UTCToday(), 7-Weekday(UTCToday()), TimeUnit.Days),
ScheduledDate) 

If the ScheduledDate is in the past, I take the next Monday; otherwise I take the ScheduledDate without changing it.

Pros/Cons of formula columns

This feature is gorgeous. Every time the data is called, the column will be reevaluated. Each day the data will be automatically changed, showing the re-calculated visit date. I don’t need any background jobs for that. 🙂

So it basically works. Why is this not enough?

Problem 1: Am I forced to use TimeZoneIndependent columns for this use-case?

I had a problem in defining the column formula though: it seems that I need to define the ScheduledDate as TimeZoneIndependent. Maybe there is a better way, that I am not aware at. It you know a way to work with the UserLocal DateTimes please let me know.

In the PowerFx formula column docs, there are limitations on working with DateTime. We can use the following function types in a formula column:

• DateTime (TZI)
• DateTime (User local) (limited to comparisons with other user local values and the DateAdd function)
• DateTime (Date only) (limited to comparisons with other date-only values, and the DateAdd function)

Also found this documentation for Dataverse for Teams (but I guess it applies here too):

The problem is that I have to compare to “today”, and since we are on SQL, only the UTCToday is available. If I try to compare to a “non TimeZoneIndependent DateTime” using PowerFx, I get an error:

So I could only fix that by using a TimeZoneIndependent column für ScheduledDate. Maybe I’m just lacking the PowerFx knowledge to find a better solution . Using a TimeZoneIndependent was not a show stopper for this demo, but it could be for a real-life project.

Problem 2. Doesn’t work in offline mode

That’s actually the bigger problem. To solve that I’ve tried out and implemented a customizer control for Power Apps Grid.

The result of Dataverse formula column

The read-only grid showing the calculed NextAppointment in online mode, based in ScheduledDate:

But it doesn’t work in offline mode:

NextAppointment doens’t get calculated in offline mode

# Calculated columns with Power Apps Grid control

I’ve created a PCF customizer control for Power Apps Grid:

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

And now it works also in offline mode:

Since we do a customizer control anyway, it was easy to implement color coding too, to help visual orientation: “red” for overdue appointments, “green” for the rest.

…while the read-only grid still doesn’t show the NextAppointmennt (or is showing the value saved during the last sync of data)

Here is a short demo:

Implementation aspects

For the implementation, I only had to write the CellRendererOverride. The “props” parameter provides the value and the formattedValue for the current cell/column. But that doesn’t help much in this use-case, because I need to read the data for other cell from this row. I could help myself by using rendererParams parameter, where I get information about the columns and row data.

 const nextAppointmentCell = (props: CellRendererProps, rendererParams: GetRendererParams) => {   
 const { value , formattedValue, } = props;    
        const {columnIndex, colDefs, rowData } = rendererParams;
        const columnName = colDefs[columnIndex].name;     
       //use this renderer onyl for the column crec8_nextappointment
        if(columnName !== "crec8_nextappointment"){
            return null;
        }
        const scheduledDate = (rowData as any)["crec8_scheduleddate"];
        if(scheduledDate==null){
            return null;
        }
        //if overdue, calculate next monday
        const nextDate = new Date(scheduledDate);
        const isOverdue = nextDate.valueOf() < new Date().setHours(0,0,0,0);
        const changedValue = isOverdue ? nextMonday(new Date()) : nextDate;        
        return (<label className="ORBIS.PAGCalculatedColumns">
                 <div className={scheduledDate == null ? "" : isOverdue ? "overdue" : "regular"}>
                 //using context formatting methods here
                 {context.formatting.formatDateShort(changedValue)} 
                 </div>
            </label>
            )           
}

I could even make use of the PCF context, which provided me the formatting methods for my calculated date. In order to be able to work with the context, I had to make a “closure”:

 export const generateCellRendererOverrides = 
     (context: ComponentFramework.Context<IInputs>) => {

    const nextAppointmentCell = (props: CellRendererProps, rendererParams: GetRendererParams) => {                              
        //using context.formatting.formatDateShort(changedValue)
        
    };

    return  {       
        ["DateOnly"]: nextAppointmentCell, 
        ["DateAndTime"]: nextAppointmentCell
    }  
}

While in index.ts I’ve defined the customizer by passing the context

   const paOneGridCustomizer: PAOneGridCustomizer = { 
                cellRendererOverrides: generateCellRendererOverrides(context)             
            };

Conclusion

Dataverse formula columns would be my first choice, as far they work for my use case. The Power Apps Grid customizer control allowed me to feel the gap. But it’s still not a prefect solution, since the sorting/filtering for the column calculated inside the Power Apps Grid customizer control will use the Dataverse data, not the one calculated inside the Power Apps Grid.