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 = '[email protected]'
    }
    [pscustomobject]@{
        TeamName  = 'Team2'
        TeamEmail = '[email protected]'
    }
)

$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
– Mathias R. Jessen, Commented Dec 30, 2020 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
– yush, Commented Dec 30, 2020 at 13:36

iRon · Accepted Answer · 2021-11-07 11:05:53Z

1

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>
     "
 }

answered Nov 7, 2021 at 11:05

iRon

24.4k10 gold badges60 silver badges107 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kemal K. · Accepted Answer · 2020-12-30 13:43:42Z

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, 2020 at 13:43

answered Dec 30, 2020 at 13:42

Kemal K.

4114 silver badges8 bronze badges

Comments

Phil Dukhov · Accepted Answer · 2021-11-08 00:43:37Z

1

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.

edited Nov 8, 2021 at 0:43

Phil Dukhov

91k22 gold badges276 silver badges302 bronze badges

answered Dec 30, 2020 at 21:03

Dr. C.

1061 silver badge6 bronze badges

Collectives™ on Stack Overflow

PowerShell: Appending Multiple Property Values to Array

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related