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

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

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.

 

ButtonExample.jpg

 

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

 

Regards,

Barry Francis

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

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

Hi @Barry_Francis ,

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,

Community Support Team _ Kris Dai
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

12 REPLIES 12
PowerApps Staff Mr-Dang-MSFT
PowerApps Staff

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

@Barry_Francis, 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:

 

image.png

 

  • 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.

Community Support Team
Community Support Team

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

Hi @Barry_Francis ,

Could you please share a bit more about your scenario?

Do you want to check if the selected date value ranges (StartDate and EndDatefall 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,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 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.

 

Error1.jpg

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

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,

 

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.

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,

 

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

Community Support Team
Community Support Team

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

Hi @Barry_Francis ,

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,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 Kris Dai,

 

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

Error2.jpg

 

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?

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 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?

 

IssueAtHand.jpg

Community Support Team
Community Support Team

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

Hi @Barry_Francis ,

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,

Community Support Team _ Kris Dai
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

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

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

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (5,532)