cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kevinsray
Helper IV
Helper IV

Finding records not used between datepicker dates

HI All.
I really need some help with what seems to be easy, but is causing hair loss.

I have 3 SQL Tables, EVENTS, RESOURCES and RESERVATIONS.

The EVENTS table contains a list of events and their start date, end date and number of days between them.
The RESOURCES table contains a list of resources.

The RESERVATIONS table contain a list of resources which have been reserved for specific events, using the event`s dates.


I am trying to create a screen where a user can select an event, and the select from a drop down any resources which are available during this event. Being that they are not already reserved in the RESERVATIONS table to another event.


I am really struggling with how to create the formula for the dropdown to select available resources.


Any help/suggestions/advice would be greatly appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
v-yutliu-msft
Community Support
Community Support

Hi kevinsray,

Do you want to filter resources which is not reserved by other events by choosing event?

Could you tell me the meaning of  “The RESERVATIONS table contain a list of resources which have been reserved for specific events, using the event`s dates”?

Could you tell me how the “using the event`s dates” is recorded in the table and what the data type they are?

I assume that the RESERVATIONS table contain resource, event and the dates that the resource is used. The column of date1 is the starting  using the resource day,date2 is the ending  using the resource day. All the data about date is stored as the date type.

I’ve made a similar test as follows:

1、Table names and column names are:

EVENTS table: event, start date, end date ,number of days

RESOURCES table: resource

RESERVATIONS table: resource, event,date1,date2

2、Create a List Box that you can choose event by clicking its item

Set its OnSelect:

ClearCollect(test,
           Filter(
                  '[dbo].[RESERVATIONS]', 
                  (
                      DateValue(Text(date1))>=DateValue(Text(ListBox1.Selected.'start date'))
                  &&DateValue(Text(date1))<=DateValue(Text(ListBox1.Selected.'end data'))
                                                                                                                                          )
             ||(
                   DateValue(Text(date2))>=DateValue(Text(ListBox1.Selected.'start date'))
                 &&DateValue(Text(date2))<=DateValue(Text(ListBox1.Selected.'end data'))
                                                                                                                                        )
                                                                                                                                          )
                                                                                                                                           )

                                                                                                                                                                                                                                                                                                                                        

3、set the Drop Down’s Items:

Filter('[dbo].[RESOURCES]',Not(resource in test.resource))

test2_410.jpg

 

 

 

 

 

Best regards,

Community Support Team _ Phoebe Liu

Community Support Team _ Phoebe Liu
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

1 REPLY 1
v-yutliu-msft
Community Support
Community Support

Hi kevinsray,

Do you want to filter resources which is not reserved by other events by choosing event?

Could you tell me the meaning of  “The RESERVATIONS table contain a list of resources which have been reserved for specific events, using the event`s dates”?

Could you tell me how the “using the event`s dates” is recorded in the table and what the data type they are?

I assume that the RESERVATIONS table contain resource, event and the dates that the resource is used. The column of date1 is the starting  using the resource day,date2 is the ending  using the resource day. All the data about date is stored as the date type.

I’ve made a similar test as follows:

1、Table names and column names are:

EVENTS table: event, start date, end date ,number of days

RESOURCES table: resource

RESERVATIONS table: resource, event,date1,date2

2、Create a List Box that you can choose event by clicking its item

Set its OnSelect:

ClearCollect(test,
           Filter(
                  '[dbo].[RESERVATIONS]', 
                  (
                      DateValue(Text(date1))>=DateValue(Text(ListBox1.Selected.'start date'))
                  &&DateValue(Text(date1))<=DateValue(Text(ListBox1.Selected.'end data'))
                                                                                                                                          )
             ||(
                   DateValue(Text(date2))>=DateValue(Text(ListBox1.Selected.'start date'))
                 &&DateValue(Text(date2))<=DateValue(Text(ListBox1.Selected.'end data'))
                                                                                                                                        )
                                                                                                                                          )
                                                                                                                                           )

                                                                                                                                                                                                                                                                                                                                        

3、set the Drop Down’s Items:

Filter('[dbo].[RESOURCES]',Not(resource in test.resource))

test2_410.jpg

 

 

 

 

 

Best regards,

Community Support Team _ Phoebe Liu

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

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

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