cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SylvieLet17
Level 8

Filter SharePoint List based on Calculated Date Field

Hi all, 

 

This should be really simple but it's not working.

I need to do a lookup on a SharePoint list. 

The formula should be LookUp(MyData, WeekdayColumn="Monday" && WeekStartColumn=WeekStartVariable, Patients)

OR alternatively, I could collect the data like this:

ClearCollect(Collection1, Filter(MyData, Weekday="Monday",WeekStart=WeekStartvar))

 

WeekStartColumn is a calculated sharepoint column that displays the date of the beginning of the week based on another date column. The formula is DateColumn-WEEKDAY(Date;3)

The column type is Date and it displays the date as dd-MM-yyyy

 

WeekStartVariable is a variable in the app that is collected when the app is opened. The formula for that is:

Set(WeekStartvar, Today()-Weekday(Today(),3))  and it displays as MM/dd/yyyy

 

there will only be one list item where WeekdayColumn="Monday" && WeekStartColumn=WeekStartVariable 

I'm not getting any value, and no error message either. 

 

The formula works when using JUST weekday=Monday or any other filters, but not with WeekStart

Any advice?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
TimRohr
Level 10

Re: Filter SharePoint List based on Calculated Date Field

Couple of thoughts...

I believe Text() can format to a short date output.

 

I think in your position, experiencing unexpected results from your DateAdd() statement, I would do one of two things...

1) I would do build my comparison logic to look at the Text() output of each side... that is:

Text(WeekStartVar,DateTimeFormat.ShortDate) = Text(WeekStartColumn,DateTimeFormat.ShortDate)

...OR...

2) I would build my logical comparison to explicitly test the component parts of the variable:

Year(WeekStartVar) = Year(WeekStartColumn) && Month(WeekStartVar) = Month(WeekStartColumn) && Day(WeekStartVar) = Day(WeekStartColumn)

 

Either of those should get the results you're looking for. It just depends on what your aesthetic is.

6 REPLIES 6
TimRohr
Level 10

Re: Filter SharePoint List based on Calculated Date Field

I had trouble with LookUp() not finding the data I was looking for, though the data was there. Can you do a First(Filter()).field construction and see if you get the return you are looking for?

 

...report back and we can keep troubleshooting.

SylvieLet17
Level 8

Re: Filter SharePoint List based on Calculated Date Field

Thanks Tim, I think this is heading in the right direction. 

One other problem I'm having is that my WeekStartVar is changing from just a date value to a DateTime value .

 

I have a button called "previous week" which subtracts 7 days from the variable:

Set(WeekStartvar,DateAdd(WeekStartvar, -7))

Randomly it seems to change it from 12/12/2018 to 12/12/2018 12:00

Do you know how to stop it from doing this? Or maybe to enforce the dd/MM/yyyy format?

TimRohr
Level 10

Re: Filter SharePoint List based on Calculated Date Field

If you explicitly tell the DateAdd() function to use Days, does it still happen?

 

DateAdd(WeekStartVar,-7,Days)

SylvieLet17
Level 8

Re: Filter SharePoint List based on Calculated Date Field

Yeah!! I tried that and it's still the same! 

Highlighted
TimRohr
Level 10

Re: Filter SharePoint List based on Calculated Date Field

Couple of thoughts...

I believe Text() can format to a short date output.

 

I think in your position, experiencing unexpected results from your DateAdd() statement, I would do one of two things...

1) I would do build my comparison logic to look at the Text() output of each side... that is:

Text(WeekStartVar,DateTimeFormat.ShortDate) = Text(WeekStartColumn,DateTimeFormat.ShortDate)

...OR...

2) I would build my logical comparison to explicitly test the component parts of the variable:

Year(WeekStartVar) = Year(WeekStartColumn) && Month(WeekStartVar) = Month(WeekStartColumn) && Day(WeekStartVar) = Day(WeekStartColumn)

 

Either of those should get the results you're looking for. It just depends on what your aesthetic is.

SylvieLet17
Level 8

Re: Filter SharePoint List based on Calculated Date Field

YES this is it! You did it!

 

I went with option 2. 

Strangely my WeekStartColumn was registering in PowerApps as a text column, even though it is a calculated date field. so eventually my lookup (very long and complex) ended up being:

Lookup(MyData, weekdaycolumn="Monday" && clinic=varclinic && Year(DateValue(Weekstartcolumn))=Year(WeekStartVar) && Month(DateValue(Weekstartcolumn))=Month(WeekStartVar) && Day(DateValue(Weekstartcolumn))=Day(WeekStartVar))

PHEW

THANK YOU

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

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

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Users Online
Currently online: 50 members 4,907 guests
Please welcome our newest community members: