cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ToRo
Frequent Visitor

Can't get RemoveIf to work with multiple conditions

Hey Experts,

 

I can't figure out how to use the RemoveIf function with multiple conditions. 

We have a collection with a date range that users can select from a calendar and it is filled like this:

 

 

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],
            DateDiff(Calendar_1.StartDate, Calendar_1.EndDate, Days) + 1),
        "Day",
            "Day " & (Value + 1),
        "Date",
            DateAdd(Calendar_1.StartDate, Value, Days)
    ,"Type",selectedType.type,"Hours","8","Person",_curUser));

 

 

Now I want to remove the weekend days and also matching holidays stored in a SP List "HolidaysGermany". I use RemoveIf like the following:

 

 

RemoveIf(DateRange, Date in HolidaysGermany.DateOfHoliday || Weekday(Date) = 1 || Weekday(Date) = 7);

 

 

After removing it does look like this (for example the range 4/9/2020 - 4/14/2020):

ToRo_1-1606400924461.png

The removal with weekday does work fine, but it does never remove the holidays (in this case 4/10/2020 & 4/13/2020). I belive it's a date format problem but I don't know how to fix it. The date in the SP list is a normal date column and it looks like (switching to date and time format doesn't help):

ToRo_0-1606400871535.png

Do you have any suggestions? Any help is appreciated!

 

Thanks and best regards

1 ACCEPTED SOLUTION

Accepted Solutions
ToRo
Frequent Visitor

Hey all,

 

after a lot of research and trying to change formats, I came upon this solution:

RemoveIf(DateRange, DateValue(Text(Date,"[$-en-US]mm/dd/yyyy")) in HolidaysGermany.DateOfHoliday || Weekday(Date) = 1 || Weekday(Date) = 7);

My Date in the SP-list seems like it's in a "german format", but I'm using Chrome always in englisch, so my Powerapps uses the US format for dates. So only a tiny change to the format worked very well!

 

Best regards

View solution in original post

3 REPLIES 3
gabibalaban
Dual Super User
Dual Super User

Hi @ToRo ,

 

If you watch carrefully on your DateRange collection values you'll see that your Date column have also a time attached. This means that you compare a Date (retrived from Sharepoint with 00:00 at time) with this value and of course its never been equals.

I have a hunch that your Calendar component returns a DateTimeValue for StartDate and EndDate.

Can you try:

RemoveIf(DateRange, DateValue(Text(Date,"dd.mm.yyyy")) in HolidaysGermany.DateOfHoliday || Weekday(Date) = 1 || Weekday(Date) = 7);

 

Hope it helps !

Hi @gabibalaban ,

 

thanks for this suggestion. Unfortunately it does not work, still the same behaviour. I tried to set locales as well:

RemoveIf(DateRange, DateValue(Text(Date,"[$-de-DE]dd.mm.yyyy")) in HolidaysGermany.DateOfHoliday || Weekday(Date) = 1 || Weekday(Date) = 7);

and also:

RemoveIf(DateRange, DateValue(Text(Date,"[$-en-US]dd.mm.yyyy")) in HolidaysGermany.DateOfHoliday || Weekday(Date) = 1 || Weekday(Date) = 7);

 

I also tried following:

ForAll(
    DateRange,
    If( CountIf(HolidaysGermany, DateOfHoliday >= ThisRecord.Date, DateOfHoliday <= ThisRecord.Date) > 0 || Weekday(ThisRecord.Date) = 1 || Weekday(ThisRecord.Date) = 7, Remove(DateRange, ThisRecord);
);

But this does also not work, since I operate on the same datasource, that is used in the ForAll.

 

Do you have any other suggestion?

 

Thanks and best regards

ToRo
Frequent Visitor

Hey all,

 

after a lot of research and trying to change formats, I came upon this solution:

RemoveIf(DateRange, DateValue(Text(Date,"[$-en-US]mm/dd/yyyy")) in HolidaysGermany.DateOfHoliday || Weekday(Date) = 1 || Weekday(Date) = 7);

My Date in the SP-list seems like it's in a "german format", but I'm using Chrome always in englisch, so my Powerapps uses the US format for dates. So only a tiny change to the format worked very well!

 

Best regards

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (5,743)