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

[Mr-Dang-MSFT]

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

[v-xida-msft]

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,

[Anonymous]

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.

[Anonymous]

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.

[Anonymous]

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

[v-xida-msft]

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,

[Anonymous]

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?

[Anonymous]

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?

[v-xida-msft · Accepted solution]

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,