Get Table content

PowerShell Universal
1 
Product: PowerShell Universal
Version: 2.9.2

Hi all,

Bit stumped here.

I have a table, names down the rows, days across the columns. Each field is an editable text field that is populated with values from the SQL d/b. That’s all fine, loads ok. What I want to allow users to do is adjust the values in the fields then press a save icon that will read the new values in the table and update/insert these new values into the d/b.

$tableElement = Get-UDElement -Id 'percentagesTable'

I can see this JSON in a modal via:

Show-UDModal -Content {
  New-UDElement -Tag 'pre' -Content {
  $tableElement | ConvertTo-Json
 }

And that’s ok, the .Data and .currentData objects are there and I can loop through them - however if I update (by overtyping into one of the editable Textboxes in the table), and then $tableElement = Get-UDElement -Id 'percentagesTable' again, the values throughout the JSON still show the original values from when the table loaded.

Is it possible to get the changed values from the table, so that can be then updated into the d/b, then Sync-UDElement to refresh the SQL query and repopulate the table with the now updated values?

Might be something super easy I’m missing.

EDIT:
Do I set each TextInput field its own unique Id and then loop through them all to build an object and write that to SQL?

[Album] imgur.com

Cheers!
Steve.

3

Hi, the way I do a similar task, say if I want to delete a row from my table, I define the code like this:
(think you have to use the $eventdata on each row for your task here.)

$webPageColumns = @(
    New-UdTableColumn -Property WebPage_Id -Title Id
    New-UdTableColumn -Property Category -Title Category -IncludeInSearch
    New-UdTableColumn -Property Title -Title Title -IncludeInSearch
    New-UdTableColumn -Property Details -Title Details -IncludeInSearch
    New-UdTableColumn -Property Url -Title Url -IncludeInSearch -Render { 
        
            New-UDLink -Text $eventdata.Url -url $eventdata.Url -OpenInNewWindow
        }
    New-UdTableColumn -Property Delete -Title Delete -Render { 
        
        New-UDButton -Text "Delete" -OnClick {
            Show-UdToast -Message "Deleting from SQL server: Id: $($eventdata.WebPage_Id)" -Duration 200

            If ($sqlConnection.State -eq "Closed") {
                $sqlconnection.Open()
               }
       
               $sqlQuery = @" 
                    DELETE FROM dbo.[NewWebPages] WHERE WebPage_ID = @WebPage_id


"@


               $sqlCommand.CommandText = $sqlQuery
               $null = $sqlCommand.Parameters.Add("@webPage_iD", $eventData.WebPage_Id)

               $null = $sqlCommand.ExecuteScalar()
               $null = $sqlCommand.Parameters.Clear()
       
               $sqlConnection.close()
               
        }  
    }

)

Table looks like this: 

![image|690x196](upload://tlfarTgTWcZZjml4wIyRIi2hdfx.png)
4

Thanks for the reply @TmJr75 - that’s great, I do something similar on another table to delete rows from SQL. For this table though I need to read each field after a person has changed some of the values. I’ve ended up doing this which works:

After creating the Textbox input fields within the columns of the table with unique Ids, like:

New-UDTextbox -Id "$($Eventdata.PubMne)-Mon" -Value $Eventdata.Mon -Variant 'outlined'
etc
etc

I then loop through all the table rows/columns and an array of and setting an array of $weekdayNames and build a SQL query that will take whatever the Texbox fields now have in them and write them to the d/b table:

$tableElement = Get-UDElement -Id 'PercentagesTable'
$updatedPercentValuesForSQL = @()
foreach($row in $tableElement.CurrentData) {
    $rowName = $row.Name
    $percentObjToAdd = [PSCustomObject]@{
        Name = $rowName
    }
    foreach($weekdayName in $weekdayNames){
        $thisDaysPercent = (Get-UDElement -Id "$rowName-$weekdayName").value
        $percentObjToAdd | Add-Member -Name "$weekdayName`daysPercent" -Type NoteProperty -Value $thisDaysPercent
    }
    $updatedPercentValuesForSQL += $percentObjToAdd
}
                                        
$savePercentagesQuery = $null
$savePercentagesQuery += "DELETE FROM tbl_PercentageValuesSet; "
foreach($u in $updatedPercentValuesForSQL) {
    $savePercentagesQuery += "INSERT INTO tbl_PercentageValuesSet (Name, "
    foreach($weekdayName in $weekdayNames){
        $savePercentagesQuery += "$($weekdayName)daysPercent, "
    }
    $savePercentagesQuery += "CreatorName, CreationDate) "
    $savePercentagesQuery += "VALUES ('$($u.Name)', "
    foreach($weekdayName in $weekdayNames){
        $savePercentagesQuery += "'$($u."$($weekdayName)daysPercent")', "
    }
    $savePercentagesQuery += "'$($session:DisplayName)', '$(Get-Date)'); "
}

Show-UDToast $savePercentagesQuery -duration 15000

Invoke-DbaQuery -SqlInstance $cache:cfgSQLserver -Database $cache:cfgSQLdatabase -Query $savePercentagesQuery

Sync-UDElement -Id 'dynPercentagesTable'
Show-UDToast -Message "Saving percentages..." -Duration 2000

This may not be the best way to do it - but it’s working :slight_smile: