Filtering items in SharePoint list

This topic contains 4 replies, has 3 voices, and was last updated by Profile photo of Venkatesam Miriyapalli Venkatesam Miriyapalli 2 years, 6 months ago.

  • Author
    Posts
  • #10795
    Profile photo of Joe Fecht
    Joe Fecht
    Participant

    I’m looking to filter items in SharePoint list based upon certain columns. We are running SharePoint 2010 on Server 2008 R2. I have a list that has roughly 25 user created columns I would like to be able to filter/pull data from that list based upon any column I want.

    I have accessed the list and can see all sorts of fun things about it. However piping the list to GM the only property I can see that is related to a column that I can filter in the list is the “Title” property which is not one of the columns that I want.

    I can see the Fields Property and get a list of those columns I want to filter by but can’t seem to piece together how to take that information to give me the content of the items.

    How to do I access the other columns to filter the list and the view the filtered list content?

    Here is how I am accessing the list.


    $site = get-spweb "http://batman"
    $list = $site.lists | where {$_.title -eq "HolyMackerelBatman"}

  • #10817
    Profile photo of Todd Klindt
    Todd Klindt
    Participant

    Unfortunately I've never found an easy way to do this with Custom Columns. 🙁

    I've only had to do this a couple of times, and the only way I could get reliable results was from the XML property of the SPListItem. There was some gnarly parsing required. I don't have it all handy, but here's the first 90%:

    $web = Get-SPWeb http://portal.contoso.com
    $list = $web.lists | Where-Object { $_.title -eq "CustomList" }
    $item = $list.Items[0]
    $blah = $item.Xml

    Now $blah has a big blob of XML in it. In the example I made my custom column was called Custom1. The value of Custom1 was stored under the ows_Custom1 field. I don't have the code handy that I used to parse through and pull all that out.

    Hope that helps.

    tk

  • #10877
    Profile photo of Joe Fecht
    Joe Fecht
    Participant

    Todd – Thanks for replying. I thought I would have to dig into XML to get it out but hadn't done it before and wanted to see if there was another trick.

    I think I got this figured out. Its an asset list in Sharepoint that has all of our Users and their associated devices among other details. I wanted to do was get the custom field of list that contains the user associated with the device and compare that with Active Directory to see if they were still enabled. If they User is in the asset list but not enabled in AD I want it to output to a CSV and give me a device and user.

    PowerShell actually makes working with XML pretty easy.

    Here is my code – Not overly pretty but it got the job done. Will probably update this with a few comments later before I finalize it.

    Import-Module ActiveDirectory
    $Web = get-spweb "http://Batman"
    $List = $web.lists | where-object {$_.title -eq "HolyMackerelBatman"}
    $Items = $List.Items
    foreach ($Item in $Items){
        [XML]$ItemXML = $Item.xml 
        $User = $ItemXML.Row | Select -Expand ows_batman_x0020_user -ErrorAction SilentlyContinue
        $Device = $ItemXML.Row | Select -ExpandProperty ows_batman_x0020_Depreciation_x0020_L -ErrorAction SilentlyContinue
        if (($User) -and ($User -notlike "*Joker*")) {
            $UserName = $User.remove(0,4)
            $GothamResident = $UserName.replace("#","")
            $Enabled = Get-ADUser -Filter {Name -eq $GothamResident} | select -ExpandProperty Enabled
            if ($Enabled -EQ $false){
                $GothamResident + "," + $Device | Out-File C:\users\fechtj\desktop\asset-list.csv -Append 
            }
        }
    }
    

    BTW – Go Hawks;)

  • #11652
    Profile photo of Todd Klindt
    Todd Klindt
    Participant

    I wanted to follow up on this. One of my customers needed to do this so I had to dig in to it. There is a better way to do this. Give this a try:


    $Web = get-spweb "http://Batman"
    $List = $web.lists | where-object {$_.title -eq "HolyMackerelBatman"}
    $Items = $List.Items
    $Items | Where-Object {{ $_["CustomColumnName"] -NotLike "*joker*"} | Do-Something

    That loops through your list items, compares the value of each item's custom column named "CustomColumnName" and returns the ones that are not like "*joker*". Should be easier than the XML route.

    tk

  • #19917

    If you are using large SharePoint lists and wanted to update one item based on your selected ID

    #Check for existing item
    # use
    $SPItem = $list.[b]GetItemById[/b]($row.ID)
    # instead of
    # SPItem = $list.Items | Where { $_.ID -eq $row.ID }

    Hope this will be helpful for someone out there.

You must be logged in to reply to this topic.