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

[Anonymous]

Hi PowerApps community,

I'm struggling on a requirement for an App I'm busy building to do the following:

• Before submitting a patch (create record), I need to determine if the dates selected already exist in the Common Data Service (CDS), if they do, prevent use of the button, otherwise allow the button to be selected (editable).
• Two date fields exist from a CDS new form, StartDate and EndDate (Renamed the date picker to these names respectively)
• CDS field is called DateFrom and DateTo respectively

Requirement:

Setting displaymode on my button, I need to determine if the dates selected (StartDate and EndDate) fall on or between any of the other dates in the CDS with regards to DateFrom and DateTo.

I.e. if I select StartDate --> 17/10/2019 and EndDate --> 18/10/2019, check to see if there are any other records in the CDS entity that fall on or between that date. If it does, set displaymode to disabled, otherwise set displaymode to edit.

Does anyone have any sort of formula that would help in this regard?

Regards,

Barry Francis

[Anonymous]

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?

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

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

[Anonymous]

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:

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

[Anonymous]

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

[Basha_Shaik]

Hi @v-xida-msft

 

Same Associate will not work in another project in between previous start date and end date 

[Basha_Shaik]

Hi @Anonymous 

I have same issues . I have project list in SharePoint

Same employee/associate cannot work in between the date range of the previous start date and end date 

For example 

I add project to the employee whose name is basha  and start date 1-jan -2022 and end date 10-jan-2022 and i add another project to the same employee and date range should be 11-jan-2022 and end date 15-jan-2022. If I give 8-jan-2022 and 15-jan-2022 in this case I want to throw error this in the range of previous date

 

[cardinalnight]

@Mr-Dang-MSFT - this was excellent and saved me a lot of stress. The diagram is a great complementary visual aid. Throwing huge kudos to you. Thanks.