Hi All,
I’ve been trying to write a powershell script to grab data from a sharepoint list to:
- Group the items by the Title field
- Sum the Quantity field of the like items
For example, if the list has:
| Title | Quantity |
| Coke | 4 |
| Sprite | 3 |
| Sprite | 2 |
| Coke | 1 |
| Coke | 8 |
Then the Powershell script would output:
| Title | Quantity |
| Coke | 13 |
| Sprite | 5 |
Here’s my script:
$web = Get-SPWeb “https://intranet.oursite.com/”
$list =$web.GetList(“/sites/test/Lists/Order”)
$view = $list.Views[“ScriptTest”]
$exportlist = $null
$exportlist = @()
$items = $list.GetItems($view) | Group-Object $_[‘Title’]
$items | select-object -property @{Name=’Title’;expression={$_[‘Title’]}}, @{Name=’Quantity’;expression={($_Group | Measure-Object -Property $_[‘Quantity’] -Sum).Sum }}
The issue I’m having is that it doesn’t recognize the Quantity field so the output is empty.
Thanks in advance.
I got some help on the MSDN forums and here’s the final script in case anyone else needs it:
Â
$web = Get-SPWeb “https://intranet.oursite.com/”
$list =$web.GetList(“/sites/test/Lists/Order”)
$view = $list.Views[“ScriptTest”]
$exportlist = $null
$exportlist = @()
$items = $list.GetItems($view) | Group-Object Title
Â
$items | select @{name=“Title”; expression={$_.name}}, @{name=“Quantity”; expression={($_.Group | select @{name=“q”; expression={$_[‘ItemQuantity’]}}Â | measure q -sum).sum }}
The key is to not have the group by set in the view settings.
Â
