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

Compare items with date ranges – show only if they overlap

I’m building a project assigning and scheduling app.

 

  • I’m using an Azure SQL database to store my projects.
  • Assigned project has a start date and an end date. And an assigned consultant.
  • New projects are also registered with a start date and an end date.

When assigning a new project to a consultant – I would like to filter all the current projects which overlaps with the new project.

Anyone has any ideas on how this would be possible achieve?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Compare items with date ranges – show only if they overlap

Hi @edvardwikstrom,

 

Here are some questions:
1. How would you get the a new Progect, from Gallery selection, or Form submit?

2. When filtering the Assigned project, are all the projects need to be filtered with?

 

Assuming the New Project is selected from Gallery1, and the filtered result for Assigned projects would be displayed in Gallery2, then the formula under Gallery2 items property should be:

Filter(AssignedProjectsTable, 
           or( Gallery1.Selected.StartDate < StartDate < Gallery1.Selected.EndDate,
               Gallery1.Selected.StartDate < EndDate   < Gallery1.Selected.EndDate
               )
)

Regards,

Michael

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

Re: Compare items with date ranges – show only if they overlap

Hi @edvardwikstrom,

 

Here are some questions:
1. How would you get the a new Progect, from Gallery selection, or Form submit?

2. When filtering the Assigned project, are all the projects need to be filtered with?

 

Assuming the New Project is selected from Gallery1, and the filtered result for Assigned projects would be displayed in Gallery2, then the formula under Gallery2 items property should be:

Filter(AssignedProjectsTable, 
           or( Gallery1.Selected.StartDate < StartDate < Gallery1.Selected.EndDate,
               Gallery1.Selected.StartDate < EndDate   < Gallery1.Selected.EndDate
               )
)

Regards,

Michael

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

Re: Compare items with date ranges – show only if they overlap

Hi Michael! 

 

Many thanks for your reply!

 

The date span i filter with comes from two text boxes that the user either can fill in, or it is sent over from a gallery.

 

To get around delegetion issues I have created a calculated INT column based on the date column in my SQL database. So that "2018-01-10" is a value "20180110".

 

It seems to be working now by tweaking your suggestion a bit. 

 

Filter(
         '[dbo].[Database]';SearchConsultant.Text in Consultant; SearchCustomer.Text in Customer; SearchTitle.Text in Title;
          
Or
(Value(SearchStartDate.Text) <= StartdateINT && StartdateINT <= Value(SearchEndDate.Text); 
Value(SearchStartDate.Text) <= EndDateINT && StartdatumINT <= Value(SearchEndDate.Text))
)

(Note that with swedish language "," is changed to ";")

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

SecondImage

PowerApps Monthly Community Call

Next Wednesday, September 18th at 8am PDT

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

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

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Users Online
Currently online: 224 members 4,764 guests
Please welcome our newest community members: