PowerShell: Appending Multiple Property Values to Array

Question

I am trying to append the $date variable which should contain the ticket creation date to an existing array that contains open tickets:

$teamConfig = @(
    [pscustomobject]@{
        TeamName  = 'Team1'
        TeamEmail = 'team1@domain.tld'
    }
    [pscustomobject]@{
        TeamName  = 'Team2'
        TeamEmail = 'team2@domain.tld'
    }
)

$query = "select * from INCAutomation"

$results = Invoke-Sqlcmd -query $query -ServerInstance 'localhost' -Database 'AyushTest'

$teamTickets = foreach($team in $teamConfig) {
  # Filter tickets based on team name
  $ticketIds = $results |Where-Object TeamName -eq $team.TeamName |Select -ExpandProperty TicketID
  $date = $results |Where-Object TeamName -eq $team.TeamName |Select -ExpandProperty createdDate

    # Create an empty array
    $ticketIdArray = @()

    foreach ($id in $ticketIds) {
        $thisId = "
            <tr>
            <td>$($id)</td>
            <td>$($date)</td>
            </tr>
        "
        $ticketIdArray += $thisId
    }


  $team |Select TeamName,TeamEmail,@{Name='HTML';Expression={$ticketIdArray}}
}

$teamTickets.HTML

However this is the output I get (example):

<tr>
<td>INC0001</td>
<td>10/12/20 10/12/20 10/12/20 10/12/20</td>
</tr>
        

<tr>
<td>INC0002</td>
<td>10/12/20 10/12/20 10/12/20 10/12/20</td>
</tr>
        
<tr>
<td>INC0003</td>
<td>10/12/20 10/12/20 10/12/20 10/12/20</td>
</tr>

This is my desired output:

<tr>
<td>INC0001</td>
<td>10/12/20</td>
</tr>
        

<tr>
<td>INC0002</td>
<td>10/12/20</td>
</tr>
        
<tr>
<td>INC0003</td>
<td>10/12/20</td>
</tr>

It seems like for whatever reason the date is being appended based on the total number of teams.

Any ideas? Thanks in advance

Word of advice: separate _data modeling_ from _presentation_ - right now you're making choices about the final ouput format (HTML) in a context where you're really more interested in being able to meaningfully interrogate the data programatically — Mathias R. Jessen, Dec 30 '20 at 13:35
hey @KemalK.no luck either, I got the following error: Cannot convert 'System.Object[]' to the type 'System.String' required by parameter 'ExpandProperty'. Specified method | is not supported. — yush, Dec 30 '20 at 13:36

Kemal K. · Answer 1 · 2020-12-30T13:43:42.870

1

i can not see the result can you try this

$ticketIds = $results |Where-Object TeamName -eq $team.TeamName |Select TicketID,createdDate


# Create an empty array
$ticketIdArray = @()

foreach ($id in $ticketIds) {
    $thisId = "
        <tr>
        <td>$($id.TicketID)</td>
        <td>$($id.createdDate)</td>
        </tr>
    "
    $ticketIdArray += $thisId
}

edited Dec 30 '20 at 13:43

answered Dec 30 '20 at 13:42

Kemal K.

301
2
7

score 1 · Answer 2 · edited Nov 08 '21 at 00:43

Kemal's solution may work, but I suggest that you use .NET arraylists instead of PS arrays.

PS arrays are of fixed size, which prevents their ironically native add/remove and related methods from working.

Meanwhile, .NET arraylists are not of fixed size, so those methods work. There is a performance benefit for arraylist manipulation if the arrays are big.

Instead of $myArray = @(), use [collections.arraylist]$myArray = @().

Thereafter, you can simply execute $myArray.Add($thisId), $myArray.Remove($thatId), etc.

score 1 · Accepted Answer · answered Nov 07 '21 at 11:05

As suggested by @ScripterMike and explained here, using the increase assignment operator (+=) to create a collection might get pretty expensive.
To avoid this, the correct PowerShell syntax is using the pipeline:

$ticketIdArray = foreach ($id in $ticketIds) {
     "
         <tr>
         <td>$($id.TicketID)</td>
         <td>$($id.createdDate)</td>
         </tr>
     "
 }

PowerShell: Appending Multiple Property Values to Array

3 Answers3