Author Posts

October 16, 2013 at 10:41 am

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"}

October 17, 2013 at 7:41 am

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

October 17, 2013 at 1:24 pm

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;)

November 24, 2013 at 11:54 am

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

October 21, 2014 at 6:37 am

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.