How to get list view of items plus their linked items in powershell-extensions?

Question

I have a list of items /{path}/ORG[1-n] which each use a multilist field "Countries" to link to multiple country items. This is a many to many relationship. ORG1 and ORG2 may both link to Country1.

I want to generate a list view (for Excel export) of each ORG-Country relation, with some addition properties for each ORG. In SQL this would be a left join.

E.G.

ORG - Property1 - Country

ORG1 - Property1.Value - Country1

ORG1 - Property1.Value - Country2

ORG2 - Property1.Value - Country1

ORG2 - Property1.Value - Country3

Is there a simple way to build this report up - if I use a For Each then I want to be adding a new entry to the report each time like

$ReportList += $ReportLine

I'm unsure of the syntax for this, or if there is a simpler way of doing it. I've previously used a property object to extend the report info from a selection of items:

$Items = Get-Item.......

$property @(
@{Name="Property1";Expression={$PSItem.Property1}}
@{Name="Property2";Expression={... query based on a field of $PSItem...}}
.....
)

 $Items | Select-Object $property | Show-ListView

I can't see how to use this with the ORG items in the initial query needing to be reported once for each country they contain.

Answer 1

James, this sure does sound achievable. If I may, I'll write some pseudo code while I'm away from my development machine.

Here is how I might approach it:

1. Get a list of ORG items.
2. For each of the ORG items, get the IDs from the multilist field and get each of those items. Consider using a hashtable where the key is the ID of the Country and the value is the item.
3. For each of the ORG items, get the property you need. Consider using a hashtable where the key is the ID of the ORG and the value is the propery.
4. Consider creating a new array of objects using [PSCustomObject]@{"Key"="Value"}. Here you can loop over the ORG items again to build your custom object.
5. Pass the report objects to Show-ListView.

Update with Example

I wrote this for a case where we have insurance providers and associate them with states.

$insuranceGroups = Get-ChildItem -Path "master:" -ID "{DF58A0D1-5CB7-4D4D-8F6C-D3A3B68218E7}"
$stateLookup = @{}
$reportItems = @()
foreach($insuranceGroup in $insuranceGroups) {
    $insurancePayers = Get-ChildItem -Path "master:" -ID $insuranceGroup.ID
    foreach($insurancePayer in $insurancePayers) {
        $stateIds = [Sitecore.Data.ID]::ParseArray($insurancePayer.InsuredStates)
        foreach($stateId in $stateIds | ForEach-Object { $_.ToString() }) {
            $stateName = ""
            if(!$stateLookup.ContainsKey($stateId)) {
                $stateItem = Get-Item -Path "master:" -ID $stateId
                $stateLookup[$stateId] = $stateItem
                $stateName = $stateItem.StateName
            } else {
                $stateItem = $stateLookup[$stateId]
                $stateName = $stateItem.StateName
            }

            $reportItem = [PSCustomObject]@{
                "PayerName"=$insurancePayer.Name
                "PayerTitle"=$insurancePayer.Title
                "StateName"=$stateName
            }
            $reportItems += $reportItem
        }
    }
}

$reportItems | Show-ListView

Answer 2

Answering my own question: Use an array object

$ORGS = Get-ChildItem "{path}"

$Report= @()

Foreach($ORG in $ORGS)
{
    $Countries = [add code to retrieve countries from field]
    Foreach($Country in $Countries)
    {

    $ReportLine= [Add code to build a report line]
    $Report += $ReportLine
    }    

}

$Report | Show-Listview