Color Grid PCF Control with Formulas and Functions

Color Grid PCF Control allows the user to personalize the colors of the grid like Header color, row Color and cell color.

Instead of just static color values, Color Grid allows the user to set the row background Color and Cell background dynamically using Excel-like formulas and custom functions.

Currently PCF control samples do not illustrate any way to implement formulas and functions that can be used in Control Properties. This article is an attempt to address that, to show a way how we can  implement formulas and custom functions in PCF.

You can find the links to the source code and the solution file below:

https://github.com/srirambalajigit/PCFControls/tree/master/ColorGrid

Solution File Link

https://github.com/srirambalajigit/PCFControls/blob/master/ColorGrid/ColorGridControl.zip

Below are the steps to use the control in Dynamics 365.

1. Import the solution ColorGridControl.zip to CRM

2. Open the “Active Accounts” view for Account entity and add column “Open Revenue” to the view.

3. Click on “Custom Controls” link in View.

4. Click on “Add Control”

5. Select Web, Phone and Tablet radio buttons.
6. Set the headerBackgroundColor and headerForegroundColor.

7.   Set the Color Control’s cellBackgroundColor property with formula below:

=(GETFIELDVALUE('openrevenue') > 100000) ? 'green': 'red';

Every formula starts with = sign.
This follows conditional formula format shown below

=Condition?TrueResult:FalseResult

Condition
An expression which is used as a condition. Such formulas commonly contain comparison operators (such as <, >, == and %) and functions such as GETFIELDVALUE.

In above scenario, (GETFIELDVALUE('openrevenue') > 100000) is condition.

  GETFIELDVALUE is a custom function, which accepts field’s schema name as input, and returns     the field value.

TrueResult

The corresponding value to return for a condition that evaluates to true.

In above case, it returns green when condition evaluates to true.

FalseResult

The corresponding value to return for a condition that evaluates to false.

In above case, it returns red when condition evaluates to false.

8. Set the Color Control rowBackColor property with Formula as shown below

=((ROWNUMBER() % 2) == 0) ? 'lightblue' : 'white';

This follows conditional formula format shown below

=Condition?TrueResult:FalseResult

Condition

((ROWNUMBER() % 2) == 0)

Here we are using ROWNUMBER function which returns the current row number. We are using % which is modulus operator, that returns the remainder left over, when ROWNUMBER is divided by 2 and that is compared with zero.

TrueResult
The corresponding value to return for a condition that evaluates to true.

In above case, it returns lightblue when condition evaluates to true.

FalseResult

The corresponding value to return for a condition that evaluates to false.

In the above case, it returns white when condition evaluates to false.

9. Navigate to Active Accounts view in CRM.