What type is $entity, how do I search, and what is Product.IdentityBroker.PowerShellValue?

So you're saying I can't realiably search for another entity? Searching for the parent orgunit (and doing it recursively) is the only way this is going to work. Any other suggestions as to how I might achieve this?

This is actually a pretty common use case - "flattening" the entire org hierarchy on the person object so you can use it in policy and group population. It would be good to get a "UNIFY best practise" solution for this.

Currently the PowerShell components provided to the transformation script don't include the full entity context. The reason for this is that other transformations can access the context via LINQ to SQL which lets them do filtering/lookup at the DB layer, not in memory, whereas the PowerShell script wouldn't have this capability and so you wouldn't get the performance you'd be hoping for anyway.

You said that this is a common use case, is there an existing solution to this problem that you could use instead? The PowerShell transform is great for small ad-hoc transformations like string manipulation, type conversions, etc., but I don't think it would be the right tool in this case.

I don't believe there is an existing generally-preferred solution. I asked on yammer a couple of months back and no one had anything new for me. I've also been looking at a SQL based approach which involves a great long query to get data out of the Entity table and put it in another table (in another DB) and then reimport it back into IdB from there. But would much prefer to handle it in the Adapter if I can.

https://www.yammer.com/unifysolutions.net/#/Threads/show?threadId=696213313&search_origin=global&scoring=linear1Y-prankie-group-private-higher&match=any-exact&search_sort=relevance&page=1&search=%22org%20unit%22

I've now created a Powershell connector which is reading the data out of the IdB database and presenting it how I need it. Could I add a feature request that IdB handles this natively?

$ErrorActionPreference = "Stop"

. D:\Scripts\Shared\Set-LocalVariables.ps1
. D:\Scripts\Shared\FIMFunctions.ps1

$Query = @"
DECLARE @cid NVARCHAR(50) = '4C1AA662-F100-415F-A398-2B1225EE12FC';
DECLARE @orgid INT = (SELECT CollectionKeyId from [Unify.IdentityBroker].[dbo].[CollectionKey] where [Caption]='Organisation Unit Number');
DECLARE @level INT = (SELECT CollectionKeyId from [Unify.IdentityBroker].[dbo].[CollectionKey] where [Caption]='Organisation Unit Level');
DECLARE @parentid INT = (SELECT CollectionKeyId from [Unify.IdentityBroker].[dbo].[CollectionKey] where [Caption]='Superior Org Unit Number');

SELECT EntityId,
       MAX( CASE [CollectionKeyId] WHEN @level THEN StringValue ELSE '' END ) OrgUnitLevel, 
       MAX( CASE [CollectionKeyId] WHEN @orgid THEN StringValue ELSE '' END ) OrgUnitID,
       MAX( CASE [CollectionKeyId] WHEN @parentid THEN StringValue ELSE '' END ) ParentID
FROM [Unify.IdentityBroker].[dbo].[EntityValue]
WHERE [PartitionId]= @cid
GROUP BY EntityId;
"@

$rows = Invoke-SQLQuery -Server $SQLServer -Instance $SQLInstance -Database "Unify.IdentityBroker" -Query $Query

$logger.LogInformation("OrgUnit Flatten: Retrieved {0} rows from SQL query" -f $rows.count)

if ($rows.Count -gt 0)
{
    foreach ($row in $rows)
    {
        $entity = $entities.Create()
        [string]$id = $row."OrgUnitID"
        $entity["OrgUnitID"] = $id
        [string]$level = $row."OrgUnitLevel"
        $entity["OrgUnitLevel"] = $level
        [string]$fieldname = "OrgUnit" + $level
        $entity[$fieldname] = $id
        $row2 = $row
        while ($level -ne "1")
        {
            $id = $row2."ParentID"
            $row2 = $rows | where {$_."OrgUnitID" -eq $id}
            $level = $row2."OrgUnitLevel"
            $fieldname = "OrgUnit" + $level
            $entity[$fieldname] = $id
        }
        $entity.Commit()
    }
} 

Are you able to do up a data model to go with the feature request, so that it's a little more clear?

Thanks.