cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
BrewinBruin
Level: Powered On

SQL Server View - Filtering an appointment calendar

I have two tables in SQL Server-Azure. One contains the BookDate, Bay, and Time_Slot of upcoming appointments:

BookDate Bay Time_Slot

2019-10-11 R-Bay  09:00-09:30

2019-10-11 R-Bay  09:30-10:00

2019-10-11 R-Bay  11:00 - 11:30

2019-10-11 S-Bay   09:00-09:30

 

A second table lists all the 16 Time_Slots available in any given day:

08:00-08:30, 08:30-09:00 .... 15:30-16:00

 

Is it possible to create a View in SQL Server (Utilizing Cross-Join, etc), which would display Items in Power Apps Gallery, and allows user to filter the collection on “Book_Date” & “Bay” to determine which time slot are available, and which are booked?

Bay = “R-Bay”. BookDate = 10/10/2019

Time Slot Avail

08:00-08:30 1

08:30-09:00 1

09:00-09:30 0

09:30-10:00 0

10:00-10:30 1

10:30-11:00 1

11:00-11:30 0

.... thru

15:30-16:00 1

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: SQL Server View - Filtering an appointment calendar

Hi @BrewinBruin ,

Based on the issue that you mentioned, I think this Delegation warning issue is related to the date type column in your LookUp formula.

 

Currently, the date type column is not delegable within SQL Server connector, direct date filters do not work for SQL Server. Please check the following article for more details:

https://docs.microsoft.com/en-us/connectors/sql/#powerapps-delegable-functions-and-operations-for-sq...

 

As an alternative solution, you could consider create a calculated number column within your SQL Table (in your Book SQL Table) based on the date type column using the following SQL Syntax:

ALTER TABLE 'YourBookTable' ADD DateAsInt AS (YEAR([BookDate]) * 10000 + MONTH([BookDate]) * 100 + DAY([BookDate]))

Then you could use the calculated number column as Filter column in above LookUp formula. You should modify above formula as below:

Then add a Gallery within your app, set the Items property to following:

AddColumns(
                      'Your Time Slot SQL Table',
                      "Avail",
                      If(
IsBlank(LookUp('Your Book SQL Table', DateAsInt = Value(Text(DatePicker1.SelectedDate, "yyyymmdd")) && Bay = Dropdown1.Selected.Result && Time_Slot = 'Your Time Slot SQL Table'[@'Time Slot'])),
1,
0
) ) 

please take a try with above solution, then check if the issue is solved.

 

If you have solved your problem, please go ahead to click "Accept as Solution" to identify this thread has  been solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Community Support Team
Community Support Team

Re: SQL Server View - Filtering an appointment calendar

HI @BrewinBruin ,

Could you please share a bit more about your scenario?

 

Based on the needs that you mentioned, I think it is not necessary to create SQL View to achieve your needs. As an direct solution, I think the Filter function in PowerApps could achieve your needs.

 

I have made a test on my side, please take a try with the following workaround:

Add a DatePicker control (DatePicker1), and a Dropdown control (Dropdown1) in your app. Set the Items property of the Dropdown control to following:

Distinct('Your Book SQL Table', Bay)

Then add a Gallery within your app, set the Items property to following:

AddColumns(
                      'Your Time Slot SQL Table',
                      "Avail",
                      If(
IsBlank(LookUp('Your Book SQL Table', Text(BookDate, "mm/dd/yyyy") = Text(DatePicker1.SelectedDate, "mm/dd/yyyy") && Bay = Dropdown1.Selected.Result && Time_Slot = 'Your Time Slot SQL Table'[@'Time Slot'])),
1,
0
) )

then within your Gallery, add two Label controls, set the Text property to following formula individually:

ThisItem.'Time Slot'
ThisItem.Avail

Please take a try with above solution, then check if the issue is solved.

Note: In order to get rid of the Time Zone offset issue between PowerApps and your SQL Server, please consider use datetimeoffset type column to store the Date value rather than use datetime type. Please check the following article for more details:

https://powerapps.microsoft.com/en-us/blog/working-with-datetime-values-in-sql/

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BrewinBruin
Level: Powered On

Re: SQL Server View - Filtering an appointment calendar

Kris,

 

Thanks for the assistance. 

 

Yes, that solution does work, however, I was attempting to avoid the "Delegation Warning - the 'Lookup' part of this formula might not work correctly on large data sets", by pushing as much of the work back to sql server via the view.

 

Regards,

Dwight

Community Support Team
Community Support Team

Re: SQL Server View - Filtering an appointment calendar

Hi @BrewinBruin ,

Based on the issue that you mentioned, I think this Delegation warning issue is related to the date type column in your LookUp formula.

 

Currently, the date type column is not delegable within SQL Server connector, direct date filters do not work for SQL Server. Please check the following article for more details:

https://docs.microsoft.com/en-us/connectors/sql/#powerapps-delegable-functions-and-operations-for-sq...

 

As an alternative solution, you could consider create a calculated number column within your SQL Table (in your Book SQL Table) based on the date type column using the following SQL Syntax:

ALTER TABLE 'YourBookTable' ADD DateAsInt AS (YEAR([BookDate]) * 10000 + MONTH([BookDate]) * 100 + DAY([BookDate]))

Then you could use the calculated number column as Filter column in above LookUp formula. You should modify above formula as below:

Then add a Gallery within your app, set the Items property to following:

AddColumns(
                      'Your Time Slot SQL Table',
                      "Avail",
                      If(
IsBlank(LookUp('Your Book SQL Table', DateAsInt = Value(Text(DatePicker1.SelectedDate, "yyyymmdd")) && Bay = Dropdown1.Selected.Result && Time_Slot = 'Your Time Slot SQL Table'[@'Time Slot'])),
1,
0
) ) 

please take a try with above solution, then check if the issue is solved.

 

If you have solved your problem, please go ahead to click "Accept as Solution" to identify this thread has  been solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

BrewinBruin
Level: Powered On

Re: SQL Server View - Filtering an appointment calendar

Kris,

 

That eliminated the Delegation issue.

Thanks for your assistance. 

 

Cheers,

Dwight

Helpful resources

Announcements
thirdimage

Power Apps Super User Class of 2020

Check it out!

thirdimage

New Badges

Check it out!

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors
Users online (4,428)