Hello.
I am trying to use UD as a proof of concept project to see if I can create a front-end using UD to allow editing rows, deleting rows, or add new rows to a SQL table.
Thus far, I’ve been able to execute the SQL query using dbatools module, and to display the data in a New-UDTable call.
I was hoping the UDTable object allowed for modifying a row, but I didn’t see anything like that.
The next best thing I could think of is to have New-UDButton objects that have “Edit” or “Delete” Or Add New" within the table object.
Below is a screenshot of a wireframe of my vision for this front-end (using the Edit/Delete buttons concept):
I’ve managed to do that (see below screenshot), but I’m having trouble getting the “current record” to be linked to the Edit and Delete buttons. Instead, it seems to return the entire object.
What I have now is:
When a user clicks the Edit or Delete button, it shows a New-UDModal that displays another New-UDTable that is intended to only show that row’s data (but currently shows the entire dataset again).
Am I going about this the wrong way? Is there an easier way to accomplish editing existing rows, or deleting a row? Did I miss edit/delete capabilities in any UDTable commands?
Is there a way I can “filter” the dataset to the “current” row tied to that row’s NameValuePairID? I tried using various combinations of $_ and $Item.NameValuePairID but I haven’t got that to work.
So when you add the rendered column for the button, that row’s data is available as $Body (JSON) which you can convert to an object (named $Item above) with:
$Item = ConvertFrom-Json $Body
Then you can access the properties of $Item, say for example the ‘Name’ property:
$Item.Name
(This may now be the best way to solve what we’re doing, but it’s working for me)
Thanks @pharnos! You got me past the hump of the current record for my Edit and Delete buttons, the key I was missing was the syntax for:$($Item.MyColumn).
Next, I want to have the Delete button show a table of the record it would delete, but I’m having trouble getting that table to only show that record (shows all records in the dataset).
How can I filter my already-acquired dataset down for just that record?
I tried something like: $sqldatatodelete = $($Item.NameValuePairID) but that didn’t quite work.
and tried: $Item = $Body | ConvertFrom-Json | Where-Object $Item.NameValuePairID -EQ $($Item.NameValuePairID)
A thought, why show the single row in another table for confirmation? Perhaps just show a modal popup when you click the ‘delete’ button which asks “Are you sure?” and show a form type view of some of the selected row values, along with ‘Yes’ and ‘No’ (or whatever labelled) buttons.
I’m stuck again.
I was trying to make it so that when a user selects the server, then with OnChange it sets the variable for the next UDSelect, then shows/creates the next UDSelect for Database, which then also with OnChange sets the variable for the last UDSelect and shows/creates the last UDSelect for Table.
It knows what to offer by reading simple text files that have the valid values to select from, found in \localhost\share\subfolder.
That way, instead of hard-coding the values, it would be a data-driven (flat file driven) dynamic process so that I don’t have to change the powershell code if i want to add a new server, database, or table to the UD.
So it’s kind of like a nested or cascading OnChange and New-UDSelect.
I’m not sure if logically that’s the way it should be, but it’s not working.
Nothing happens after I select the SQL Server.
Any ideas? Below is sanitized code of the UD in entirety.
New-UDDashboard -Title "MyCompany SQL data editing portal" -Content {
<#
# debug user
New-UDElement -Tag 'br' # debug
whoami # debug
New-UDElement -Tag 'br' # debug
([System.Security.Principal.WindowsIdentity]::GetCurrent()).Name # debug
New-UDElement -Tag 'br' # debug
$env:USERNAME # debug
New-UDElement -Tag 'br' # debug
$env:USERDOMAIN+"\"+$en:USERNAME # debug
New-UDElement -Tag 'br' # debug
#>
New-UDTypography -Text "MyCompany SQL data editing portal - please start by selecting the SQL Server"
New-UDElement -Tag 'br'
$sqlserveroptions = Get-Content "\\localhost\share\subfolder\SQLSERVERS.TXT" # account used for PowerShellUniversal service need permissions for this to work
New-UDElement -Tag 'br' # debug
#$sqlserverselections # debug
New-UDSelect -Id SelectSQLServer -Label "Select SQL Server" -Multiple:$false -Option {
New-UDSelectOption -Name "Select..." -Value 0 # placeholder
foreach ($server in $sqlserveroptions)
{
New-UDSelectOption -Name $server -Value $server
}
} # end Option SelectSQLServer
-OnChange {
$sqlserver = $Body
$sqldboptions = Get-Content "\\localhost\share\subfolder\Databases $sqlserver.TXT" # account used for PowerShellUniversal service need permissions for this to work
#New-UDElement -Tag 'br' # debug
#$sqldboptions # debug
#New-UDElement -Tag 'br' # debug
New-UDSelect -Id SelectSQLDatabase -Label "Select SQL Database" -Multiple:$false -Option {
New-UDSelectOption -Name "Select..." -Value 0 # placeholder
foreach ($sqldb in $sqldboptions)
{
New-UDSelectOption -Name $sqldb -Value $sqldb
}
} # end Option SelectSQLDatabase
-OnChange {
$sqldb = $Body
$sqltableoptions = Get-Content "\\localhost\share\subfolder\Tables $sqldb.TXT" # account used for PowerShellUniversal service need permissions for this to work
New-UDSelect -Id SelectSQLTable -Label "Select SQL Table" -Multiple:$false -Option {
New-UDSelectOption -Name "Select..." -Value 0 # placeholder
foreach ($sqltbl in $sqltableoptions)
{
New-UDSelectOption -Name $sqltbl -Value $sqltbl
}
} # end Option SelectSQLTable
-OnChange {
$sqltable = $Body
$sqltableoptions = Get-Content "\\localhost\share\subfolder\Tables $sqldb.TXT" # account used for PowerShellUniversal service need permissions for this to work
} # end OnChange SelectSQLTable
} # end OnChange SelectSQLDatabase
} # end OnChange SelectSQLServer
#$sqlserver = 'localhost' # debug. to be parameter from Select component
#$sqldb = 'prodcopy' # debug. to be parameter from Select component
#$sqlschema = 'lookup' # debug. NOT to be parameter from Select component
#$sqltable = 'vwNameValue' # debug. to be parameter from Select component
#$sqlquery = "SELECT * FROM [$sqlschema].[$sqltable]" # debug. to be retrieved from .SQL file?
$sqlquery = "SELECT * FROM $sqltable" # will have schema included
Import-Module dbatools
$sqlreaduser = 'TestLogin' # debug
$sqlreadpw = ConvertTo-SecureString -String 'test' -AsPlainText -Force # debug
$sqlreadcreds = New-Object System.Management.Automation.PSCredential -ArgumentList ($sqlreaduser, $sqlreadpw)
$sqldata = Invoke-DbaQuery -SqlInstance $sqlserver -Database $sqldb -Query $sqlquery -SqlCredential $sqlreadcreds | ForEach-Object {
@{
NameValueGroupID = $_.NameValueGroupID
GroupName = $_.GroupName
NameValuePairID = $_.NameValuePairID
Name = $_.Name
Value = $_.Value
IsActive = $_.IsActive
}
}
$sqleditdeletecolumns = @(
#New-UDTableColumn -Property NameValueGroupID -Title NameValueGroupID
New-UDTableColumn -Property GroupName -Title GroupName
#New-UDTableColumn -Property NameValuePairID -Title NameValuePairID
New-UDTableColumn -Property Name -Title Name
New-UDTableColumn -Property Value -Title Value
New-UDTableColumn -Property IsActive -Title IsActive
)
$sqlcolumns = @(
New-UDTableColumn -Property NameValueGroupID -Title NameValueGroupID -Sort $true
New-UDTableColumn -Property GroupName -Title GroupName -Sort $true
New-UDTableColumn -Property NameValuePairID -Title NameValuePairID -Sort $true
New-UDTableColumn -Property Name -Title Name -Sort $true
New-UDTableColumn -Property Value -Title Value -Sort $true
New-UDTableColumn -Property IsActive -Title IsActive -Sort $true
New-UDTableColumn -Property NameValuePairID -Title Edit -Sort $false -Filter $false -Render {
$Item = $Body | ConvertFrom-Json
New-UDButton -Id "btnEdit$($Item.NameValuePairID)" -Text Edit -Icon (New-UDICon -Icon edit) -OnClick {
Show-UDToast -Duration 10000 -Message "This will edit NameValuePairID $($Item.NameValuePairID)" # debug placeholder
#Show-UDMOdal -Content { ... } # TO BE
} # end UDButton Edit OnClick
}
New-UDTableColumn -Property NameValuePairID -Title Delete -Sort $false -Filter $false -Render {
$Item = $Body | ConvertFrom-Json
New-UDButton -Id "btnDelete$($Item.NameValuePairID)" -Text Delete -Icon (New-UDICon -Icon trash) -OnClick { Show-UDModal -FullWidth -MaxWidth 'md' -Content {
#New-UDButton -Id "btnDelete$($Item.NameValuePairID)" -Text Delete -Icon (New-UDICon -Icon trash) -OnClick { Show-UDModal -Fullscreen -Content {
$sqldatatodelete = $sqldata | Where-Object NameValuePairID -EQ $($Item.NameValuePairID)
New-UDTypography -Text "Are you CERTAIN you want to delete row for NameValuePairID $($Item.NameValuePairID)? THIS CANNOT BE UNDONE!"
New-UDTable -Data $sqldatatodelete -Columns $sqleditdeletecolumns -Title "$sqlserver $sqldb $sqlschema.$sqltable $($Item.NameValuePairID)"
# below buttons intended to be footer
New-UDButton -Text "Cancel" -Icon (New-UDIcon -Icon arrow_circle_left) -Style @{'color'='white';'background'='blue'} -OnClick { Hide-UDModal }
New-UDButton -Text "Delete" -Icon (New-UDIcon -Icon trash) -Style @{'color'='white';'background'='red'} -OnClick { Hide-UDModal } # change OnClick to run delete stored procedure
} # end Content
} # end UDButton Delete OnClick
} # end Render
)
#New-UDTable -Data $sqldata -Columns $sqlcolumns -Title "$sqlserver $sqldb $sqlschema.$sqltable" -Export -Filter -Search -Sort # debug
New-UDTable -Data $sqldata -Columns $sqlcolumns -Title "$sqlserver $sqldb $sqltable" -Export -Filter -Search -Sort
}
The first example there provides a way to update a select based on a previous selected value. The options can still come from your files to make it data driver.
The idea is that the first select will trigger a dynamic region to reload when something is selected. That dynamic region will look at the value of the select and then read the proper file. You can chain them togther so the third select looks at the second.
Here’s some code I didn’t test to give you the idea.
New-UDSelect -Id SelectSQLServer -Label "Select SQL Server" -Multiple:$false -Option {
New-UDSelectOption -Name "Select..." -Value 0 # placeholder
foreach ($server in $sqlserveroptions)
{
New-UDSelectOption -Name $server -Value $server
}
} # end Option SelectSQLServer -OnChange {
Sync-UDElement -Id 'databases'
}
New-UDDynamic -Id 'databases' -Content {
$SqlServer = (Get-UDElement -Id 'SelectSQLServer ').value
$sqldboptions = Get-Content "\\localhost\share\subfolder\Databases $sqlserver.TXT" # account used for PowerShellUniversal service need permissions for this to work
New-UDSelect -Id SelectSQLDatabase -Label "Select SQL Database" -Multiple:$false -Option {
New-UDSelectOption -Name "Select..." -Value 0 # placeholder
foreach ($sqldb in $sqldboptions)
{
New-UDSelectOption -Name $sqldb -Value $sqldb
}
} # end Option SelectSQLDatabase
}
New-UDDynamic -Id 'databases' -Content {
$SqlServer = (Get-UDElement -Id 'SelectSQLServer ').value
$sqldboptions = Get-Content "\\localhost\share\subfolder\Databases $sqlserver.TXT" # account used for PowerShellUniversal service need permissions for this to work
New-UDSelect -Id SelectSQLDatabase -Label "Select SQL Database" -Multiple:$false -Option {
New-UDSelectOption -Name "Select..." -Value 0 # placeholder
foreach ($sqldb in $sqldboptions)
{
New-UDSelectOption -Name $sqldb -Value $sqldb
}
} # end Option SelectSQLDatabase
-OnChange { Sync-UDElement -Id 'tables' }
}
New-UDDynamic -Id 'tables' -Content {
$SqlDb= (Get-UDElement -Id 'SelectSQLDatabase').value
$sqltableoptions = Get-Content "\\localhost\share\subfolder\Tables $sqldb.TXT" # account used for PowerShellUniversal service need permissions for this to work
New-UDSelect -Id SelectSQLTable -Label "Select SQL Table" -Multiple:$false -Option {
New-UDSelectOption -Name "Select..." -Value 0 # placeholder
foreach ($sqltbl in $sqltableoptions)
{
New-UDSelectOption -Name $sqltbl -Value $sqltbl
}
} # end Option SelectSQLTable
-OnChange {
Sync-UDElement -Id 'mydatatable'
} # end OnChange SelectSQLTable
}
Wow, the creator of UD responding to my forum post!
Thanks for your ideas and examples!
I’ve applied what you’ve shown, but I’m still having an issue.
I think there’s a scoping problem or something like that, because the 2nd Select (for database) is not showing the values for the selected server.
Upon loading the dashboard, it also seems to run all 3 in a row, without waiting for a selection.
That manifests by each Select appearing about a second after the prior one.
Below is a screen capture GIF that demonstrates what I mean with the Select appearing after another upon dashboard load, and the selecting localhost not working with the Database select and Table Select.
One thing that’s weird that I’ve discovered, is that if I comment out the "Select..." placeholder New-UDSelectOption lines, I get a React error:
Error: Minified React error #31; visit https://reactjs.org/docs/error-decoder.html?invariant=31&args[]=TypeError%3A%20Cannot%20read%20property%20'value'%20of%20undefined&args[]= for the full message or use the non-minified dev environment for full errors and additional helpful warnings.
in p
in ForwardRef
in ForwardRef
in div
in ForwardRef
in ForwardRef
in div
in ForwardRef
in ForwardRef
in ForwardRef
in ForwardRef
in u
in Dashboard
in t
in div
in t
in t
in n
Hello @wsuhoey nice stuff you been building here so I saw this post, and I use SQL with UD a lot, or have done. I am not saying this is the problem…and not blowing my own trumpet, but I use my own component here:- https://marketplace.universaldashboard.io/Dashboard/UniversalDashboard.UDSelector which I have implemented on a dashboard next to another drop-down using this same component and everything works as expected, as skimming through the post, this seems to be your latest hurdle to over-come correct? Happy to chip in with some code to show how this component works, although there is some already on this forum. Let me know if you need more info from me…or just to fully clairfy this latest hurdle is on selecting and the results not being pushed to the next select list?
Yes! Thanks for the offer to assist!
The problem is indeed the cascading UD-Selects not working as expected.
I’m about to logoff for the weekend so perhaps looking into this next week will work for you?
so I saw this post, and I use SQL with UD a lot, or have done. I am not saying this is the problem…and not blowing my own trumpet, but I use my own component here:-