Add items into a collection array dynamically

Question

Okay. Here's what I'm trying to do. I'm basically trying to make a 2D array in powershell by importing stuff from AD and a SQL database. I'm going to use the array to write, update, and delete rows in the database later in the script. What I'm having a problem with, is getting everything into one variable.

When I run my code, it adds the same user over and over again into the desired array. The very last item in the created to the $Table variable gets written over every single instance inside the $ToWrite.

I tried pausing after inputting all the variables into $Table, writing $Table to console, then writing $ToWrite to console. The $Table changes correctly in every loop, but again, once it writes to $ToWrite it overwrites each instance. I've tried multiple ways like .add(), PsCustomObject[], etc. I'm just stuck now.

Here's my semi-functioning code:

$AllPOCs = Get-ADGroupMember 'ALL POC'
$POCs = @()

$Counter = 0

$AllPOCs | ForEach-Object {
    $Name = $AllPOCs[$Counter].SamAccountName

    $TestPOC = Invoke-Sqlcmd -Query "SELECT * FROM TABLE WHERE CLIENT = '$Name'" -ServerInstance "SERVER\INSTANCE"

    if ($TestPOC -eq $null) {
        $POCs += Get-ADUser $Name -Properties * |
                 select -Property SamAccountName, GivenName, Surname, SID, EmailAddress
    }
    $Counter += 1
}

$ToWrite = @()
$Counter = 0
$SEQUENCE = Invoke-Sqlcmd -Query "Select TABLEFIELD FROM DATABASE WHERE NAME = 'FIELDID'" -ServerInstance "SERVER\INSTANCE"
$SEQUENCE = $SEQUENCE.RECNUM + 1
$Table = "" | select SEQUENCE, LASTUSER, GROUP, CLIENT, FNAME, NAME, EMAILID, USEDEPT, USELOCATION, CREATEDFROMSSD, DISPLAYCLIENTCOMMENTS, _INACTIVE_, WINUSERID, SELFSERVICEACCESS, SELFSERVICELICENSE, WIAENABLED, SID

#everything works correctly up to here

$POCs | ForEach-Object {
    $Table.SEQUENCE = $SEQUENCE
    $Table.LASTUSER = 'SYSTEMACCOUNT'
    $Table.GROUP = 1
    $Table.CLIENT = $POCs[$Counter].SamAccountName.ToUpper()
    $Table.FNAME = $POCs[$Counter].GivenName.ToString()
    $Table.Name = $POCs[$Counter].surname.ToString()
    $Table.EmailID = 'SMTP:{' + $POCs[$Counter].EmailAddress.ToString() + '}' + 
    $POCs[$Counter].EmailAddress.ToString()
    $Table.USEDEPT = 0
    $Table.USELOCATION = 0
    $Table.CREATEDFROMSSD = 0
    $Table.DISPLAYCLIENTCOMMENTS = 0
    $Table._INACTIVE_ = 0
    $Table.WINUSERID = '\DOMAIN' + $POCs[$Counter].SamAccountName.ToString()
    $Table.SELFSERVICEACCESS = 'TYPE'
    $Table.SELFSERVICELICENSE = 1
    $Table.WIAENABLED = 1
    $Table.SID = $POCs[$Counter].SID.ToString()

    $ToWrite += $Table #THIS DOESN'T WORK PROPERLY.
    $SEQUENCE += 1
    $Counter += 1
}

Output example:

SEQUENCE              : 1206
LASTUSER              : SYSTEMACCOUNT
GROUP                 : 1
CLIENT                : USERNAME
FNAME                 : FIRSTNAME
NAME                  : LASTNAME
EMAILID               : SMTP:{EMAIL}EMAIL
USEDEPT               : 0
USELOCATION           : 0
CREATEDFROMSSD        : 0
DISPLAYCLIENTCOMMENTS : 0
_INACTIVE_            : 0
WINUSERID             : DOMAIN\USERNAME
SELFSERVICEACCESS     : TYPE
SELFSERVICELICENSE    : 1
WIAENABLED            : 1
SID                   : S-1-Z-XX-CCCCCCCCCC-YYYYYYYYY-VVVVVVVVVV-125121

SEQUENCE              : 1206
LASTUSER              : SYSTEMACCOUNT
GROUP                 : 1
CLIENT                : USERNAME
FNAME                 : FIRSTNAME
NAME                  : LASTNAME
EMAILID               : SMTP:{EMAIL}EMAIL
USEDEPT               : 0
USELOCATION           : 0
CREATEDFROMSSD        : 0
DISPLAYCLIENTCOMMENTS : 0
_INACTIVE_            : 0
WINUSERID             : DOMAIN\USERNAME
SELFSERVICEACCESS     : TYPE
SELFSERVICELICENSE    : 1
WIAENABLED            : 1
SID                   : S-1-Z-XX-CCCCCCCCCC-YYYYYYYYY-VVVVVVVVVV-125121

SEQUENCE              : 1206
LASTUSER              : SYSTEMACCOUNT
GROUP                 : 1
CLIENT                : USERNAME
FNAME                 : FIRSTNAME
NAME                  : LASTNAME
EMAILID               : SMTP:{EMAIL}EMAIL
USEDEPT               : 0
USELOCATION           : 0
CREATEDFROMSSD        : 0
DISPLAYCLIENTCOMMENTS : 0
_INACTIVE_            : 0
WINUSERID             : DOMAIN\USERNAME
SELFSERVICEACCESS     : TYPE
SELFSERVICELICENSE    : 1
WIAENABLED            : 1
SID                   : S-1-Z-XX-CCCCCCCCCC-YYYYYYYYY-VVVVVVVVVV-125121

Any ideas?

Answer 1

The problem is that you're updating the very same object, $Table, over and over, and adding references to that same object to the output array, $ToWrite - all of whose elements therefore end up pointing to the one and only $Table object, whose property values at that point contain the ones that were assigned in the last iteration.

The problem is explained in detail in this answer, which shows a possible solution using custom classes, available in PowerShell v5 and above.

A solution without custom classes requires you to clone your custom $Table object in each iteration:

# Create a new instance with the same properties:
$Table = $Table.psobject.Copy()

Note: This cloning technique only works as expected with custom objects, i.e., instances of [System.Management.Automation.PSCustomObject], such as created by the Select-Object cmdlet and literal syntax [pscustomobject] @{ ... }

That said, since you're assigning to all properties of your custom object in your loop, there is no benefit to creating a template object up front - instead, simply use literal custom-object creation syntax [pscustomobject] @{ ... } (PSv3+) inside your loop, which implicitly creates a new instance in every iteration.

---

Additionally, your solution can be streamlined, because it is both simpler and more efficient to let PowerShell create arrays for you, simply by collecting the output from commands that output multiple objects in a variable.

Here's a simplified example that puts it all together:

# Loop over the input and instantiate a new custom object
# in each iteration, then let PowerShell collect the results
# in array variable $ToWrite
[array] $ToWrite = 1..3 | ForEach-Object {
  # Instantiate and output a new custom object in each iteration.
  [pscustomobject] @{
    PropA = "ValueA-$_"
    PropB = "ValueB-$_"
  }
}

# Output the resulting array
$ToWrite

^{Note: The [array] type constraint is only needed if you need to ensure that $ToWrite is always an array; without it, if there happened to be just a single loop iteration and therefore output object, $ToWrite would store that output object as-is, not wrapped in an array (this behavior is fundamental to PowerShell's pipeline).}

The above yields the following, showing that distinct objects were created:

PropA    PropB
-----    -----
ValueA-1 ValueB-1
ValueA-2 ValueB-2
ValueA-3 ValueB-3

Answer 2

To illustrate - big caveat here, I'm at home so can't test any of this but it should give you an idea:

$AllPOCs = Get-ADGroupMember 'ALL POC'

$Table = $AllPOCs | ForEach-Object {
    $Name = $_.SamAccountName
    $TestPOC = Invoke-Sqlcmd -Query "SELECT * FROM TABLE WHERE CLIENT = '$Name'" -ServerInstance "SERVER\INSTANCE"

    If($TestPOC -eq $null) {
        $POC = get-aduser $Name -Properties * | select -Property SamAccountName, GivenName, Surname, SID, EmailAddress 
        $SEQUENCE = Invoke-Sqlcmd -Query "Select TABLEFIELD FROM DATABASE WHERE NAME = 'FIELDID'" -ServerInstance "SERVER\INSTANCE"
        $SEQUENCE = $SEQUENCE.RECNUM + 1
        [pscustomobject]@{SEQUENCE = $SEQUENCE;
                        LASTUSER = 'SYSTEMACCOUNT';
                        GROUP = 1;
                        CLIENT = $_.ToUpper();
                        FNAME = $_.GivenName.ToString();
                        Name = $_.surname.ToString();
                        EmailID = 'SMTP:{' + $_.EmailAddress.ToString() + '}' + $_.EmailAddress.ToString();
                        USEDEPT = 0;
                        USELOCATION = 0;
                        CREATEDFROMSSD = 0;
                        DISPLAYCLIENTCOMMENTS = 0;
                        _INACTIVE_ = 0;
                        WINUSERID = '\DOMAIN' + $_.SamAccountName.ToString();
                        SELFSERVICEACCESS = 'TYPE';
                        SELFSERVICELICENSE = 1;
                        WIAENABLED = 1;
                        SID = $_.SID.ToString()}
        $SEQUENCE += 1
    }
}

The above can also be simplified, but I've tried to keep it similar to your exisitng code