none
Adding Parent Objects To Child API RRS feed

  • Question

  • I have been beating my head against my keyboard trying to figure out what I am doing wrong!!!! 

    I am using Invoke-RestMethod to get API data from a website.

    I am able to get all of the data without any issues but I am having troubles displaying that how I want to.



    Table 1 that is a list of companies and their unique IDs

    CompanyID     CompanyName

    27631             Company A

    26154             Company B

    26456             Company C

    Or as displayed in PS

    CompanyID: 27631

    CompanyName: Company A

    CompanyID: 26154

    CompanyName: Company B

    CompanyID: 26456

    CompanyName: Company C



    The CompanyID is then used to access the companies information. (These are 2 completely different places.)

    Example For Company A

    Active   CompanyUsers    CompanyComputers

    1          4                       5

    1          6                       7

    Example For Company B

    Active   CompanyUsers    CompanyComputers

    1          3                       4

    1          5                       6

    Example For Company C

    Active   CompanyUsers    CompanyComputers

    1          1                       2

    0          3                       4

    #################################################

    $CompanyURL = "https://api.crm.com"

    $s = (Invoke-RestMethod @Params2).CompanyList |
        ForEach-Object {
            $myCompanyName = $_.CompanyName
            $myCompanyID = $_.CompanyID
    $IDURL = "$CompanyURL/$myCompanyID/companyinfo"
            $Params4 = @{
                "ErrorAction" = "Stop"
                "URI" = $IDURL
                "ContentType" = "application/json"
                "Headers" = @{"Authorization" = "Bearer "+ $CompanyIDAuth}
                "Method" = "Get"
            }
            (Invoke-RestMethod @Params4).CompanyInformation | 
    foreach-object {$_|add-member -type noteproperty -name CompanyName -value $myCompanyName;$_} | 
    foreach-object {$_|add-member -type noteproperty -name CompanyID -value $myCompanyID;$_} | 
    Where-Object {$_.Active -ge 0} | 
    Select-Object CompanyName,CompanyID,Active,CompanyUsers,CompanyComputers
    }

    $s | Format-Table -auto

    #################################################

    This is what it is returning:
    CompanyName   CompanyID   Active   CompanyUsers    CompanyComputers
    Company A                 1        4               5
    Company A                 1        6               7    
    Company B                 1        3               4   
    Company B                 1        5               6   
    Company C                 1        1               2   
    *Note: It will return the CompanyName which is from the list of companies and their unique IDs but will not add any more. I understand that technically the CompanyID is only displaying because I defined it in Select-Object. When I tried with a different variation of this code, it kept saying it could not add this member because it was already there.


    This is what I am trying to get to return:
    CompanyName   CompanyID   Active   CompanyUsers    CompanyComputers
    Company A     27631       1        4               5
    Company A     27631       1        6               7  
    Company B     26154       1        3               4 
    Company B     26154       1        5               6 
    Company C     26456       1        1               2   

    Thank you very much in advance!!!!

    Nick Hohn

    Friday, March 30, 2018 7:42 PM

Answers

  • Obviously I can't query your web server for the data, but I can construct sample data:


    $companyInfoTable = @"
    "CompanyID","CompanyName"
    "27631","Company A"
    "26154","Company B"
    "26456","Company C"
    "@ | ConvertFrom-Csv
    
    $companyDataTable = @"
    "Name","Active","Users","Computers"
    "Company A","1","4","5"
    "Company A","1","6","7"
    "Company B","1","3","4"
    "Company B","1","5","6"
    "Company C","1","1","2"
    "Company C","0","3","4"
    "@ | ConvertFrom-Csv
    
    foreach ( $companyDataRow in $companyDataTable ) {
      if ( $companyDataRow.Active -ne "0" ) {
        $companyID = ""
        foreach ( $companyInfoRow in $companyInfoTable ) {
          if ( $companyDataRow.Name -eq $companyInfoRow.CompanyName ) {
            $companyID = $companyInfoRow.CompanyID
            break
          }
        }
        [PSCustomObject] @{
          "CompanyName" = $companyDataRow.Name
          "CompanyID"   = $companyID
          "Active"      = [Int] $companyDataRow.Active
          "Users"       = [Int] $companyDataRow.Users
          "Computers"   = [Int] $companyDataRow.Computers
        }
      }
    }
    

    Essentially the code "looks up" the ID from the $companyInfoTable table and constructs new composite output objects. (Requires PowerShell 3.0 or newer.) This could be made more efficient by converting the company info table into a hashtable (associative array) and use that for lookups. (This is left as an exercise for the reader.)

    -- Bill Stewart [Bill_Stewart]



    Wednesday, May 30, 2018 7:23 PM
    Moderator