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.

SecondImage

PowerApps Monthly Community Call

Next Wednesday, September 18th at 8am PDT

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: 49 members 4,317 guests
Please welcome our newest community members: