Constructing a Where statement with variables

This topic contains 13 replies, has 4 voices, and was last updated by Profile photo of Scott Windmiller Scott Windmiller 6 months, 3 weeks ago.

  • Author
    Posts
  • #61093
    Profile photo of Scott Windmiller
    Scott Windmiller
    Participant

    I have this statement:

    $tracks | ? { ($_.Year -like "1986") } | Select year
    Year
    ----
    1986
    

    and was able to add:

    $tracks | ? { ($_.Year -like "1986" -or $_.Year -like '2003') } | Select year
    Year
    ----
    2003
    1986
    2003
    2003
    2003
    2003

    but I want to be able to select years from checkboxes in a gui and have them append to that statement if checked like:

    If ($checkbox1.Checked -eq $true)
    	{
    		$next += "-or $_.Year -like '2003'"
    	}
    $tracks | ? { ($_.Year -like "1986" $next) } | Select year

    Can this be done like this so I can select multiple years and just add the -or section to the statement?

    Thanks,
    Scott

  • #61096
    Profile photo of Don Jones
    Don Jones
    Keymaster

    You can do it like that. It'd probably be easier to use -in, though.

    $target_year -in @($choice_one, $choice_two, $choice_three)
    

    Since your GUI will probably make it easy to get a collection of selected values, that can just be the second operand the right side of -in.

  • #61105
    Profile photo of Scott Windmiller
    Scott Windmiller
    Participant

    Thanks.

    When I tried it to test like this:

    $next = "-or $_.Year -like '2003'"
    $tracks | ? { ($_.Year -like "1986" $next) } | Select year

    I get:

    At line:2 char:37
    + $tracks | ? { ($_.Year -like "1986" $next) } | Select year
    +                                     ~~~~~
    Unexpected token '$next' in expression or statement.
    At line:2 char:36
    + $tracks | ? { ($_.Year -like "1986" $next) } | Select year
    +                                    ~
    Missing closing ')' in expression.
    At line:2 char:13
    + $tracks | ? { ($_.Year -like "1986" $next) } | Select year
    +             ~
    Missing closing '}' in statement block or type definition.
    At line:2 char:42
    + $tracks | ? { ($_.Year -like "1986" $next) } | Select year
    +                                          ~
    Unexpected token ')' in expression or statement.
    At line:2 char:44
    + $tracks | ? { ($_.Year -like "1986" $next) } | Select year
    +                                            ~
    Unexpected token '}' in expression or statement.
    At line:2 char:46
    + $tracks | ? { ($_.Year -like "1986" $next) } | Select year
    +                                              ~
    An empty pipe element is not allowed.
        + CategoryInfo          : ParserError: (:) [], ParentContainsErrorRecordException
        + FullyQualifiedErrorId : UnexpectedToken

    Can you tell me whats wrong with my syntax on this? and would it be a problem if $next was blank...meaning nothing was selected.

    Thanks for your help!
    Scott

  • #61108
    Profile photo of Don Jones
    Don Jones
    Keymaster

    Ah, OK. I misunderstood what you were attempting.

    $next is illegal in that context. PowerShell won't evaluate that as code in the way you're thinking, no. You'd have to put your entire comparison into a variable and then use Invoke-Expression – which is also vulnerable to a number of different injection attacks if any of your data is coming from external sources.

  • #61110
    Profile photo of Scott Windmiller
    Scott Windmiller
    Participant

    OK thanks. All of this is run internally so that should not be a problem. Could you give me a example of what you mean by chance? I have a better time understanding it if I see an example.

    Thanks,
    Scott

  • #61113
    Profile photo of Don Jones
    Don Jones
    Keymaster

    Sure, just put your ENTIRE comparison in a string.

    $expression = "$tracks | where { $year -like '1' -or $year -like '2' -or $year -like '3' }"
    Invoke-Expression $expression

    What comes out should be the results you want.

  • #61116
    Profile photo of Daniel Krebs
    Daniel Krebs
    Moderator

    To use Don's earlier suggestion. If you would use a listbox enabled for multi-selection in your GUI instead of checkboxes for the years. It would be very easy to implement the filter without using Invoke-Expression which can become dangerous if used incorrectly.

    Example code:

    # Dummy source data converted into an array of objects
    $tracks = @'
    Name,Year
    Space Oddity,1969
    Heroes,1977
    Under Pressure,1982
    Lazarus,2016
    '@ | ConvertFrom-Csv
    
    Add-Type -AssemblyName System.Windows.Forms
    
    $listbox = New-Object -TypeName System.Windows.Forms.ListBox
    $listbox.SelectionMode = [System.Windows.Forms.SelectionMode]::MultiSimple
    
    # Add some items to our listbox
    $tracks.Year | ForEach-Object {
        [void]($listbox.Items.Add($_))
    }
    
    # Select a couple of items because this example doesn't have a UI
    $listbox.SetSelected(0, $true)
    $listbox.SetSelected(3, $true)
    
    # Actual code to filter the tracks
    $tracks | Where-Object { $_.Year -in $listbox.SelectedItems }
    

    I hope that helps.

  • #61117
    Profile photo of Scott Windmiller
    Scott Windmiller
    Participant

    Thanks for all your help, reading the the docs for Invoke-Expression does seem to be exactly what I need but it does not seem to be reading $tracks.

    $express = "$tracks | where { $_.Year -like '1986' }"
    Invoke-Expression $express
    Invoke-Expression : At line:1 char:8
    +        | where { .Year -like '1986' }
    +        ~
    An empty pipe element is not allowed.
    At line:2 char:1
    + Invoke-Expression $express
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : ParserError: (:) [Invoke-Expression], ParseException
        + FullyQualifiedErrorId : EmptyPipeElement,Microsoft.PowerShell.Commands.InvokeExpressionCommand
    

    Do I have to pass $tracks somehow?

    Viewing the $express variable just shows:

    $express
           | where { .Year -like '1986' }

    Viewing $tracks does show content so I am not sure what I am doing wrong.

    EDIT: Thanks Daniel Krebs, I think I was typing as the same time as you were responding. I will try that out but would still like to know what I did wrong above just so I know 🙂

    Thanks,
    Scott

  • #61119
    Profile photo of Daniel Krebs
    Daniel Krebs
    Moderator

    The dollar-signs in Don's Invoke-Expression example need to be escaped with a backtick to avoid early variable expansion into the $express string.

    # Just some example data
    $tracks = @'
    Name,Year
    Space Oddity,1969
    Heroes,1977
    Under Pressure,1982
    Lazarus,2016
    '@ | ConvertFrom-Csv
    
    $express = "`$tracks | where { `$_.Year -like '1969' -or `$_.Year -like '2016' }"
    Invoke-Expression $express
    
  • #61128
    Profile photo of Scott Windmiller
    Scott Windmiller
    Participant

    Got it!!! Thanks.

    Now to see if I can get the Listbox idea working.

    Thanks!

  • #61258
    Profile photo of Scott Windmiller
    Scott Windmiller
    Participant

    I got it working both ways just to make sure I understood both methods. Thanks to both of you for all the help!!
    I went with the Listbox method which you are right is so much easier, I over complicated it a bit I think..lol
    I do have another related question though. I want to be able to pick from a list of Genres and Years (2 separate Listboxes) and have it output whatever matches. I was able to get it to populate the list of Genres automatically but wanted to use a range for the years so I just put them in manually. What I have below works just fine but only if you select at least one entry from each of the 2 Listboxes. I don't know how to tell it to ignore that part of the statement if nothing is selected:

    	$FilteredYears = @()
    	If ($listboxYears.SelectedItems -eq "1980s")
    	{
    		$FilteredYears += (1980..1989)
    	}
    	If ($listboxYears.SelectedItems -eq "1990s")
    	{
    		$FilteredYears += (1990..1999)
    	}
    	If ($listboxYears.SelectedItems -eq "2000s")
    	{
    		$FilteredYears += (2000..2009)
    	}
    	If ($listboxYears.SelectedItems -eq "2010s")
    	{
    		$FilteredYears += (2010..2019)
    	}
    	
    	$Filtered = $tracks | Where-Object { ($_.Genre -in $listboxGenres.SelectedItems) -and ($_.Year -in $FilteredYears) }

    Thanks,
    Scott

  • #61263
    Profile photo of Dan Potter
    Dan Potter
    Participant

    🙂 you're still working on this?

    You validate both controls and build your query based on that. Use the api's built in search.

    $genre = $listbox1.selecteditem
    $songs = $libraryplaylist.search($genre, $this.ITPlaylistSearchFieldall)

    if($listbox2.selectedindex -gt 0){

    #filter all $songs by year selected

    }

  • #61266
    Profile photo of Dan Potter
    Dan Potter
    Participant

    Unfortunately the search doesn't include 'ALL' fields as the directions say. I tried it by year, no dice 🙁

  • #61281
    Profile photo of Scott Windmiller
    Scott Windmiller
    Participant

    LOL...yeah I am still working on it and its going great. I am using this to learn more as I am still very new to PS.

    So basically I would create a list of $songs from the Genre selections then use that filtered list to list the year(s) that match in that filter list, right? That makes sense.
    I was thinking I had to have it done all at once.

    Thanks,
    Scott

You must be logged in to reply to this topic.