Combine API results into single object

PowerShell Help
1

I have a process to interact with an API and write the data to a SQL table. Hitting the two endpoints separately and getting the data is no issue. I am stuck now on how to combine them so I can make one write to SQL as the loop processes. Endpoint #1 gets me all the data “/api/v1/employee/$($.eecode)" . I need to hit Endpoint #2 "/api/v1/employee/$($.eecode)/customfield”

with the same eecode from the first call. I want to keep them in the same object so I can write the row to SQL with data from both endpoints. I had to remove some code since the API for the system I am interacting with isn’t public.

function Export-Feed {
    Begin {
        $serverInstance = “”
        $database = “”
        $tableName = “”
        $employees = Get-Directory | Where-Object eestatus -eq A
    }
    Process {
        $result = $employees | ForEach-Object -Parallel {
            $user = Connect-Api -apiEndpoint “/api/v1/employee/$($_.eecode)”
            [pscustomobject][ordered]@{
                ‘firstname’                 = $user.firstname
                ‘middlename’                = $user.middlename
                ‘lastname’                  = $user.lastname
                ‘ADID’                      = (Connect-Api -apiEndpoint “/api/v1/employee/$($_.eecode)/customfield”).CustomText04.value
                ‘hire_date’                 = $use.hire_date
                ‘rehire_date’               = $user.rehiredate
                ‘position_title’            = $user.position_title
                ‘termination_date’          = if ($null) { $_.termination_date }else { “NULL” }
                ‘work_email’                = $user.work_email
                ’employee_code’             = $user.employee_code
                ‘clocksequencenumber’       = $user.clocksequencenumber
                ‘department_code’           = $user.department_code
                ‘department_description’    = $user.department_description
                ’employee_status’           = $user.employee_status
                ‘supervisor_primary’        = $user.supervisor_primary
                ‘supervisor_primary_code’   = $user.supervisor_primary_code
                ‘supervisor_secondary’      = $user.supervisor_secondary
                ‘supervisor_secondary_code’ = $user.supervisor_secondary_code
                ‘supervisor_tertiary’       = $user.supervisor_tertiary
                ‘supervisor_tertiary_code’  = $user.supervisor_tertiary_code
                ‘supervisor_quaternary’     = $user.supervisor_quaternary
                ‘cat1’                      = $user.cat1
                ‘cat1desc’                  = $user.cat1desc
                ‘cat2’                      = $user.cat2
                ‘cat2desc’                  = $user.cat2desc
                ‘cat3’                      = $user.cat3
                ‘cat3desc’                  = $user.cat3desc
                ‘cat4’                      = $user.cat4
                ‘cat4desc’                  = $user.cat4desc
                ‘cat5’                      = $user.cat5
                ‘cat5desc’                  = $user.cat5desc
                ‘cat6’                      = $user.cat6
                ‘cat6desc’                  = $user.cat6desc
                ‘cat7’                      = $user.cat7
                ‘cat7desc’                  = $user.cat7desc
                ‘cat8’                      = $user.cat8
                ‘cat8desc’                  = $user.cat8desc
                ‘cat9’                      = $user.cat9
                ‘cat9desc’                  = $user.cat9desc
            }
        }
    }
    End {
        #$result | Out-File c:\temp\test.txt
        Write-SqlTableData -DatabaseName $database -TableName $tableName -ServerInstance $serverInstance -SchemaName dbo -InputData $result -force
    }
}
2 
function Export-Feed {

    Begin {
        $serverInstance = ""
        $database = ""
        $tableName = ""
        $employees = Get-Directory | Where-Object eestatus -eq A
    }

    Process {
        $result = $employees | ForEach-Object -Parallel {

            $params = @(
                'firstname',
                'middlename',
                'lastname',
                @{n='ADID';e=(Connect-Api -apiEndpoint "/api/v1/employee/$($_.eecode)/customfield").CustomText04},
                'hire_date',
                'rehire_date',
                'position_title',
                'termination_date',
                'work_email',
                'employee_code',
                'clocksequencenumber',
                'department_code',
                'department_description',
                'employee_status',
                'supervisor_primary',
                'supervisor_primary_code',
                'supervisor_secondary',
                'supervisor_secondary_code',
                'supervisor_tertiary',
                'supervisor_tertiary_code',
                'supervisor_quaternary',
                'cat1',
                'cat1desc',
                'cat2',
                'cat2desc',
                'cat3',
                'cat3desc',
                'cat4',
                'cat4desc',
                'cat5',
                'cat5desc',
                'cat6',
                'cat6desc',
                'cat7',
                'cat7desc',
                'cat8',
                'cat8desc',
                'cat9',
                'cat9desc'
            )

            
            Connect-Api -apiEndpoint "/api/v1/employee/$($_.eecode)" | Select-Object $params
            #Connect-Api -apiEndpoint "/api/v1/employee/$($_.employee_code)/customfield" | Select-Object -ExpandProperty CustomText04 | Select-Object value
           
        }
    }
    
    End {
        $result | Out-File c:\temp\test.txt
        #Write-SqlTableData -DatabaseName $database -TableName $tableName -ServerInstance $serverInstance -SchemaName dbo -InputData $result -force 
    }
}
3

Sam, I never would have thought of putting the call in the expression for ADID. I added CustomText04.value and by itself, it returns the value I would need. Now I am getting an error when the second call is being made and selecting the $params.

<!–StartFragment –>

Error:
<!--StartFragment -->
… -apiEndpoint "/api/v1/employee/$($_.eecode)" | Select-Object $params | ~~~~~~~~~~~~~~~~~~~~~ | The "expression" key cannot have an empty string value.
<!--EndFragment -->
<!--EndFragment -->

 

4

The expression key value needs to be a script block (surrounded by {}).

@{n='ADID';e={(Connect-Api -apiEndpoint "/api/v1/employee/$($_.eecode)/customfield").CustomText04}}
5

Thanks! That helped out getting rid of that error. My text file I write out is still blank for the ADID. Running the expression by itself and getting the value of the custom field gets me what I want.

I can also grab the value directly and be left with a string.

PS C:\Users\E011891> (Connect-Api -apiEndpoint "/api/v1/employee/1891/customfield").CustomText04

description value
----------- -----
ADID        E011891

If I load the expression into my shell. The array looks as if it isn’t executing the command?

PS C:\Users\E011891> $params
firstname
middlename
lastname

Name                           Value
----                           -----
e                              (Connect-Api -apiEndpoint "/api/v1/employee/1891/customfield").CustomText04.value
n                              ADID
hire_date

 

6 
@{n='ADID';e={(Connect-Api -apiEndpoint "/api/v1/employee/$($_.eecode)/customfield").CustomText04.value}}
7

I have the expression fixed with the extra {} to clear my error. When I take out the variable and hard code an employee code it will execute and stick the value into the $params array.

However, when I have the variable in the execution it comes out blank in my text file. I know the variable evaluates correctly as I can write the foreach-object and get all the values in my shell.

8

I updated the code with my final result. I refactored a bit, but have the result I need now to make the SQL table write. Thx for your input. It helped out a lot.