cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
calvares
Continued Contributor
Continued Contributor

Odata filter query - how to filter on Created By

I have a SharePoint Get Items which I need to add a filter to as there's too many bytes to process if I do it without one.

I already have Order By Author asc. and I want the filter to also pick up say a section of the items where the Created By or Author begins with A up to where they begin with M. 

As the next part of my flow is to create a HTML file they need to be in alphabetical order so I can amalgamate the files after.

 

Author ge 'A' and le 'M' doesn't work and nor does replacing the letters with the actual names. 

There are about 140 items in the list so I don't want to be adding ID numbers individually or anything. 

Any idea how I can do this?

1 ACCEPTED SOLUTION

Accepted Solutions
eliotcole
Impactful Individual
Impactful Individual

OK, @calvares  ... I just lost a massive post. So I'll be brief because I'm seething.

You may find a complicated ODATA solution, but this is pure SharePoint and Power Automate.

Apologies for the brevity, but I'm so annoyed with this forum interface I shouted aloud in this office.

 

---

Part One - Process Inputs

Create a number field in the list, I hid mine, ensure the decimals are set to 0 and there are no thousands separator.

Nom Nom NumNom Nom Num

 

Now create a flow to process any new items to the list with two variables:

Ooh, look what you made me doOoh, look what you made me do

 

I had explained the two variables / expressions there but I can't do it again.

 

firstCharVAR gets the first letter:

 

substring(triggerOutputs()?['body/Author/DisplayName'], 0, 1)

 

firstCharVAR finds out the index in the alphabet of that letter with a being 0:

 

indexOf('abcdefghijklmnopqrstuvwxyz', toLower(variables('firstCharVAR')))

 

 

If you wanted to just put that all in the Update item SharePoint action field, you would combine them thusly:

 

indexOf('abcdefghijklmnopqrstuvwxyz', toLower(substring(triggerOutputs()?['body/Author/DisplayName'], 0, 1)))

 

 

Part 2 - ODATA Filter

This is much easier now, you just use this ODATA formula, where "lt" means less than, and "N" is the 13th number in the list:

 

aToMnum lt 13

 

Ohhhhhh ... DATA!Ohhhhhh ... DATA!

 

View solution in original post

3 REPLIES 3
eliotcole
Impactful Individual
Impactful Individual

DELETE THIS POST, PLEASE, MODS.

eliotcole
Impactful Individual
Impactful Individual

OK, @calvares  ... I just lost a massive post. So I'll be brief because I'm seething.

You may find a complicated ODATA solution, but this is pure SharePoint and Power Automate.

Apologies for the brevity, but I'm so annoyed with this forum interface I shouted aloud in this office.

 

---

Part One - Process Inputs

Create a number field in the list, I hid mine, ensure the decimals are set to 0 and there are no thousands separator.

Nom Nom NumNom Nom Num

 

Now create a flow to process any new items to the list with two variables:

Ooh, look what you made me doOoh, look what you made me do

 

I had explained the two variables / expressions there but I can't do it again.

 

firstCharVAR gets the first letter:

 

substring(triggerOutputs()?['body/Author/DisplayName'], 0, 1)

 

firstCharVAR finds out the index in the alphabet of that letter with a being 0:

 

indexOf('abcdefghijklmnopqrstuvwxyz', toLower(variables('firstCharVAR')))

 

 

If you wanted to just put that all in the Update item SharePoint action field, you would combine them thusly:

 

indexOf('abcdefghijklmnopqrstuvwxyz', toLower(substring(triggerOutputs()?['body/Author/DisplayName'], 0, 1)))

 

 

Part 2 - ODATA Filter

This is much easier now, you just use this ODATA formula, where "lt" means less than, and "N" is the 13th number in the list:

 

aToMnum lt 13

 

Ohhhhhh ... DATA!Ohhhhhh ... DATA!

 

View solution in original post

calvares
Continued Contributor
Continued Contributor

This is brilliant, thank you @eliotcole. I've not set up the flow yet but ordering numerically works perfectly.

Sorry you lost your other post...happens to me lots too!

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,874)