2

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.

2

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

Report Image Example

  • Thanks Michael - I added some pseudocode in my own answer, it's only missing how to build the $ReportLine object, but I think it's doing what you suggest otherwise. – James Walford Mar 1 '17 at 14:11
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.