How to Query a SharePoint List from Another SharePoint List Using Microsoft Flow?

Why query multiple SharePoint lists from Microsoft Flow?

If you are wondering why on earth would I think of using Microsoft Flow to query one list from another (when there is at least one common metadata to connect line items of both lists), I’ve the same clichéd reason – being a Power User, dependent on ready to use functions, I make a perfect case for Microsoft Flow.

So, I have a driving list which triggers the Microsoft Flow, and in turn using its line items, I can query the other connected list(s).

Even easier to demonstrate, let me refer to the driving list as Main List and the other list as Connected List. Below is a view of how my sample lists’ structures and data look like:

1. Main List – Owners

List Structure

List Data

2. Connected List – Task Status

List Structure

List Data

What makes a basic Use-Case to use this approach?

To me, referring to my sample lists, the simplest reason is that my connected Task Status list contained multiple rows for each owner and I preferred using my Owners list (with unique users) to trigger my Flow using the Email Type field. So, with a selected set of data in the Owners list, I trigger my Flow, which in-turn further selects data in the Task Status list.

If the above don’t form a strong case, revisit below how a Flow can be run on all items of a SharePoint list, in this case if we are thinking of using just the Task Status list:

• Get Items (triggered on Recurrence/Schedule to fetch all items of a list)
• When an existing Item is modified (modify each item of the list to check if it passes the Flow condition)

With about the above trigger options, I ran into a case where my Task Status list had 5000+ items, so to use Trigger #2, modifying each item of this list wasn’t practical enough. For Trigger #1, I did try setting up a recurrence, but that would mean each time when I need to perform some action, I’ve to switch it ON and OFF to prevent the Flow from running on its own.

Now, that I believe I managed to produce my argument to convince you enough, let’s move on to creating the Flow.

You may also like: Plan customizations, solutions and apps for SharePoint

How to setup this Flow?

Since I already mentioned that I’m using the Owners list as the driving list, I trigger my flow when an item is created or modified (i.e. either a new line item is added to the list, or I’ve set the Email Type field, which I’m using to decide whether to trigger the flow, for an existing item).

In my example, I’ve tried to use the Choice column and the Person or Group fields for conditions, as I find them more complex than using Number or Single Line of Text fields, especially in the interest of users new to Flow.

So, while I’m using the Email Type field, you can see in the following images, that I actually use the Email Type Value to evaluate condition if it’s set to ‘Reminder’ for the modified line item.

My next step to fetch data from the Task Status list is dependent on the outcome of this condition, and for simplicity, I’ve used only the ‘If Yes’ (pass) condition.

Caution:

There’s a catch here – with the Get Items action, unlike the When Item is modified action, Flow doesn’t give you handy Dynamic Content options for the specified list to use in Filter Query field. Since I wanted to connect the ‘Team Member’ field of the Task Status list with the ‘Site Owner’ field of the Owners list, I had to manually type in the fields for Task Status list.

Another thing to note is to use the internal names for the dependent (Task Status) list. Here again, I’ve used the Choice and the Person or Group fields as I find them more complex to use than other field types. But here, instead of using the Choice field value like in Flow conditions, we rather use its Internal Name.

Since the Person or Group field is a nested (or complex) field containing a lot of other details, best is to use the EMail sub-field (in the specified format – ColumnInternalName/EMail) for comparing it with the Site Owner Email value of the Owners list.

Note:

You’ll be able to find a lot of explanations on a quick internet search as to why use the format ‘ColumnInternalName/EMail’ for Person or Group in the Filter Query. My personal check was the Power BI approach instead of a rote rule.

My last step to send a reminder email is dependent on the number of items returned from the above Filter Query.

On a closer look, Condition 2 is evaluated on the length of the value of items returned. If there are any rows (i.e. number of rows >= 1) in the Task Status list that satisfy the condition in the Filter Query, I send an email(‘If Yes’ condition).

So, basically, I use the length(collection) from Expression with value as the Dynamic Content so that my reminder emails are sent only if there are any ‘In Progress’ items in the Task Status list.

All Set?

Yes! My final flow looks like the one below.

And I’m finally ready to trigger my Flow by setting the Email Type in the Owners list.

On an Ending Note

Apart from the aforementioned reasons justifying why I had to drift to this approach, I also like the fact that the query filters on field values work even when the view doesn’t contain those columns/fields, which makes the view quite manageable for display to end users, while still using the query in the URL.

Each day that I use Flow, I find it different – either old issues being fixed, or new ones encountered. While I’m still exploring more topics to blog, do send me your comments and suggestions or reach out to us!