New DataGrid UDDataGrid - Losing $EventData.Filter on paging

PowerShell Universal
1 
Product: PowerShell Universal
Version: 3.2.6

Heyas, just using the example from Data Grid - PowerShell Universal and the filters added to the WHERE condition were coming in as Param0 and Param1 etc instead of the values added to the filters.

I changed the instances of:
$Property LIKE $Parameter
to
$Property LIKE ‘%$Value%’

This seems to be working now, column filtering. Not sure if that’s correct or not? Doc needs updating? Not sure.

But also - great now the filter works, I get 5 rows back (page size 5) from the database of 100,000+ rows, filtered ok - but then when I page to the next page, the query updates and sets the OFFSET/FETCH correctly but it loses the WHERE filter condition! :frowning: Anyone know how to keep that WHERE condition no matter which page the user clicks to?

Cheers!
Steve.

$SqlFilter = "WHERE "        
        $SqlParameters = @{}
        $Filter = $Context.Filter 
        foreach ($item in $Filter.Items) {
            $Property = $item.columnField
            $Value = $item.Value
            $Parameter = "Param" + $SqlParameters.Count
            
            if ($item.operatorValue -eq 'contains')
            {
                $SqlParameters.Add($Parameter, "%$Value%") | Out-Null
            } 
            else 
            {
                $SqlParameters.Add($Parameter, $Value) | Out-Null            
            }
            
            switch ($item.operatorValue) {
                "contains" { $SqlFilter += "$Property LIKE '%$Value%' AND " } 
                "equals" { $SqlFilter += "$Property = '$Value' AND " }  
                "isEmpty" { $SqlFilter += "$Property IS NULL "  }
                "isNotEmpty" { $SqlFilter += "$Property IS NOT NULL "  }
            }
        }

        $SQLFilter += " 1 = 1"
2

Hello @pharnos ,
same behavior here.
I opened an issue for this

1 Like
3

I reported the param issue to Adam and hopefully the docs get updated.
For the where issue I’ve been trying to figure this out too, ideally the grid needs to maintain it’s own state. But you can use a $cache: variable to maintain the where clause and this is passed between page loads.

1 Like
4

So, doc does need updated.

$Parameter = "Param" + $SqlParameters.Count

Needs to be…

$Parameter = "@Param" + $SqlParameters.Count

I’m seeing the same behavior on 3.2.6 though.

Some additional findings: when a filter is applied, and you go to the next page, it drops the filter from $Eventdata.

If you then remove the filter, it then sets page to 0. Meaning you could be on page 3, but it will load Page 1 data.

1 Like