Need to filter a SharePoint List by a field value that equals an A/D field value for the signed in user

[Anonymous]

Hi everyone,

I have a very simple SharePoint online List that has a string value field called "STNUM" that contains a value associated with a location such as "018", "457", "123", etc.  Always 3 digits with leading zeros required.  That's the first part.

The second part is User dependent.  The Azure A/D field "Office" for every User also contains the location value for the user as the same string value like "123" that tells us the user is associated with all of the SharePoint list items where "STNUM" equals the A/D "Office"value. 

So, what I want to do is set a recurring Flow on the SharePoint List that runs every day, or every hour and then filters the list to only show the items where the logged in User A/D "Office" field equals the "STNUM" field of the List Items. 

I have tried a zillion different ways to get there but nothing works.  I must be missing something simple.  Or, this is just too complicated for a stand alone Flow.

I have been able to get this to work if I use Power Apps and it is pretty nice.  The issue is the per User cost associated with Power Apps vs Flows are free.  I need to just use Flow for this one.

Any and all help will be greatly appreciated.  Thanks.

Ed

[v-bacao-msft]

Hi @Anonymous ,

Could you provide more details about this field? Perhaps you can provide an example to illustrate the filter.

Basically we can use the Get items action to get the items first, then we can filter the items through the Condition/Filter Query/Filter array, provided we need to know what filter conditions you want to set.

Best Regards,

[Anonymous]

Hi,

Thanks for responding so quickly.  Here is an example:

1. User "John557@Company.com" logs into his Office365 account and browses to the main SharePoint Intranet page.  As part of his Azure A/D profile, Location "557" is saved in the "Office Location" field to identify where he is, location "557".  "Mary009@Company.com" is located in "Office Location" "009".  Leading zeros are required for the field.

2. Now that John or Mary are logged in and browsing to the SharePoint page, one of the web parts on the page surfaces the SharePoint List which contains one or more entries where the List field "STNUM" corresponds to their respective location code ("557" or "009" in this instance.).

3. The list is not very big (always less than 500) but could change many times daily.  The number of locations is less than 100 and is fairly static.

4. I would simply like to be able to filter the list web part view so that John only sees list items with his location in the "STNUM" field, and Mary for hers.

As I said in my initial email I think it should be simple but I'm afraid I don't know enough about FLOW, and especially expressions for Flow, to get this to work.

The closest I have gotten is with a FLOW that does this:

1. Sets the Recurrence to every 1 hour.

2. Get my profile V2) in order to see the Azure A/D fields.

3. Initialize a variable "TLoc" of type string with a Value "Office Location" from the Azure profile.  So far so good.

4. Next I set SharePoint Get Items to the correct Site Address and List Name.  I know I need to set a Filter Query but I can't figure out how to word the expression. Under Dynamic content I only see the Variable I created "TLoc" and the Get my profile (V2) fields.  The SharePoint List fields do not show for me to pick.

My question is what am I doing wrong or missing?  Thanks again for helping.  If I can get this to work, it will be a life saver.

Ed

[MattWeston365 · Accepted solution]

Hi @Anonymous 

With a filter query, generally you will manually type the name of the field, the one that you're filtering on, into the filter query and then use dynamic content for the comparison.

E.g. I am going to assume that the internal name for your filter is called STNUM (always be aware that the display name and internal name aren't always the same). In this case, my filter would look like:

STNUM eq '<Location from Dynamic Content>'

Notice the use of eq, which indicates that the comparison is going to be equal to. The dynamic content for the location must be surrounded by single quotes as we are comparing a string. If you need more help with filter queries then try this video: https://www.youtube.com/watch?v=_MPtIr9FqNQ

If I'm honest, I'm not certain how the Flow is going to help with the filtering within the SharePoint UI, unless you're changing the permissions?

[Anonymous]

@MattWeston365 

You are the best!!  This was what I needed to see.  I was missing the single quotes around the Dynamic Content field.  As soon as I put them in, the flow succeeded and the correct List Items showed.

Thank you so much.

Ed