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

Re: Evaluate to see if two date fields are in range of any existing records in the common data service

Hi Kris Dai,

 

Just to confirm, there are four end dates in the last two OR statements, is that correct? Or should there be a start date somewhere inbetween?

 

EndDates.jpg

 

If the above is correct, its still allowing for time periods to cross over those time periods:

 

Third Attempt.jpg

 

As you can see from the above, I should not be able to enable the button when the start and end dates cross over existing record data within that time period.

 

Regards,

Barry Francis

Highlighted
Barry_Francis
Level: Powered On

Re: Evaluate to see if two date fields are in range of any existing records in the common data service

Hi there,

 

I am trying a few things to resolve this, one of the solutions I'm looking at is to create a collection of dates inbetween the two selected dates, then use a lookup to see if there is a match across the one gallery to the other.

 

Here is the collection I am setting on change of the two date pickers:

 

ClearCollect(
DateRange,
AddColumns(
FirstN(
[0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32],
DateDiff(StartDate.SelectedDate, EndDate.SelectedDate, Days) + 1),
"Day",
"Day " & (Value + 1),
"Date",
DateAdd(StartDate.SelectedDate, Value, Days)
)
)

 

Then I am setting the values into a gallery:

 

Alternate Solution.jpg

 

Then I am setting a label to try and work in a lookup to see if any of records on the individual date gallery matches one of the dates in the other gallery, but am struggling to get that IF formula correct:

 

If(!IsBlank(LookUp(SingleDateGallery.AllItems, Value(Text(Date, "[$-en-US]yyyymmdd")) = Value(Text(DateFromGallery, "[$-en-US]yyyymmdd")))),"Date Found","No Dates Found")

 

SingleDateGallery --> gallery with each date listed out

Date --> The date each collect is returning

DateFromGallery --> The date labels in the gallery with the from and to dates.

 

Regards,

Barry Francis

Barry_Francis
Level: Powered On

Re: Evaluate to see if two date fields are in range of any existing records in the common data service

Hi Kris Dai,

 

I played around with the code you sent me and found the solution:

 

If(
!IsBlank(LookUp('Leave Requests', Value(Text(crbb9_datefrom, "[$-en-US]yyyymmdd")) <= Value(Text(StartDate.SelectedDate,"[$-en-US]yyyymmdd")) && Value(Text(crbb9_dateto, "[$-en-US]yyyymmdd")) >=
Value(Text(StartDate.SelectedDate, "[$-en-US]yyyymmdd")))) ||
!IsBlank(LookUp('Leave Requests', Value(Text(crbb9_datefrom, "[$-en-US]yyyymmdd")) <= Value(Text(EndDate.SelectedDate,"[$-en-US]yyyymmdd")) && Value(Text(crbb9_dateto, "[$-en-US]yyyymmdd")) >=
Value(Text(EndDate.SelectedDate, "[$-en-US]yyyymmdd")))) ||
!IsBlank(LookUp('Leave Requests', Value(Text(crbb9_datefrom, "[$-en-US]yyyymmdd")) > Value(Text(StartDate.SelectedDate, "[$-en-US]yyyymmdd")) && Value(Text(crbb9_dateto, "[$-en-US]yyyymmdd")) <
Value(Text(EndDate.SelectedDate, "[$-en-US]yyyymmdd")))),
DisplayMode.Disabled,DisplayMode.Edit)

 

The start date must be less than the from date and the end date must be greater than the to date.

 

Thanks for all the assistance.

 

Regards,

Barry Francis

Helpful resources

Announcements
Better Together’ Contest Finalists Announced!

'Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

thirdimage

Power Apps Community User Group Member Badge

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

sixthImage

Join THE global Microsoft Power Platform event series

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

thirdimage

Microsoft Business Applications Virtual Launch

Join us for the Microsoft Business Applications Virtual Launch Event on Thursday, April 2, 2020, at 8:00 AM PST.

thirdimage

Community Summit North America

Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (8,599)