I have Googled and found a Date Picker, but no idea how to integrate it?

Universal Dashboard Universal Dashboard Help
1

Hello again everybody,

I have been using my dashboard for over a month and there is some feature creep fast approaching (self inflicted I might add) :stuck_out_tongue:

I want to use a date picker that uses the input as a variable that I can feed back into the end point of the script where my SQL statements live. I am making a page with a date picker that populates a table and graph, and I found this rather handy piece of code:

    New-UDInput -Title "Select Range (DAY/MONTH/YEAR)" -Id "DateForm" -Content {
   New-UDInputField -Type 'date' -Name 'DateFrom' -Placeholder 'FROM:' -DefaultValue "01-01-2019"
     New-UDInputField -Type 'date' -Name 'DateTo' -Placeholder 'TO:'-DefaultValue "01-02-2019"

} -Endpoint {
    param(
        $DateFrom,
        $DateTo
    )

}

My problem is that I have no idea how to interact with the input as those variables remain empty after picking a date (from what I can tell) .

Is there a better way of achieving this?

2

Hello @kreef so glad you asked this question as I recently done this at work…I used one of my own custom components:-
https://marketplace.universaldashboard.io/Dashboard/UniversalDashboard.UDDatePicker
Which I designed to make a simple FROM and TO date picker…
If you read the thread here:- New-UDDatePicker a new calendar component
you will see I posted a link to my github here:-
https://github.com/psDevUK/New-UDDatePicker
Look at the https://github.com/psDevUK/New-UDDatePicker/blob/master/DayPickerExample.ps1 to see how to use it…if you need more help I have a working example using this date picker along with:-
https://marketplace.universaldashboard.io/Dashboard/UniversalDashboard.UDSelector
Which I pre-populated loads of choices from the DB
https://marketplace.universaldashboard.io/Dashboard/UniversalDashboard.UDButtonParticle
This is so they only clicked the button once…and it also looks super cool :sunglasses:
If you need more guidence let me know. Peace

1 Like
3

Wow thanks!!!

I will start working on this and post back if I get stuck!

:smiley:

1 Like
4

Hi @psDevUK, I seem to have hit a few snags so far, do you think you could help with some silly questions?

I have installed the UniversalDashboardUDDatePicker with the following command:

Install-Module UniversalDashboard.UDDatePicker

When I run the test script from the Project’s Page I get the following error when I select 2 dates (Look at the bottom right of my screenshot):
"An unexpected error occured invoking 'ClientEvent' on the server. CmdletInvocationException: AuthorizationManager check failed"

image
image1907×864 21.8 KB

And the last question is how would I integrate your date picker into this page?

$HomePage = New-UDPage -Name "Home" -Icon home -Content {
   

     New-UDChart -Title "Here's a Fancy Chart " -Type Doughnut -Height 400px -Width 1 -Endpoint { 
                    $cache:SomeSQLData | Out-UDChartData  -DataProperty "Today Count" -Label "name" -DatasetLabel "$cache:SomeSQLData.'Name'" -BackgroundColor @("#52D726", "#FFEC00", "#FF7300", "#FF0000", "#007ED6", "#7CDDDD", "#FFEC21", "#378AFF", "#FFA32F", "#F54F52", "#93F03B", "#9552EA") -BorderColor 'black' -HoverBackgroundColor '#FF9F0D'
                       } -RefreshInterval 10 -AutoRefresh  
               
   }

Thanks again for all your help! :smiley:

5

Hello @kreef no worries for asking the questions, and no question is a silly question as it will end up helping someone else out with that same question in the future…
I just seen this ping up as I was replying to another post…sadly I need to do some house things right now…but with any component I build I try and give as much help as possible…as I put the component out there… so have you read:- https://psdevuk.github.io/ud-flix/Custom-Date-Picker/ which is the blog I wrote for this?
I will post a working example later with comments but I really need to go help my wife and kids, else I will be in trouble… :roll_eyes: will update this post later, but let me know how you get on with the blog read and if makes more sense after that. Peace

1 Like
6

No problem at all, thanks for the quick reply :smiley:

I must admit, I haven’t read the blogpost yet, I’ll head over there in the meantime.

Thanks again

7

Unfortunately I still can’t seem to get this working, I Tried the other calendar and I also get the same Toast error message when I click to submit a date:
"An unexpected error occured invoking 'ClientEvent' on the server. CmdletInvocationException: AuthorizationManager check failed"
I wonder if it isn’t something wrong with my installation then? :thinking:

8

I run this component on a work dashboard so I know it works…and had others users use this ok…do you have a locked down powershell execution policy? Can you install latest community edition on vm or pc/laptop and see if you can get module working with that? I use loads of my custom component on my dashboard. Can you try another random component off of the marketplace see if you get same issue

9

I uninstalled the module and reinstalled it and it seems to be working now, thanks!

I’ll start testing it out and see how it goes.

1 Like
10

Hi again. :slight_smile:

So I have been experimenting and everything seems to work just fine, but I can’t get the dashboard to accept the dates that I choose.

My thinking was that I just use the start and end variables in my SQL statement, but for some reason it doesn’t populate my chart. If it’s not too much of a cheeky request you by any chance have an example where you use the date picker with SQL?

11

Hey @kreef must be in different time zones, as I am running late for work…more than happy to provide a fully working SQL example, but will have to wait till I finish work, or on my lunch break…I did do a blog on how I use SQL in UD located here:- https://psdevuk.github.io/ud-flix/Serving-SQL-on-a-UniversalDashboard/
Also just for clarity my SQL DB I search on I have to provide the date format in yyyy-mm-dd as in

Select something
,somethingElse
From dbo.this.DB
WHERE datecolumn BETWEEN '2019-01-01' AND '2019-01-16'
12

Hey @kreef finished work and back home now…so you need to read the blog on how I am using SQL with UD…I couldn’t post whole script due to sensitivity but this is it, minus the New-UDSelector bit I had to strip out…FOCUS on the $Session:Start and $Session:End as that is how the SQL dates are being past, after being formatted correctly. I do not claim this is the best way or smartest, but it works for me and give me the results I need everytime:-

New-UDColumn -Size 4 -Endpoint {
New-UDCard -BackgroundColor "#8789c0" -Content {
    New-UDDatePicker -Id "Picker"
}
}
New-UDColumn -Size 2 -Endpoint {
New-UDCard -BackgroundColor "#8789c0" -Content {
    New-UDButtonParticle -Id "Explode2" -Text "Search" -Color "#2e3d55" -BackgroundColor "#2e3d55" -Icon search -onClick {
        $from = (Get-UDElement -Id "Picker").Attributes.from
        [datetime]$s = "$from"
        $Session:Start = get-date $s -format 'yyyy-MM-dd'
        $to = (Get-UDElement -Id "Picker").Attributes.to
        [datetime]$e = "$to"
        $Session:End = get-date $e -format 'yyyy-MM-dd'
        show-udtoast -message "You Selected $Session:Start until $Session:End" -duration 5000 -Position center
        @("GridData3","InsideGrid3") | Sync-UDElement
        start-sleep -Seconds 5
        }
}
}
New-UDColumn -Size 12 -Endpoint {
    New-UDElement -Id GridData3 -Tag div -Endpoint {
if (-not($Session:Selected -eq $null)) {
New-UDGrid -Headers @("WeekEndingDate","AccountAnalysisCode","CustomerCode","ProductName","Quantity","Net","Depot","Bread","Milk","Goods") -Properties @("WeekEndingDate","AccountAnalysisCode","CustomerCode","ProductName","Quantity","Net","Depot") -PageSize 10 -DefaultSortColumn "AccountAnalysisCode" -Endpoint {
$Query3 = @"
SELECT CONVERT(varchar,[WeekendingDate],111) WeekEndingDate
,[AccountAnalysisCode]
,CustomerCode
,[ProductName]
,[Quantity]
,[TransactionNet] Net
,[TransactionDepot] Depot
FROM [DATABASE].[dbo].[View]
WHERE TransactionType = 'Invoice' AND AccountAnalysisCode IN ($($Session:Selected))
AND TransactionDate BETWEEN '$($Session:Start)' AND '$($Session:End)'
ORDER BY AccountAnalysisCode,CustomerCode
"@
$A3 = Invoke-Sqlcmd2 -ServerInstance DB-SERVER -Database DATABASE -Query $Query3 -Username USERNAME -Password PASSWORD -QueryTimeout 500
$A3 | Select-Object "WeekEndingDate","AccountAnalysisCode","CustomerCode","ProductName","Quantity","Net","Depot" | Out-UDGridData

} -Id InsideGrid3

}

} -AutoRefresh
}

The code is not complete and does not have all the corresponding closing brackets…but hopefully this is enough to show you how to get the date selector working with SQL…on my blog I do mention how to do charts so please look at that as well. Peace

1 Like
13

Thanks @psDevUK, much appreciated.

I just wanted to compare the structure of my dashboard to someone with a working example, and you have provided me with an excellent source.

Thank you :slight_smile:

1 Like