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
Solved! Go to Solution.
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:
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:
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:
The flow works successfully as below:
More details about using OData query expression within SharePoint, please check the following article:
Best regards,
Kris
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
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))
Any pointers are much appreciated!
Cheers!
Join digitally, March 2โ4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!
Power Platform release plan for the 2021 release wave 1 describes all new features releasing from April through September 2021.
User | Count |
---|---|
90 | |
50 | |
36 | |
30 | |
27 |
User | Count |
---|---|
75 | |
60 | |
50 | |
42 | |
38 |