Good way to handle data with lots of columns in a Grid/Table?

jagrock · December 27, 2019, 12:45am

I’m looking for direction in handling data with lots of columns. The column headers go past the edge of that card and does not look uniform at all. I’d like it to stay in the card and give scroll bars horizonatally so it doesn’t leave the card edge.

I can get the horizontal scroll bar by using New-UDElement to create a div with overflow-x set to auto. The scroll bar stays within the container card but scrolling goes past the edge of the container, thus giving unexpected results. I also noticed that the background color for both Grid and Table apply to parent div of those not the actual div of the table/grid. I tried using it to give a solid color background to see if that helped, but it didn’t.

Updated examples screenshots. This also show where the background style is being set on the containing div and not the grid/table itself.

Code used to generate this:
(Updated with nesting under New-UDElement creating a DIV with overflow-x - no screenshots for this but you can see the color differences on the color of the table/grid vs the color of the div)

Get-UDDashboard | Stop-UDDashboard

$Dashboard = New-UDDashboard -Content {

    $cols = 50
    $rows = 3

    $i = 0
    $cache:LargeDataset = [System.Collections.ArrayList]::new()

    do {

        $i++
        $tmpHash = [ordered]@{ }
        foreach ($col in 1..$cols) {
            $tmpHash."col$col" = "r$i`:c$col"
        }

        [void]$cache:LargeDataset.Add( ([PSCustomObject]$tmpHash) )

    } until ($i -eq $rows)

    New-UDGrid -Id "UDGridWithLargeDataSet" -Title "UDGrid with Large Dataset" -BackgroundColor "red" -Endpoint {

        $cache:LargeDataset | Select-Object $_.psobject.properties.name | Out-UDGridData
    }

    New-UDTable -Id "UDTableWithLargeDataSet" -Title 'UDTable with Large Dataset' -BackgroundColor "green" -Headers $cache:LargeDataset[0].psobject.Properties.name -Endpoint {

        $cache:LargeDataset | Out-UDTableData -Property $cache:LargeDataset[0].psobject.Properties.name

    }

    $divAttrs = @{
        style = @{
            'overflow-x'       = "auto"
            width              = "100%"
            'background-color' = 'blue'
        }
    }

    New-UDElement -Id 'tablePlaceholder' -Tag div -Attributes $divAttrs -Content {
        New-UDTable -Id "UDTableNested" -Title 'UDTable Nested in New-UDElement DIV' -BackgroundColor "green" -Headers $cache:LargeDataset[0].psobject.Properties.name -Endpoint {

            $cache:LargeDataset | Out-UDTableData -Property $cache:LargeDataset[0].psobject.Properties.name

        }
    }

    New-UDElement -Id 'gridplaceholder' -Tag div -Attributes $divAttrs -Content {
        New-UDGrid -Id "UDGridNested" -Title "UDGrid Nested in New-UDElement DIV" -BackgroundColor "red" -Endpoint {

            $cache:LargeDataset | Select-Object $_.psobject.properties.name | Out-UDGridData
        }
    }
}

Start-UDDashboard -Name 'Large DataSet Handling' -Dashboard $Dashboard -Port 10000 -AllowHttpForLogin

dennisbuswell · December 27, 2019, 1:34am

I’m still new to UD and don’t have a technical answer but I would challenge you to think about what you are trying to convey with the data you are presenting in a dashboard. Perhaps the data can be reduced or broken up in a different format.

V/r,

Dennis

jagrock · December 27, 2019, 3:57am

Yeah, i wish that was easily possible. This isn’t going to be a dashboard but a form for a User Access Review (UAR) and really needs to be plug and play for over 60 different controls, each having a minimum set of data to show the reviewer.

If we had time to build out a page for each review, that could be possible but time is something we don’t have for this at the moment. Changing the underlying data isn’t an option as this is just an overlay to a SharePoint list (now that the AAD token can be passed through, graph api calls will be made to make the updates to the data to the SharePoint list, thus showing the logged in user as the person making the change. This keeps the auditors happy and not wanting a full on SDLC set of controls for this app usage.

jagrock · December 27, 2019, 11:41pm

So I was able to get close to what I am looking for by adding overlow-x: auto via the browsers dev tools to the udtable/grid div container.

jagrock · December 28, 2019, 2:09am

Ok, was able to get something passable for both UD-Card and UD-Table using the below.

New-UDGrid -Id "UDGridWithLargeDataSet" -Title "UDGrid with Large Dataset" -BackgroundColor "red" -PageSize 5 -NoExport -Endpoint {

    $cache:LargeDataset | Select-Object $_.psobject.properties.name | Out-UDGridData
    Invoke-UDJavaScript -JavaScript "
        var checkExist = setInterval(function () {
        if (document.getElementById('UDGridWithLargeDataSet').querySelector('table.griddle-table') != null) {
            document.getElementById('UDGridWithLargeDataSet').querySelector('table.griddle-table').parentNode.style.overflowX = 'auto'
            clearInterval(checkExist);
        }
    }, 100);
    "

}



New-UDTable -Id "UDTableWithLargeDataSet" -Title 'UDTable with Large Dataset' -BackgroundColor "green" -Headers $cache:LargeDataset[0].psobject.Properties.name -Endpoint {

    Invoke-UDJavaScript -JavaScript "
        var child = document.getElementById('UDTableWithLargeDataSet').querySelector('table');
        var parent = child.parentNode;
        var wrapper = document.createElement('div');
        wrapper.style.overflowX = 'auto';
        parent.replaceChild(wrapper, child);
        wrapper.appendChild(child);
    "
    $cache:LargeDataset | Out-UDTableData -Property $cache:LargeDataset[0].psobject.Properties.name

}

mkellerman · January 3, 2020, 6:04pm

Can you post a screenshot of what it looks like with your changes? I’m curious… I have the same problem…

Alc · January 6, 2020, 2:33pm

UDGrid is a little lacking in features tbh. @BoSen29 did start to look at an alternative in this thread New-UDgrid filtering and grouping but not sure how far he managed to get with it.

psDevUK · January 6, 2020, 2:43pm

Hi @Alc grids are a lot more to develop…hence I have not done any myself, as I believe you need to know a fair bit of javascript to accomplish the task. I myself would like to do a pivot table component, as that is what people at my work are used to…however it’s not a full solution, but at the moment, I am using my UD Selector component to allow the end user to select one or more options, I then display 2 grids, one summerised and one detailed…it’s not perfect but they can then export to excel…which is the stage I would like to eliminate.

BoSen29 · January 6, 2020, 2:46pm

Yeah, @Alc
I’m currently in the progress of procrastinating on this.
I’ve been building a couple of other components in order to advance my javascript knowledge before i tackle this beast of a project.

I’ve scheduled the grid component to be my next, after i finish up this modal thingy i’m working on

Alc · January 6, 2020, 3:08pm

Oh I do not underestimate how difficult it would be! Just wanted to let the OP know that it is possible something may be along in the future. It’s certainly not something I could achieve so massive respect to anyone to even tries it

jagrock · January 9, 2020, 5:45pm

The code is below. You can adjust the $rows and $cols vars at the top to adjust the size of the example dataset.

I’m also looking at building the two below compents out as well, but not sure if it will be generic or specific to meet our UAR needs. The first one is in the youtube example, but it’s a little outdated. Figured out the Plaster to build the template files. Also, some of the way you set state or constructor is a little different in the template than in the video but was able to figure it out (for the most part).

https://adazzle.github.io/react-data-grid/docs/examples/row-selection

https://autodesk.github.io/react-base-table/examples/default

    Get-UDDashboard | Stop-UDDashboard

$Dashboard = New-UDDashboard -Content {

    $cols = 50
    $rows = 10

    $i = 0
    $cache:LargeDataset = [System.Collections.ArrayList]::new()

    do {

        $i++
        $tmpHash = [ordered]@{ }
        foreach ($col in 1..$cols) {
            $tmpHash."col$col" = "r$i`:c$col"
        }

        [void]$cache:LargeDataset.Add( ([PSCustomObject]$tmpHash) )

    } until ($i -eq $rows)

    New-UDGrid -Title "UDGrid - Default" -BackgroundColor "red" -PageSize 5 -NoExport -Endpoint {

        $cache:LargeDataset | Select-Object $_.psobject.properties.name | Out-UDGridData

    }

    New-UDGrid -Id "UDGridWithLargeDataSet" -Title "UDGrid - JavaScript Set Overflow" -BackgroundColor "red" -PageSize 5 -NoExport -Endpoint {

        $cache:LargeDataset | Select-Object $_.psobject.properties.name | Out-UDGridData
        Invoke-UDJavaScript -JavaScript "
                var checkExist = setInterval(function () {
                if (document.getElementById('UDGridWithLargeDataSet').querySelector('table.griddle-table') != null) {
                    document.getElementById('UDGridWithLargeDataSet').querySelector('table.griddle-table').parentNode.style.overflowX = 'auto'
                    clearInterval(checkExist);
                }
            }, 100);
            "

    }

    New-UDTable -Title 'UDTable - Default' -BackgroundColor "green" -Headers $cache:LargeDataset[0].psobject.Properties.name -Endpoint {

        $cache:LargeDataset | Out-UDTableData -Property $cache:LargeDataset[0].psobject.Properties.name

    }

    New-UDTable -Id "UDTableWithLargeDataSet" -Title 'UDTable - JS Example 1' -BackgroundColor "green" -Headers $cache:LargeDataset[0].psobject.Properties.name -Endpoint {

        Invoke-UDJavaScript -JavaScript "
                var child = document.getElementById('UDTableWithLargeDataSet').querySelector('table');
                var parent = child.parentNode;
                var wrapper = document.createElement('div');
                wrapper.style.overflowX = 'auto';
                parent.replaceChild(wrapper, child);
                wrapper.appendChild(child);
            "
        $cache:LargeDataset | Out-UDTableData -Property $cache:LargeDataset[0].psobject.Properties.name

    }

    New-UDTable -Id "UDTableWithLargeDataSet2" -Title 'UDTable - JS Example 2' -BackgroundColor "green" -Headers $cache:LargeDataset[0].psobject.Properties.name -Endpoint {

        Invoke-UDJavaScript -JavaScript "
                var checkExist = setInterval(function () {
                    if (document.getElementById('UDTableWithLargeDataSet2').querySelector('table') != null) {
                        var child = document.getElementById('UDTableWithLargeDataSet2').querySelector('table');
                        var parent = child.parentNode;
                        var wrapper = document.createElement('div');
                        wrapper.style.overflowX = 'auto';
                        parent.replaceChild(wrapper, child);
                        wrapper.appendChild(child);
                        clearInterval(checkExist);
                    }
                }, 100);
            "
        $cache:LargeDataset | Out-UDTableData -Property $cache:LargeDataset[0].psobject.Properties.name

    }
}

Start-UDDashboard -Name 'Large DataSet Handling' -Dashboard $Dashboard -Port 10000 -AllowHttpForLogin

psDevUK · January 16, 2020, 9:18pm

So I am not saying I have solved the problem…but tonight I managed to build another custom component a pivot-table component…I am sure displaying the data in this would drastically reduce the size of the grid, as thats what pivot tables are great at doing…hoping to get this released soon:-

augustin.ziegler · January 16, 2020, 10:04pm

@psDevUK

Looks so great …very excited to see more of it …

Alc · January 17, 2020, 9:19am

Oooh that is very nice!

McAndersDK · January 17, 2020, 12:19pm

@jagrock you could quickly cut down on the count of column, by doing a grid pr Type of Column ?

I guess they have somekind of type associated to them?

I also think this could make it alot easyer for the reviewer to gasp what is going on, instead of a very wide grid.

jagrock · January 17, 2020, 9:32pm

@McAndersDK here may be and looking at that, but it wont be enough to get into the confines of how the grids work OOTB currently. The problem is, this has to be very dynamic across a wide range of data (60+ apps each with their own unique data export). This will simply be a way to give the end users a better UX experience than the current web part page with the list embedded.

@psDevUK - that looks pretty cool and already thinking about many ways i could use this. I don’t know if it will solve my use case as the end user will be making a choice/action on each item that will pass back to a sharepoint list. Giving them free range to change the order and layout may also lead to further confusion for them, especially if they can impact where the checkbox ends up.

psDevUK · January 17, 2020, 9:36pm

Was using this today on every-page which I had a grid on, so the user could then further analyze that grid but in a pivot fashion to which they are used to…If you gave your users only 3 items in the data hashtable then they simply put the DATE/TIME along the top and the DATA ones on the left hand side:-

Then they shouldnt be able to mess it up…although end-users

BTW this is my top 4 CPU processes

Thinking you could probably set the state with how you want it laid out…might look into this now…I am looking at releasing this soon…just trying to build up some hype first

sysbala · September 18, 2020, 8:40am

I am able to use you code snip to fix overlap issues. Thanks for the snip - it works well.

Topic		Replies	Views
Make long table scrollable in a udcard Universal Dashboard	0	246	May 11, 2023
Scrollable Table PowerShell Universal	1	314	September 23, 2022
UDGrid - Filter Data Posted with New-UDElement Universal Dashboard Help	1	518	March 4, 2020
New-UDColumn -Offset Universal Dashboard	3	418	November 11, 2021
Difficulty with the layout Universal Dashboard Help	1	771	June 19, 2019

Good way to handle data with lots of columns in a Grid/Table?

Related Topics