1 of 1

Data Grid

Data grid component for Universal Dashboard.

The UDDataGrid component is an advanced version of the table that is useful for displaying large amounts of data. It supports many of the same features as the table but also provides complex filtering, row virtualization, multi-column sort and more.

Simple Data Grid

Data grids load their data via the -LoadRows event handler. You will need to return a hashtable that contains the row data and the total number of rows.

Columns are defined using hashtables.

Prior to version 3.3., column field names must be in camel case. For example, the property Name would need to be name while the property FirstName would need to be firstName.

New-UDDataGrid -LoadRows {
    $Data = @(
        @{ Name = 'Adam'; Number = Get-Random}
        @{ Name = 'Tom'; Number = Get-Random}
        @{ Name = 'Sarah'; Number = Get-Random}
    )
    @{
        rows = $Data 
        rowCount = $Data.Length
    }
} -Columns @(
    @{ field = "name"}
    @{ field = "number"}
) -AutoHeight

Columns

Columns are customizable using hashtables. You can find the supported properties below.

Rendering Custom Columns

You can render custom components in columns by specifying render within the column hashtable. You can access the current row's data by using the $EventData or $Row variable

In this example, the number is shown in the name column with a New-UDTypography component.

New-UDDataGrid -LoadRows {  
    $Rows = 1..100 | % {
        @{ Name = 'Adam'; Number = Get-Random}
    }
    @{
        rows = $Rows
        rowCount = $Rows.Length
    }
    
} -Columns @(
    @{ field = "name"; render = { New-UDTypography $EventData.number }}
    @{ field = "number"}
) -AutoHeight

Flexible Width Columns

Column fluidity or responsiveness can be achieved by setting the flex property of a column.

The flex property accepts a value between 0 and ∞. It works by dividing the remaining space in the grid among all flex columns in proportion to their flex value.

For example, consider a grid with a total width of 500px that has three columns: the first with width: 200; the second with flex: 1; and the third with flex: 0.5. The first column will be 200px wide, leaving 300px remaining. The column with flex: 1 is twice the size of flex: 0.5, which means that final sizes will be: 200px, 200px, 100px.

To set a minimum and maximum width for a flex column set the minWidth and the maxWidth property on the column.

New-UDDataGrid -LoadRows {  
    $Rows = 1..100 | % {
        @{ Name = 'Adam'; Number = "This column is a very long string. This column is a very long string. This column is a very long string. This column is a very long string. This column is a very long string. This column is a very long string."}
    }
    @{
        rows = $Rows
        rowCount = $Rows.Length
    }
    
} -Columns @(
    @{ field = "name"; render = { New-UDTypography $EventData.number }}
    @{ field = "number"; flex = 1.0}
) -AutoHeight

LoadRows

The -LoadRows parameter is used to return data for the data grid. Table state will be provided to the event handler as $EventData. You will find the following properties within the $EventData object.

Paging

To implement paging, you can access the page and pageSize properties of the $EventData variable.

New-UDDataGrid -LoadRows {  
    $Rows = 1..100 | % {
        @{ Name = 'Adam'; Number = Get-Random}
    } 
    @{
        rows = $Rows | Select-Object -First $EventData.pageSize -Skip ($EventData.page * $EventData.pageSize)
        rowCount = $Rows.Length
    }
    
} -Columns @(
    @{ field = "name"; }
    @{ field = "number"}
) -AutoHeight -Pagination

Filtering

The filter hashtable is included in the $EventData for the -LoadRows event handler when a filter is defined. The hashtable has a structure as follows.

@{
    items = @(
        @{ 
            columnField = "Name"
            overatorValue = "contains"
            value = "test"
        }
    )
    linkOperator = "and"
}

Items

The items property contains an array of columns, operators and values. You can use these to filter your data.

LinkOperator

The link operator field is used to specify the link between the filters. This can be and or or.

Sorting

The $EventData object will contain a Sort property when the user sorts the data grid. It contains properties for each column that is sorted. The properties will start as 0 and increment as more columns are sorted.

For example, you can access the first sorted column as follows.

$EventData.Sort.'0'.field

You will also receive the sort direction for each column.

Detailed Content

You can use the -LoadDetailedContent event handler to display additional information about the row you are expanding. Information about the current row is available in $EventData.row.

New-UDDataGrid -LoadRows {
    $Data = @(
        @{ Name = 'Adam'; Number = Get-Random }
        @{ Name = 'Tom'; Number = Get-Random }
        @{ Name = 'Sarah'; Number = Get-Random }
    )
    @{
        rows     = $Data 
        rowCount = $Data.Length
    }
} -Columns @(
    @{ field = "Name" }
    @{ field = "number" }
) -AutoHeight -LoadDetailContent {
    Show-UDToast $Body
    New-UDAlert -Text $EventData.row.Name
}

Editing

Tables provide editor support by specifying the -OnEdit event handler. The new row data will be provided as $EventData. You can chose to return updated row information (for example, adjusting something the user has entered) and return it from the event handler. If you do not return anything, the row will reflect what the user entered.

The $EventData has the following format.

@{
    newRow = @{}
    oldRow = @{}
}

Ensure that you provide the editable property to each column you wish for the user to edit.

New-UDDataGrid -LoadRows {
    $Data = @(
        @{ Name = 'Adam'; number = Get-Random }
        @{ Name = 'Tom'; number = Get-Random }
        @{ Name = 'Sarah'; number = Get-Random }
    )
    @{
        rows     = $Data 
        rowCount = $Data.Length
    }
} -Columns @(
    @{ field = "Name"; editable = $true }
    @{ field = "number" ; editable = $true }
) -AutoHeight -OnEdit {
    Show-UDToast "Editing $Body" 
}

Custom Export

To override the default export functionality, use the -OnExport event handler. $EventData will be an array of rows with their values. You should use Out-UDDataGridExport to return the data from -OnExport.

New-UDDataGrid -LoadRows {
    $Data = @(
        @{ Name = 'Adam'; Number = Get-Random}
        @{ Name = 'Tom'; Number = Get-Random}
        @{ Name = 'Sarah'; Number = Get-Random}
    )
    @{
        rows = $Data 
        rowCount = $Data.Length
    }
} -Columns @(
    @{ field = "name"}
    @{ field = "number"}
) -AutoHeight -OnExport {
    $Data = $EventData | Select-Object -Expand name 
    Out-UDDataGridExport -Data $Data -FileName 'export.txt' | Out-String
}

Example: Static Data

In this example, we generate an array of 10,000 records. We will create a new function, Out-UDDataGridData to manage the paging, sorting and filtering.

New-UDDashboard -Title 'PowerShell Universal' -Content {
     $Data =  1..10000 | % {
        @{ Name = 'Adam'; Number = Get-Random }
    } 
    New-UDDataGrid -LoadRows {  
      $Data | Out-UDDataGridData -Context $EventData
    } -Columns @(
        @{ field = "name"; render = { 
            New-UDButton -Icon (New-UDIcon -Icon User) -OnClick { Show-UDToast $EventData.Name } } 
        }
        @{ field = "number" }
    ) -AutoHeight -Pagination
}

Example: SQL Data

In this example, we'll query the PowerShell Universal database with dbatools.

function Out-UDSQLDataGrid {
    param(
        [Parameter(Mandatory)]
        $Context,
        [Parameter(Mandatory)]
        [string]$Table,
        [Parameter(Mandatory)]
        [string]$SqlInstance,
        [Parameter(Mandatory)]
        [string]$Database
    )
    End {
        $simpleFilter = @()
        if($null -ne $Context.Filter.Items -and $Context.Filter.Items.Count -gt 0) {
            $linkOperator = $Context.Filter.linkOperator #The link operator is 'AND' or 'OR'. It will always be one or the other for all properties
            foreach ($item in $Context.Filter.Items) {         
                $simpleFilter += [PSCustomObject]@{
                    Property    = $item.columnField
                    Value       = $item.Value
                    Operator    = $item.operatorValue
                }
            }
        }

        if($null -ne $simpleFilter -and $simpleFilter.Count -gt 0) {
            $count = 1
            foreach($filter in $simpleFilter) {
                if ($count -gt 1) {
                    $SqlFilter += " $($linkOperator) "
                } else {
                    $SqlFilter += " WHERE "
                }
                switch ($filter.Operator) {
                    "contains" { $SqlFilter += " $($filter.Property) LIKE '%$($filter.Value)%' " }
                    "equals" { $SqlFilter += " $($filter.Property) = '$($filter.Value)' " }
                    "startsWith" { $SqlFilter += " $($filter.Property) LIKE '$($filter.Value)%' " }
                    "endsWith" { $SqlFilter += " $($filter.Property) LIKE '%$($filter.Value)' " }
                    "isAnyOf" {
                        $count = 1
                        foreach($val in $filter.Value){
                            if($count -gt 1) {
                                $list += ", '$val'"
                            } else {
                                $list += "'$val'"
                            }  
                            $count += 1
                        }
                        $SqlFilter += " $($filter.Property) IN ($($list)) "
                    }
                    "isempty" { $SqlFilter += " TRIM ($($filter.Property)) IS NULL " }
                    "isnotempty" { $SqlFilter += " TRIM ($($filter.Property)) IS NOT NULL " }
                    "notequals" { $SqlFilter += " $($filter.Property) != '$($filter.Value)' " }
                    "notcontains" { $SqlFilter += " $($filter.Property) NOT LIKE '%$($filter.Value)%' " }
                }
                $count += 1
            }
        } else {
            $SqlFilter = $null
        }
        $totalCount = (Invoke-DbaQuery -SqlInstance $SqlInstance -Database $Database -Query "SELECT COUNT(*) As Count FROM $Table $SqlFilter" -SqlParameters $SqlParameters).Count
        $sort = $Context.Sort.'0'
        if ($sort)
        {
            $sqlSort = "ORDER BY $($sort.field) $($sort.Sort) "
        } else {
            $sqlSort = "ORDER BY (SELECT NULL)"
        }
        $sqlPage = "OFFSET $($Context.Page * $Context.PageSize) ROWS FETCH NEXT $($Context.PageSize) ROWS ONLY;"
        if($null -ne $SqlFilter) {
            $Query = "SELECT * FROM $Table $sqlFilter $sqlSort $sqlPage"
        } else {
            $Query = "SELECT * FROM $Table $sqlSort $sqlPage"
        }
        $Rows = Invoke-DbaQuery -SqlInstance $SqlInstance -Database $Database -Query $Query -As PSObject -SqlParameters $SqlParameters
        @{
            rows     = [Array]$Rows
            rowCount = $TotalCount
        }
    }   
}

New-UDDashboard -Title 'PowerShell Universal' -Content {
    New-UDDataGrid -LoadRows {  
      Out-UDSqlDataGrid -Context $EventData -SqlInstance "(localdb)\MSSQLLocalDb" -Database "PSU" -Table "Job"
    } -Columns @(
        @{ field = "id"; }
        @{ field = "startTime"; }
        @{ field = "status"; render = {
            if ($EventData.Status -eq 2) {
                New-UDAlert -Severity 'Success' -Text 'Success'
            }

            if ($EventData.Status -eq 3) {
                New-UDAlert -Severity 'Error' -Text 'Failed'
            }
        } }
    ) -AutoHeight -Pagination
}

Data Grid

Data grid component for Universal Dashboard.

Simple Data Grid

Data grids load their data via the -LoadRows event handler. You will need to return a hashtable that contains the row data and the total number of rows.

Columns are defined using hashtables.

Prior to version 3.3., column field names must be in camel case. For example, the property Name would need to be name while the property FirstName would need to be firstName.

New-UDDataGrid -LoadRows {
    $Data = @(
        @{ Name = 'Adam'; Number = Get-Random}
        @{ Name = 'Tom'; Number = Get-Random}
        @{ Name = 'Sarah'; Number = Get-Random}
    )
    @{
        rows = $Data 
        rowCount = $Data.Length
    }
} -Columns @(
    @{ field = "name"}
    @{ field = "number"}
) -AutoHeight

Columns

Columns are customizable using hashtables. You can find the supported properties below.

Property

Description

Type\Value

Rendering Custom Columns

You can render custom components in columns by specifying render within the column hashtable. You can access the current row's data by using the $EventData or $Row variable

In this example, the number is shown in the name column with a New-UDTypography component.

New-UDDataGrid -LoadRows {  
    $Rows = 1..100 | % {
        @{ Name = 'Adam'; Number = Get-Random}
    }
    @{
        rows = $Rows
        rowCount = $Rows.Length
    }
    
} -Columns @(
    @{ field = "name"; render = { New-UDTypography $EventData.number }}
    @{ field = "number"}
) -AutoHeight

Flexible Width Columns

Column fluidity or responsiveness can be achieved by setting the flex property of a column.

The flex property accepts a value between 0 and ∞. It works by dividing the remaining space in the grid among all flex columns in proportion to their flex value.

To set a minimum and maximum width for a flex column set the minWidth and the maxWidth property on the column.

New-UDDataGrid -LoadRows {  
    $Rows = 1..100 | % {
        @{ Name = 'Adam'; Number = "This column is a very long string. This column is a very long string. This column is a very long string. This column is a very long string. This column is a very long string. This column is a very long string."}
    }
    @{
        rows = $Rows
        rowCount = $Rows.Length
    }
    
} -Columns @(
    @{ field = "name"; render = { New-UDTypography $EventData.number }}
    @{ field = "number"; flex = 1.0}
) -AutoHeight

LoadRows

Property

Description

Type

Paging

To implement paging, you can access the page and pageSize properties of the $EventData variable.

New-UDDataGrid -LoadRows {  
    $Rows = 1..100 | % {
        @{ Name = 'Adam'; Number = Get-Random}
    } 
    @{
        rows = $Rows | Select-Object -First $EventData.pageSize -Skip ($EventData.page * $EventData.pageSize)
        rowCount = $Rows.Length
    }
    
} -Columns @(
    @{ field = "name"; }
    @{ field = "number"}
) -AutoHeight -Pagination

Filtering

The filter hashtable is included in the $EventData for the -LoadRows event handler when a filter is defined. The hashtable has a structure as follows.

@{
    items = @(
        @{ 
            columnField = "Name"
            overatorValue = "contains"
            value = "test"
        }
    )
    linkOperator = "and"
}

Items

The items property contains an array of columns, operators and values. You can use these to filter your data.

Property

Description

Type

LinkOperator

The link operator field is used to specify the link between the filters. This can be and or or.

Sorting

For example, you can access the first sorted column as follows.

$EventData.Sort.'0'.field

You will also receive the sort direction for each column.

Property

Description

Type

Detailed Content

You can use the -LoadDetailedContent event handler to display additional information about the row you are expanding. Information about the current row is available in $EventData.row.

New-UDDataGrid -LoadRows {
    $Data = @(
        @{ Name = 'Adam'; Number = Get-Random }
        @{ Name = 'Tom'; Number = Get-Random }
        @{ Name = 'Sarah'; Number = Get-Random }
    )
    @{
        rows     = $Data 
        rowCount = $Data.Length
    }
} -Columns @(
    @{ field = "Name" }
    @{ field = "number" }
) -AutoHeight -LoadDetailContent {
    Show-UDToast $Body
    New-UDAlert -Text $EventData.row.Name
}

Editing

The $EventData has the following format.

@{
    newRow = @{}
    oldRow = @{}
}

Ensure that you provide the editable property to each column you wish for the user to edit.

New-UDDataGrid -LoadRows {
    $Data = @(
        @{ Name = 'Adam'; number = Get-Random }
        @{ Name = 'Tom'; number = Get-Random }
        @{ Name = 'Sarah'; number = Get-Random }
    )
    @{
        rows     = $Data 
        rowCount = $Data.Length
    }
} -Columns @(
    @{ field = "Name"; editable = $true }
    @{ field = "number" ; editable = $true }
) -AutoHeight -OnEdit {
    Show-UDToast "Editing $Body" 
}

Custom Export

New-UDDataGrid -LoadRows {
    $Data = @(
        @{ Name = 'Adam'; Number = Get-Random}
        @{ Name = 'Tom'; Number = Get-Random}
        @{ Name = 'Sarah'; Number = Get-Random}
    )
    @{
        rows = $Data 
        rowCount = $Data.Length
    }
} -Columns @(
    @{ field = "name"}
    @{ field = "number"}
) -AutoHeight -OnExport {
    $Data = $EventData | Select-Object -Expand name 
    Out-UDDataGridExport -Data $Data -FileName 'export.txt' | Out-String
}

Example: Static Data

In this example, we generate an array of 10,000 records. We will create a new function, Out-UDDataGridData to manage the paging, sorting and filtering.

New-UDDashboard -Title 'PowerShell Universal' -Content {
     $Data =  1..10000 | % {
        @{ Name = 'Adam'; Number = Get-Random }
    } 
    New-UDDataGrid -LoadRows {  
      $Data | Out-UDDataGridData -Context $EventData
    } -Columns @(
        @{ field = "name"; render = { 
            New-UDButton -Icon (New-UDIcon -Icon User) -OnClick { Show-UDToast $EventData.Name } } 
        }
        @{ field = "number" }
    ) -AutoHeight -Pagination
}

Example: SQL Data

In this example, we'll query the PowerShell Universal database with dbatools.

function Out-UDSQLDataGrid {
    param(
        [Parameter(Mandatory)]
        $Context,
        [Parameter(Mandatory)]
        [string]$Table,
        [Parameter(Mandatory)]
        [string]$SqlInstance,
        [Parameter(Mandatory)]
        [string]$Database
    )
    End {
        $simpleFilter = @()
        if($null -ne $Context.Filter.Items -and $Context.Filter.Items.Count -gt 0) {
            $linkOperator = $Context.Filter.linkOperator #The link operator is 'AND' or 'OR'. It will always be one or the other for all properties
            foreach ($item in $Context.Filter.Items) {         
                $simpleFilter += [PSCustomObject]@{
                    Property    = $item.columnField
                    Value       = $item.Value
                    Operator    = $item.operatorValue
                }
            }
        }

        if($null -ne $simpleFilter -and $simpleFilter.Count -gt 0) {
            $count = 1
            foreach($filter in $simpleFilter) {
                if ($count -gt 1) {
                    $SqlFilter += " $($linkOperator) "
                } else {
                    $SqlFilter += " WHERE "
                }
                switch ($filter.Operator) {
                    "contains" { $SqlFilter += " $($filter.Property) LIKE '%$($filter.Value)%' " }
                    "equals" { $SqlFilter += " $($filter.Property) = '$($filter.Value)' " }
                    "startsWith" { $SqlFilter += " $($filter.Property) LIKE '$($filter.Value)%' " }
                    "endsWith" { $SqlFilter += " $($filter.Property) LIKE '%$($filter.Value)' " }
                    "isAnyOf" {
                        $count = 1
                        foreach($val in $filter.Value){
                            if($count -gt 1) {
                                $list += ", '$val'"
                            } else {
                                $list += "'$val'"
                            }  
                            $count += 1
                        }
                        $SqlFilter += " $($filter.Property) IN ($($list)) "
                    }
                    "isempty" { $SqlFilter += " TRIM ($($filter.Property)) IS NULL " }
                    "isnotempty" { $SqlFilter += " TRIM ($($filter.Property)) IS NOT NULL " }
                    "notequals" { $SqlFilter += " $($filter.Property) != '$($filter.Value)' " }
                    "notcontains" { $SqlFilter += " $($filter.Property) NOT LIKE '%$($filter.Value)%' " }
                }
                $count += 1
            }
        } else {
            $SqlFilter = $null
        }
        $totalCount = (Invoke-DbaQuery -SqlInstance $SqlInstance -Database $Database -Query "SELECT COUNT(*) As Count FROM $Table $SqlFilter" -SqlParameters $SqlParameters).Count
        $sort = $Context.Sort.'0'
        if ($sort)
        {
            $sqlSort = "ORDER BY $($sort.field) $($sort.Sort) "
        } else {
            $sqlSort = "ORDER BY (SELECT NULL)"
        }
        $sqlPage = "OFFSET $($Context.Page * $Context.PageSize) ROWS FETCH NEXT $($Context.PageSize) ROWS ONLY;"
        if($null -ne $SqlFilter) {
            $Query = "SELECT * FROM $Table $sqlFilter $sqlSort $sqlPage"
        } else {
            $Query = "SELECT * FROM $Table $sqlSort $sqlPage"
        }
        $Rows = Invoke-DbaQuery -SqlInstance $SqlInstance -Database $Database -Query $Query -As PSObject -SqlParameters $SqlParameters
        @{
            rows     = [Array]$Rows
            rowCount = $TotalCount
        }
    }   
}

New-UDDashboard -Title 'PowerShell Universal' -Content {
    New-UDDataGrid -LoadRows {  
      Out-UDSqlDataGrid -Context $EventData -SqlInstance "(localdb)\MSSQLLocalDb" -Database "PSU" -Table "Job"
    } -Columns @(
        @{ field = "id"; }
        @{ field = "startTime"; }
        @{ field = "status"; render = {
            if ($EventData.Status -eq 2) {
                New-UDAlert -Severity 'Success' -Text 'Success'
            }

            if ($EventData.Status -eq 3) {
                New-UDAlert -Severity 'Error' -Text 'Failed'
            }
        } }
    ) -AutoHeight -Pagination
}