Power Automate - Get SharePoint items using Created By/Modified By

-

Recently I had an interesting request where someone was trying to filter items based on the Created By field in SharePoint using Power Automate. I thought, that should be pretty straight forward, make a claim or something similar and I headed to Google.

Surprisingly there's not actually that many hits and it took quite a bit of digging. The answer once you realise is pretty straight forward.

SharePoint holds a hidden(ish) list, the "User Information List" you can view it by adding the following to a Site Collections URL: 

/_catalogs/users/simple.aspx

This list holds all the "Ensured Users" on the site - you can google that one if you want to find out more. It also holds the information on which you can use in the filters for People Fields. Here are all the useful filters you can use:

"ItemInternalId":"11",
"ID":11,
"Title":"Martin Pyman",
"Name":"i:0#.f|membership|martin@marnotechnology.com",
"EMail":"martin@marnotechnology.com",
"SipAddress":"martin@marnotechnology.com",
"IsSiteAdmin":true,
"Deleted":false,
"UserInfoHidden":false,
"Picture":"https://marnotechnologycom-my.sharepoint.com:443/User%20Photos/Profile%20Pictures/martin_marnotechnology_com_MThumb.jpg",
"FirstName":"Martin",
"LastName":"Pyman",
"WorkPhone":"123456",
"UserName":"martin@marnotechnology.com",

Keep in mind the Power Automate query uses the column internal names, which are Editor and Author respectively. The other part is how do you query into those People Fields, turns out all you need to use is a forward slash:


Hope it helps!

Comments

  1. MarownIOM26 January 2022 at 11:46

    Thanks for the article. I have a smiliar issue.
    My flow executes when a new record is added to a list.
    I have another list where I need to lookup my additional user settings. (Like IsApprover)
    This is stored in a list I called User with a field named User which is a "Person/Group" field.

    I need to lookup the extra user data based on the user who created the new record.
    I've tried many permutations but the OData returned is either empty or I get an error saying the field doesn't exist.

    Here's some of the filer queries I've tried:
    (where is the dynamic content of the new list item)
    User/Email eq ''
    Author/Email eq ''
    User.Email etc...
    Author.Email
    User
    Author

    None of these work. There should be an easy way to lookup a user in another list...
    What do you think??

    ReplyDelete
  2. Martin Pyman19 July 2022 at 03:45

    You want to use Author/EMail eq '' note the capital M - they are case sensitive!

    ReplyDelete
  3. Rizvan19 December 2023 at 12:29

    Hey guys, I want to filter the query in Power Automate where Created by is not equal to Modified by. Any thoughts how can I do it pls?

    ReplyDelete

Post a Comment

Popular posts from this blog

Use Power Automate to create a Document Set – The easy way!

-
Image
NOTE: This makes a “pseudo” document set, use the VTI bin method to create a full blown document set, however perhaps some of the steps could be useful to you. I’ve recently been working with improving some workflows which use Document Sets and stumbled into a problem. Online when you search for “Document Sets using Power Automate” you get posts such as:  https://karinebosch.wordpress.com/2018/11/08/microsoft-flow-create-a-document-set/ https://knowhere365.space/microsoft-flow-sharepoint-document-set-creation/ Now these posts have helped a lot of people but there is one drawback to them and most of the others I can find online. They rely on the use of _vti_bin (sometimes referred to as SP 2010 REST API) If you don’t know much about the history of VTI go check this out: https://www.spjeff.com/2009/03/13/history-of-_vti_bin/ It’s pretty vintage now. The customer I’ve been working with has a handful of SharePoint 2010 workflows we discovered using the SharePoint Modernisation Scanner  
Read more

Power Automate - Update SharePoint item metadata using only variables

-
Image
 When you use the inbuilt Power Automate SharePoint "Update item" or "Update file properties" and want to use a variable for that, you lose all the prepopulated columns: For general quick Power Automates - "Update item" is fine, but if you work with other developers or promote these across Power Platform Environments and hook them up to different lists/libraries, you may want to be able to quickly set that information at the top of the Automate. Not have to scroll through tens/hundreds of steps to change the target list URL everywhere. This is where "Send an HTTP Request to SharePoint" comes in, you just have to know how to use the SharePoint REST API, or follow my guide below. My Target library has two additional metadata columns which I'll show you how to update, "Document Type" and "Item Types", the latter being a choice field. For the example below I show what this might look if you are passing an Id of an item from a
Read more