multiple lookup powershell array

Question

have an array with organization data like this:

org_id  org_name        parent_id
1       Company         NULL        
2       HR              1           
3       MARKETING       2           
4       FINANCE         1           
5       IT              4

So the structure is:

[Company]
|- HR
| |- MARKETING
|- FINANCE
| |- IT

I'm trying to figure out how I can query which parent organization an organization has for a specific user.

So if a user has defined that they belong to IT I want to match string in org_name and get parent_id. then match parent_id against org_id so in the end get org_name, in this case FINANCE.

score 2 · Accepted Answer · answered Nov 15 '19 at 16:04

If I understand the question properly, your organization array is like this:

$Company = [PsCustomObject]@{ org_id = 1; org_name = 'Company'; parent_id = $null },
           [PsCustomObject]@{ org_id = 2; org_name = 'HR'; parent_id = 1 },
           [PsCustomObject]@{ org_id = 3; org_name = 'MARKETING'; parent_id = 2 },
           [PsCustomObject]@{ org_id = 4; org_name = 'FINANCE'; parent_id = 1 },
           [PsCustomObject]@{ org_id = 5; org_name = 'IT'; parent_id = 4 }

With this you can do

$department = 'IT'
$parentId   = ($Company | Where-Object { $_.org_name -eq $department}).parent_id
$orgName    = ($Company | Where-Object {$_.org_id -eq $parentId }).org_name

After this, $orgName contains FINANCE

If the department you are after is HR or FINANCE, it will return Company
If you search for department MARKETING, you'll end up with HR etc.

Thanks! this work just fine. started doing some while loops to find a specific row and then match that row index with the parent id row. this is much simpler :) — SuperDOS, Nov 16 '19 at 11:51

iRon · Answer 2 · 2019-11-15T20:40:31.033

Using this Join-Object cmdlet (see also: what's the best way to join two tables into one?), you might make use of it's self-join feature to create a cross reference:

$List = ConvertFrom-SourceTable '
    org_id  org_name        parent_id
    1       Company         NULL
    2       HR              1
    3       MARKETING       2
    4       FINANCE         1
    5       IT              4' # https://www.powershellgallery.com/packages/ConvertFrom-SourceTable

$Reference = FullJoin $List parent_id -eq org_id '', 'parent'
$Reference | Format-Table # Show what's in the $Reference

org_id org_name  parent_id parentorg_id parentorg_name parentparent_id
------ --------  --------- ------------ -------------- ---------------
1      Company   NULL
2      HR        1         1            Company        NULL
3      MARKETING 2         2            HR             1
4      FINANCE   1         1            Company        NULL
5      IT        4         4            FINANCE        1
                           3            MARKETING      2
                           5            IT             4

Selecting a parent organization of an organization:

$Organization = 'IT'
($Reference | Where-Object org_name -eq $Organization).parentorg_name
FINANCE

Or vice versa: selecting the organization(s) of a parent organization:

$Parent = 'Company'
($Reference | Where-Object parentorg_name -eq $Parent).org_name
HR
FINANCE

multiple lookup powershell array

2 Answers2