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

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

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

Categories:

Building Power Apps

I have the same question (0)

All responses (15)

Answers (1)

Sort by

Mr-Dang-MSFT Microsoft Employee on at

Like
a
(2)

Report
Copy link

Link copied!
@Anonymous, it sounds like you're comparing a range of dates (StartDate to EndDate) against a table containing date ranges in two columns: DateTo and DateFrom.

This is similar to seeing if one rectangle collides with another rectangle in a game. We need to check if one edge overlaps another and we can do that with 4 conditions, illustrated below:

StartDate cannot be >= DateFrom and <= DateTo

EndDate cannot be >=DateFrom and <=DateTo

If( IsEmpty( Filter(datasource, Or( And( StartDate>=DateFrom, StartDate<=DateTo ), And( EndDate>=DateFrom, EndDate<=DateTo ) ) ) ), DisplayMode.Edit, DisplayMode.Disabled )

Working from inside out, this means:

Filter the datasource where one of two conditions is true

A record has a DateFrom-DateTo range with the StartDate in between

A record has a DateFrom-DateTo range with the EndDate in between

If there are no records with the dates in between (the filter is empty), make the button clickable, else disabled.

Let me know if you need clarification on any part.

Was this reply helpful? Yes No
v-xida-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @Anonymous ,

Could you please share a bit more about your scenario?

Do you want to check if the selected date value ranges (StartDate and EndDate) fall on or between any of the other date ranges in the CDS with regards to DateFrom and DateTo field?

I have made a test on my side, please consider take a try with the following workaround:

Set the DisplayMode property of the Submit Button to following:

If( !IsBlank(LookUp('YourCDSEntity', DateFrom <= StartDatePicker.SelectedDate && DateTo >= StartDatePicker.SelectedDate)) ||
!IsBlank(LookUp('YourCDSEntity', DateFrom <= StartDatePicker.SelectedDate && DateTo >= EndDatePicker.SelectedDate)), DisplayMode.Disabled, DisplayMode.Edit )

Please consider take a try with above solution, then check if the issue is solved.

Best regards,

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi Mr-Dang-MSFT,

Correct, I'm checking to see if any date overlap is happening and ensure the button behaviours accordingly.

I added your formula to the button, but am getting the following error:
An error occured on the server. The left side of the 'GreaterThanOrEqual' operator must be a property of the entity.

Based on the above, you can see that I am using date pickers to determine the start and end dates.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi Kris Dai,

Thanks for the reply.

Yes, when setting a date from a date picker, I'd like the displaymode of the button to check and see that the date range between the two pickers does not overlap any existing date range from the CDS entity.

I tried your formala and found no errors, but no matter which date range I set, it would not influence the displaymode of the button even though I know there is a date range within the preset range:

I.e. I set the date range in the date pickers to 15/10/2019 (Start) and 18/10/2019 (End). There is a date range on the CDS that overlaps this: 14/10/2019 (Date From) and 18/10/2019 (Date To).

The formala as seen from my side:
If(
!IsBlank(LookUp('Leave Requests', crbb9_datefrom <= StartDate.SelectedDate && crbb9_dateto >= StartDate.SelectedDate)) ||
!IsBlank(LookUp('Leave Requests', crbb9_datefrom <= EndDate.SelectedDate && crbb9_dateto >= EndDate.SelectedDate)),
DisplayMode.Disabled,
DisplayMode.Edit
)

Hope this helps.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi Kris Dai,

Also to add to the above response, I am getting the following error:

The requested operation is invalid. Server Response: The right side of the "LessThanOrEqual" operator can't be NULL

Was this reply helpful? Yes No
v-xida-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @Anonymous ,

Could you please share more details about the error message within your formula?

Based on the error message that you mentioned, it seems to tell that the value you provided in right side of the "LessThanOrEqual" operator within your formula is NULL.

Please consider modify your formula as below:

If( !IsBlank(LookUp('Leave Requests', crbb9_datefrom < StartDate.SelectedDate && crbb9_dateto > StartDate.SelectedDate)) || !IsBlank(LookUp('Leave Requests', crbb9_datefrom <= EndDate.SelectedDate && crbb9_dateto >= EndDate.SelectedDate)), DisplayMode.Disabled, DisplayMode.Edit )

or

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

In addition, please make sure the date value displayed within your app is same as that in your CDS Entity itself.

Best regards,

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi Kris Dai,

If you see from the screen shot below, the start date (the right of the "LessThanOrEqual" operator) is not null:

The date from and to also are not null. I saved the formula into the app and reloaded the app, it appears to be working well now thank you.

Last question, I now need to add a filter to the above formula that restricts certain records where an employee code matches a variable I have, where would that fit into the formula? (Same table).

I tried the following:
If(
!IsBlank(LookUp('Leave Requests', crbb9_employeecode=InputEmployeeCode.Text, 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")))),
DisplayMode.Disabled,
DisplayMode.Edit
)

Should I be adding a filter before the lookup or should it work as a condition before the date region check?

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi all,

I see there is still another issue at play here outside of the proposed solutions that have been given. In the below image, examples 1, 2 and 3 are accounted for in the formula:

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")))),
DisplayMode.Disabled,DisplayMode.Edit)

However, example 4 is not accounted for. Is there a way around this?

Was this reply helpful? Yes No
Verified answer

v-xida-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @Anonymous ,

Do you want to add additional filter condition to restricts certain records where an employee code matches a variable?

If you want to add additional filter condition to restricts certain records where an employee code matches a variable, you could add your filter condition before the date region check within the LookUp function rather than add another Filter before the LookUp.

In addition, for the example 4, you should add another filter condition in your formula. Please consider modify your formula as below:

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(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(EndDate.SelectedDate, "[$-en-US]yyyymmdd")) && Value(Text(crbb9_dateto, "[$-en-US]yyyymmdd")) < Value(Text(EndDate.SelectedDate, "[$-en-US]yyyymmdd")))), DisplayMode.Disabled,DisplayMode.Edit)

Please consider take a try wiht above solution, check if the issue is solved.

Best regards,

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No