PowerGrid

lowcodera PowerGrid Component

Purpose

Packed with features and customization options, the lowcodera PowerGrid takes presenting, interacting and editing your tabular data in Power Apps to a new level.

Binding Data

You can bind data to the PowerGrid using the items property. Simply select your data source like you would with any out of the box control like the Gallery or Data Grid.

PowerGrid Properties

Theme	Styles the grid with one of several themes.	Input
Auto-Size	Size the grid columns with three different options Auto-Size All – auto-sizes columns based on cell content and column name lengths Auto-Size All (Skip Header) – auto-sizes columns based on cell content and ignores headers Size to Fit – auto-sizes the grid content to fit the width of the component	Input
Row Drag	Enables or disables the ability for end users to re-order rows by dragging rows	Input
Grouped Rows	Enables or disables the ability for end users to use the row grouping function. The row grouping feature allows the data to be grouped by a column or hierarchy of columns.	Input
Use Custom Grouping	Boolean to determine if rows should be grouped based on the column schema definition instead of what the end user does as run-time	Input
Columns Sidebar	Enables or disables the ability for end users to see the columns sidebar. The columns sidebar allows users to select which columns to show in the grid at run-time	Input
Filters Sidebar	Enables or disables the ability for end users to see the Filters sidebar. The filters sidebar provides advanced data filtering capability for users at run-time	Input
Column Search	Enables or disables the in-column search function for users	Input
Use Dataset API	Enables or disables use of the Dataset API feature. The Dataset API is new feature from Microsoft and simplifies how data is saved back to a data source. There are currently limitations and known issues with this approach which is why it is not always suitable for saving data back to your data source	Input
Hide Toolbar	Boolean to hide or show the PowerGrid toolbar which is above the grid.	Input
items	Used to bind to your data source	Input
ColumnsDataSet_Items	Used to define column schema in JSON so that advanced grid column features can be used.	Input
Load All Rows	Preloads all rows instead of loading data on scroll	Input
Font Size	Sets the font size of the grid content	Input
Row Height	sets the height of rows for the grid	Input
Set Filter	definition of the pre-defined filter settings to apply to the grid	Input
filter	JSON output of the filters currently applied to the grid. Can be copied and pasted into Set Filter input property for setting predefined filters	Output
onSelectedItems	Returns a collection of the items selected on the grid	Output
onEventType	Outputs the current event which has been triggered on the grid.	Output
actionButtonRowId	Returns the id of the row from where the user invoked an action button	Output

Columns Dataset

The items property allows you to bind the grid to your data source and render it. In addition to this, there is some advanced functionality available in the PowerGrid which is unlocked through using the columns dataset property which is defined in JSON. Key attributes for our column dataset schema are documented below

Attribute	Description
DisplayName	Sets the display name of the column
IsKey	Boolean to define if the column is the primary key. Necessary when the API dataset method is not used to save records.
visible	Boolean to define whether the column is visible or not on the grid
editable	Boolean to determine if a column is editable or not
DataType	Defines the column Data Type. Supported values are: String Number Date Choices html rating actionButton
DataFormat	Allows advanced data formatting expressions to be applied. See data formatting for examples.
ConditionalFormatting	Allows in-cell conditional formatting rules to specified for the column. See Conditional Formatting for examples
Choices	Used to provide a JSON data schema for any column defined with a datatype of Choices
rowGroupIndex	Integer to define the column grouping order for this column
showCheckbox	Boolean to determine if a checkbox is shown against the column

Example Schema

Table(
    {
        fieldName:"ID",
        displayName:"ID",
        isKey:true        
    },
    {
        fieldName:"Title",
        displayName:"Work Order"        
    },
    {
        fieldName:"District",
        displayName:"District"
    },
    {
        fieldName:"Service",
        displayName:"Service"        
    },
    {
        fieldName:"LeadTech",
        displayName:"LeadTech"                
    },    
    {
        fieldName:"WorkDate",
        displayName:"Work Date",                
        dataType:"date",//datatype for the value eg. string,number
        dataFormat:"DD-MMM-YYYY" //Custom date format         
    },
    {
        fieldName:"Payment",
        displayName:"Payment",
        dataType:"choices",
        choices:ForAll([
            {
                key:"COD",
                value:"C.O.D."
            },
            {
                key:"account",
                value:"Account"
            }
        ], {Value: ThisRecord})  
    },
    {
        fieldName:"LbrHrs",
        displayName:"Labor Hours",
        dataType:"number",        
        dataFormat:"{{value:d1}}"// data format mechanism to format the number value eg. 100.0
    },
    {
        fieldName:"Techs",
        displayName:"Techs",       
        dataType:"number",
        conditionalFormat: //Display background-color of the value based on two or more conditions 
        ForAll([
            {
                condition: "value == 1",
                ifTrue: {backgroundColor: "red"}                
            },
            {            
                condition: "value == 2",
                ifTrue: {backgroundColor: "blue"}                
            },
            {
                condition: "value == 3",
                ifTrue: {backgroundColor: "green"}                
            }
        ], {Value: ThisRecord})      
    },
    {
        fieldName:"TotalPayable",
        displayName:"Total Cost",
        dataType:"number",
        dataFormat:"{{value:d2}}",//Set the data format of the value eg. 100.25
        dataAlign: "right"//Set the alignment of the value eg. left,center,right        
    }
)

Saving Data

There are two approaches for saving data back to your data source- using the dataset API or by patching records via the OnChange Event. The Dataset API is simpler to use, however it is a new feature from Microsoft and there are some limitations which means it is not suitable for all situations.

Using the Dataset API

To use this method for saving data, make sure the “Use Dataset API” property is set to true and the Hide Toolbar property is set to false.

As users make changes to column values, the toolbar will show a count of unsaved row changes. Users need to press the save button in the toolbar to commit changes back to the underlying data source.

Saving via the OnChange Event

Before using this method:

1. Set the IsKey attribute to true for whichever the unique ID column is in your Columns Dataset.

2. Set the “Use Dataset API” property to false

3. Set the “Hide Toolbar” property to true.

This method requires PowerFX to be inserted into the OnChange event of the PowerGrid. There are 3 sections to the PowerFX code which are documented in the example code snippet below.

• Generating a collection of changed rows

• Generating a collection of changed values from the changed rows

• Patching the changed values back to the data source

See below for an example code snippet for saving data via the OnChange Event:

// 1.  Parsing changed rows into a collection
Clear(updatedRowsCol);
Clear(updatedItemsCol);

ForAll(
    Split(Self.onUpdatedItems,"</Row>"),
    Collect(updatedRowsCol,Result)
);

// 2. Parsing the ID, ColumnName and new value for each changed row

ForAll(
    updatedRowsCol,
    Collect(
        updatedItemsCol,
        {
            id: First(Split(ThisRecord.Value,"</>")).Result,
            fieldName: First(LastN(Split(ThisRecord.Value,"</>"),2)).Result,
            value: Last(LastN(Split(ThisRecord.Value,"</>"),2)).Result
        }
    )
);

// 3. Save the final data - Patch Updates

ForAll(
    updatedItemsCol As UpdatedItems,
    Switch(
        UpdatedItems.fieldName,
        "field_LeadTech",
        Patch(
            colWorkOrders,
            LookUp(
                colWorkOrders,
                ID = Value(UpdatedItems.id)
            ),
            {LeadTech: UpdatedItems.value}
        ),
        "field_ReqDate",
        Patch(
            colWorkOrders,
            LookUp(
                colWorkOrders,
                ID = Value(UpdatedItems.id)
            ),
            {
                ReqDate: DateValue(
                    UpdatedItems.value,
                    "en-GB"
                )
            }
        ),
        "field_Service",
        Patch(
            colWorkOrders,
            LookUp(
                colWorkOrders,
                ID = Value(UpdatedItems.id)
            ),
            {Service: UpdatedItems.value}
        ),
        "field_District",
        Patch(
            colWorkOrders,
            LookUp(
                colWorkOrders,
                ID = Value(UpdatedItems.id)
            ),
            {District: UpdatedItems.value}
        ),
        "field_LbrHrs",
        Patch(
            colWorkOrders,
            LookUp(
                colWorkOrders,
                ID = Value(UpdatedItems.id)
            ),
            {LbrHrs: Value(UpdatedItems.value)}
        )
    )
);

Copy and Pasting Data

The grid supports copying and pasting data into it from data sources like Excel. The Ctrl + V keyboard shortcut will need to be used to perform the paste operation.

When data is pasted into the grid, there is a pastedData EventType which is exposed. This should be used in the OnChange event to process the pasted data. The pasted data is held in the onUpdatedItems output property, as per when any data is updated in the grid.

You will need to handle the two scenarios of when data is pasted into existing records and when pasted data is creating new rows. When the pasted data is creating a new row on the grid, the id attribute for that row of onUpdatedItems will be set to "NEW_ROW".

See the example PowerFx code below for how the OnChnage event of the grid can be used to process regular updates to the grid as well as from Copy and Paste operations.#

    Clear(updatedRowsCol);
    Clear(updatedItemsCol);

If(
    Self.onEventType = "pastedData",

// 1.  Parsing pasted rows into a collection
ForAll(
        Split(
            Self.onUpdatedItems,
            "</Row>"
        ),
        Collect(
            updatedRowsCol,
            Result
        )
    );
    
// 2. Parsing the ID, ColumnName and new value for each pasted row
ForAll(
        updatedRowsCol,
        Collect(
            updatedItemsCol,
            {
                id: First(
                    Split(
                        ThisRecord.Value,
                        "</>"
                    )
                ).Result,
                fieldName: First(
                    LastN(
                        Split(
                            ThisRecord.Value,
                            "</>"
                        ),
                        2
                    )
                ).Result,
                value: Last(
                    LastN(
                        Split(
                            ThisRecord.Value,
                            "</>"
                        ),
                        2
                    )
                ).Result
            }
        )
    );
    
// Patch any new records from the paste operation. New rows hav an id value of NEW_ROW
ForAll(
        Filter(
            updatedItemsCol,
            id = "NEW_ROW"
        ) As UpdatedItems,
        Switch(
            UpdatedItems.fieldName,
            "Payment",
            Patch(
                colltestData,
                Defaults(colltestData),
                {Payment: UpdatedItems.value}
            ),
            "Title",
            Patch(
                colltestData,
                Defaults(colltestData),
                {Title: UpdatedItems.value}
            ),
            "JoinDate",
            Patch(
                colltestData,
                Defaults(colltestData),
                {JoinDate: DateValue(UpdatedItems.value)}
            )
        )
    );
    
//Patch Updates for pasted rows which already exist
ForAll(
        updatedItemsCol As UpdatedItems,
        Switch(
            UpdatedItems.fieldName,
            "Payment",
            Patch(
                colltestData,
                LookUp(
                    colltestData,
                    ID = Value(UpdatedItems.id)
                ),
                {Payment: UpdatedItems.value}
            ),
            "Title",
            Patch(
                colltestData,
                LookUp(
                    colltestData,
                    ID = Value(UpdatedItems.id)
                ),
                {Title: UpdatedItems.value}
            ),
            "JoinDate",
            Patch(
                colltestData,
                LookUp(
                    colltestData,
                    ID = Value(UpdatedItems.id)
                ),
                {JoinDate: DateValue(UpdatedItems.value)}
            )
        )
    ),
    
//Handle regular updates to rows

    
// 1.  Parsing chnaged rows into a collection
ForAll(
        Split(
            Self.onUpdatedItems,
            "</Row>"
        ),
        Collect(
            updatedRowsCol,
            Result
        )
    );
    
// 2. Parsing the ID, ColumnName and new value for updated row
ForAll(
        updatedRowsCol,
        Collect(
            updatedItemsCol,
            {
                id: First(
                    Split(
                        ThisRecord.Value,
                        "</>"
                    )
                ).Result,
                fieldName: First(
                    LastN(
                        Split(
                            ThisRecord.Value,
                            "</>"
                        ),
                        2
                    )
                ).Result,
                value: Last(
                    LastN(
                        Split(
                            ThisRecord.Value,
                            "</>"
                        ),
                        2
                    )
                ).Result
            }
        )
    );
    
    
//Patch Updates for chnaged rows
ForAll(
        updatedItemsCol As UpdatedItems,
        Switch(
            UpdatedItems.fieldName,
            "Payment",
            Patch(
                colltestData,
                LookUp(
                    colltestData,
                    ID = Value(UpdatedItems.id)
                ),
                {Payment: UpdatedItems.value}
            ),
            "Title",
            Patch(
                colltestData,
                LookUp(
                    colltestData,
                    ID = Value(UpdatedItems.id)
                ),
                {Title: UpdatedItems.value}
            ),
            "JoinDate",
            Patch(
                colltestData,
                LookUp(
                    colltestData,
                    ID = Value(UpdatedItems.id)
                ),
                {JoinDate: DateValue(UpdatedItems.value)}
            )
        )
    )
);

Conditional Formatting

The columns schema can be used to define cell based rules for conditional formatting. The example code snippet below shows how the background color of a cell can conditionally be defined

    {
        fieldName:"AccountStatus",
        displayName:"Account Status",
        dataType:"number",
        conditionalFormat: ForAll([
            {
                condition: "value == 1",
                ifTrue: {backgroundColor: "red"}                
            },
            {            
                condition: "value == 2",
                ifTrue: {backgroundColor: "blue"}                
            },
            {
                condition: "value == 3",
                ifTrue: {backgroundColor: "green"}                
            }
        ] ,{Value: ThisRecord})   
    }

Supported operators

Relational operators

• >

• <

• >=

• <=

• ===

• <> or !== or !=

Logical operators

• && or And

• || or Or

• ! or Not

• ‘value’ or ‘x’ is used for the content in the cell

• Schema for conditional formatting JSON (conditionalFormat in column definition of the grid)

When there are overlapping regions then the later style takes the precedence

Any CSS attributes can be used inside the ‘ifTrue’ and ‘ifFalse’. Also use camelCase for the CSS attributes.

Data Formatting

Cell values can be formatted in the PowerGrid by specifying the appropriate expression in DataFormat attribute of the JSON column schema.

Decimal places

Use the expression below, where the number after d denotes the number of decimal places. The example below formats the numbers to 2 decimal places. dataFormat: "{{value:d2}}"

Thousand separators

A thousand separator can be specified by extending the decimal formatting expression with “:t”. The example below formats the numbes to 0 decimal places, with a thousand separator

dataFormat: "{{value:d2:t}}"

Prefixes

Values can also be prefixed into the expression e.g. Currency Symbol. The example below formats the number to 0 decimal places, with a thousand separator and a £ currency symbol

dataFormat: "£{{value:d2:t}}"

Dates

When the DataType is Date, various data formatting expressions can be used to format the date.

dataFormat:"DD-MM-YYYY"

Action Buttons

Action buttons allow you to define in-line interactive buttons within the grid. You can have one or more buttons per row and style each one independently. Importantly, you can also assign an event to a button and define PowerFx expressions to trigger when the user clicks on it.

Adding buttons

Action buttons can be defined in the Column Schema by setting the DataType attribute for a column to be “actionButton”

Sample button definition

        fieldName: "button_column1",
        visible: true,
        displayName: "",
        editable: false,
        dataType: "actionButton",
        actionButtonProps: [
            {
                event: "viewProfile",
                id: "viewProfile",
                variant: "Outline",
                tooltip: "View Profile",
                name: "view",
                disabled: false,
                icon: "eye",
                fontColor: "green",
                backgroundColor: "transparent",
                borderColor: "transparent",
                width: 30,
                height: 30,
                fontSize: 12,
                borderRadius: 10,
                margin: 0
            }
        ]

Action button attributes

event	Name of the event which can be used in the OnChange event formula
id	Unique of the event. Comes into use when conditional visibility rules are applied
variant	The style of the button. “Outline” or “Default”
tooltip	A tooltip description which will be displayed on hover
name	Text to appear on the button
disabled	Boolean to denote whether button can be clicked or not
icon	FontAweseome icon reference
fontColor	Color of text ad icon
backgroundColor	Background color of button
borderColor	Border color of button
width	Width of button in px
height	Height of button in px
fontSize	Size of text and icon
borderRadius	Radius of the button in px
margin	Margin between buttons

Handling user interaction

When a user clicks on an action button the PowerGrid onEventType property outputs the name of the event, as defined in the column definition. The actionButtonRowId output property also returns the id of the row the user has interacted with.

To handle the interaction, follow these steps:

1. Make sure you have defined a unique name for the action button in the event attribute

2. In the OnChange of the PowerGrid, add a PowerFx expression for when the onEventType is equal to the name of your event.

3. Use the actionButtonRowId output to perform your action against the appropriate row in your data source

4. See below for example, where the event has been called viewProfile

Switch(
    Self.onEventType,
    "viewProfile",
    Set(
        VarPlayerProfileID,
        Self.actionButtonRowId
    );
    Navigate('Player Profile')
)

Setting Predefined Filters

The PowerGrid supports scenarios where you need the grid prefiltered. To enable this simply populate the Set Filter input property with the appropriate JSON expression.

To save you having to manually generate the JSON for the filter query, the easiest way of setting a predefined filter is to first of all apply the filter condition(s) to the grid as an end user. Then copy the string generated in the output property filter and paste this into the set filter input property

Example filter

{
  "Nationality": {
    "filterType": "multi",
    "filterModels": [
      null,
      {
        "values": [
          "Argentina",
          "Austria",
          "Belgium"
        ],
        "filterType": "set"
      }
    ]
  }
}

Using Pre-set Grouping

It's possible to have predefined column grouping on the grid. To enable this follow thee steps

1. Turn on the Use Custom Grouping property in the grid (set to true)

2. In the column schema, for each column you want to group by, set the rowGroupIndex attribute. This should be a number from 0 onwards for each column which will be grouped. See example below.

{
    fieldName: "name",
    displayName: "name", 
},
{
    fieldName: "country",
    displayName: "category",
    rowGroupIndex: 0
},
{
    fieldName: "Language",
    displayName: "Language",
    rowGroupIndex: 1
}

Limitations/Known issues

• Saving decimal data is currently not supported by the new dataset API

• Saving data to SQL data source is currently not supported the new dataset API

• Under certain scenarios, changes on the grid are not reflected in real-time even though they are saved to the data source