SQL Server View - Filtering an appointment calendar

[BrewinBruin]

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

[v-xida-msft]

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,

[BrewinBruin]

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

[v-xida-msft · Accepted solution]

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,

[BrewinBruin]

Kris,

That eliminated the Delegation issue.

Thanks for your assistance. 

Cheers,

Dwight