cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
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
New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

MPA Community Blog

Power Automate Community Blog

Check out the community blog page where you can find valuable learning material from community and product team members!

Users online (25,919)