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.