How to check a list of dates in a collection against a sharepoint list?

[TeeTong]

Hi,

I am trying to compare a list of dates in the PowerApps against a list of dates in a Sharepoint list using Lookup. Below is my app set-up.

I have 2 date pickers (start date and end date). 

For example, i selected start date as 16/03/2020 and end date as 20/3/2020.

I have used a Forall to loop through these 5 dates and check if any of these dates (16/03, 17/03... 20/03) exists in my PublicHolidays list.

The command is 

ForAll(FirstN(Days,Value(lblNoDays.Text)),
Not(IsBlank(LookUp(PublicHolidays, (Dates) = DateAdd(drpLeaveStartDate.SelectedDate,Value(Title))))))

Note: the FirstN command allow me to know how times must i loop through based on how days between start and end date.

However the command does not seem to work as it always return all true or all false based on the first date in the collection. 

Any help will be appreciated!

[v-siky-msft]

Hi @TeeTong ,

The issue I think is caused by 'Title' column in Days, please share more information about Days, Title in your code.

I think the Days should be a Table store number form 0 - +∞. 

I create a collection 'colDays' for the demo, please try this:

ClearCollect(colDays,{Title:0},{Title:1},{Title:2},{Title:3},{Title:4}); 
ClearCollect(colResult,ForAll(FirstN(colDays,DateDiff(drpLeaveStartDate.SelectedDate,drpLeaveEndDate.SelectedDate,Days)),Not(IsBlank(LookUp(PublicHolidays, Dates = DateAdd(drpLeaveStartDate.SelectedDate,Value(Title)))))))

Sik

[TeeTong · Accepted solution]

Hi @Sik ,

My Days table contain a single column comprising of running number from 0 to 365 so it should tally with what you mentioned.

However i managed to resolve my issue. It seem that checking directing against the list does not work properly.

I created another collection that pull the number across from Days table and check against the collection. This works! 🙂

Thanks!