cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Muchiri_Steven
Helper I
Helper I

FLOW FOR GENERATING CSV FOR ONLY THE CURRENT WEEK NUMBER

I have a Flow linked to a sharepoint list which has a WeekNo column, the current Flow extracts everything in the list but I would like to filter as for the current WeekNumbers only.

I'm using a "Get Items" sharepoint action but I'm trying to filter using the ODATA field.

Is there any syntax to filter using the current WeekNos.

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions

Thanks,

I saw your comment about "Filter Array", can that help

View solution in original post

5 REPLIES 5
v-xida-msft
Community Support
Community Support

Hi @Muchiri_Steven,

 

Could you please share more details about your SharePoint list?

Do you want to filter record using OData Query within the "Get items" action?

 

I suppose that the WeekNo column within your SharePoint list store all week numbers of this year, is it true? I have created a SharePoint list on my side and the data structure of it as below:1.JPG

Note: The WeekNo column is a Number type column, the TaskName and Executor column are both Single lines of text type column. Current week is the 14th week of this year.

 

I have made a test on my side and please take a try with the following workaround:

  • Add a proper trigger, here I use Flow Button trigger.
  • Add a "Get items" action, specify Site Address and List Name. The Filter Query field set to following formula:
WeekNo eq If(...)

The If(...) expression within above formula read as below:

If(greater(div(float(dayOfYear(utcNow())),7),div(dayOfYear(utcNow()),7)),add(div(dayOfYear(utcNow()),7),1),div(dayOfYear(utcNow()),7))

Note: Use the above If() expression to get current week nuumber of this year.

 

Image reference:2.JPG

The flow works successfully as below:3.JPG

 

 

More details about using OData query expression within SharePoint, please check the following article:

https://docs.microsoft.com/en-us/sharepoint/dev/sp-add-ins/use-odata-query-operations-in-sharepoint-...

 

 

Best regards,

Kris

 

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Kris,

Thanks for the response. The challenge is that the WeekNo column is a calculated column based on a Date Field column the list.

I got the below prompt/error

"The field 'WeekNo' of type 'Calculated' cannot be used in the query filter expression."

Is it a must to be a number field?

Thanks in advance

Hi @Muchiri_Steven,

 

Yes, the Calculated column could not be used within OData Query expression in Microsoft Flow currently.

 

If you want to filter items using OData query based on Calculated column within Microsoft Flow currently, I afraid that there is no way to achieve your needs in Microsoft Flow.

 

You could consider take a try to change the data type of the 'WeekNo' column into Number type on your side.

 

Best regards,

Kris

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks,

I saw your comment about "Filter Array", can that help

View solution in original post

Hi @v-xida-msft 

 

I'm sorry for ressurecting this but i tried your expression below, and mondays still render as last week.

I used the expression in two flows, one which send a mail on friday telling the person who is assigned next week and one which sends a mail on monday the following week telling the asignee what to do that week.

I look up weeksnumbers from sharepoint which is working great.

In Denmark our week starts with monday.

 

This monday, the 25th of february the weeknumber should be 9, but the below expression rendered it as 8.

If(greater(div(float(dayOfYear(utcNow())),7),div(dayOfYear(utcNow()),7)),add(div(dayOfYear(utcNow()),7),1),div(dayOfYear(utcNow()),7))

ThisWeek.PNG

 

Any pointers are much appreciated!

Cheers!

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Users online (1,214)