This seems like such a simple thing to do. Select a single name/value pair from a SharePoint list used as an app configuration source. I do this all the time with workflows so I can externalize email addresses, workflow participants, etc. But in Microsoft Flow the rules have changed. Here are a couple of design templates that can be used to get those name/value pairs storing your workflow configuration data.
In MS Flow, you’ll find two SharePoint actions: Get Item and Get Items. If you have an ID for a SharePoint list item your problem is solved. Simply use Get Item and supply the list item ID. This is handy when you’ve created a new item and need to reference it (update or retrieve it) during the flow execution. It’s not quite so handy when you need to reference an item from another list and you don’t have the item ID.
Now on to Get Items. Get Items returns an array of all the items selected from the SharePoint list, even if you narrow down your selection to a single item. Once you get this “collection” you can iterate through it using an Apply to each loop to select the item you want. I would argue, however, this is less efficient than selecting a single value and either referencing it with a first() function or by its index of [0]. You can actually see this difference in the execution of the workflow, as the Apply to each loop takes significantly more time to set up and execute. Here are some examples.
There are four examples and I think I like example #4 the best.
Example #1
Initialize your variable.
Using the Get Items action, select your site and list and create a Filter Query to select the item from your list. This assumes the Title (the name part of the name/value pair) is unique. My filter query: Title eq ‘AdminFinalApprovalEmail’, will select a single item but, just for grins, I also add the Top Count of 1 just to make sure.
Here’s what my underlying SharePoint list AppConfig data looks like. It contains name/value pairs. The Title contains the name and a column called Value contains the value. I guess I could’ve been more imaginative on the column names, like Fred and Ethel, but this works just fine. 🙂
Now I add a Set Variable action. I’ve selected my variable I initialized earlier, AdminFinalApprovalEmail. Notice how I rename my actions to something long and descriptive. That’s from years of programming and trying to make my code self-documenting. My new name is “Set variable AdminFinalApprovalEmail.”
For the Value I use this expression:
body(‘Get_items_Filter_by_AdminFinalApprovalEmail’)?[‘Value’]?[0]?[‘Value’]
The body() function references my renamed SharePoint Get Items action. Note that flow replaces spaces with underscores: Get_items_Filter_by_AdminFinalApprovalEmail. Now comes the somewhat confusing part.
The first ?[‘Value’]?[0] says to get the zeroth [0] indexed item from the Value portion of the body of the action Get_items_Filter_by_AdminFinalApprovalEmail. There is only one item retrieved from the SharePoint list and this object contains all the fields retrieved for the item identified by “AdminFinalApprovalEmail.”
The next part ?[‘Value’] says to select the field named Value from the SharePoint results. This is a bit confusing because I named the column Value which is the same name that Flow uses for its “Value.” If the field name in SharePoint was “Ethel” it would look like this:
body(‘Get_items_Filter_by_AdminFinalApprovalEmail’)?[‘Value’]?[0]?[‘Ethel’]
Hopefully you get it.
Example #2
Now for the next way to do the same thing. In this case we’ll adjust the expression to use the first() function. I’m using a new variable called AdminFinalApprovalEmailFirst in this example. I am still referencing the same SharePoint results retrieved using Get Items with the filter applied.
This is what the expression looks like:
first(body(‘Get_items_Filter_by_AdminFinalApprovalEmail’)?[‘Value’])?[‘Value’]
We reference the same body() data but apply the first() function to the first [‘Value’] on the SharePoint data. This essentially is referencing the [0] index, since it’s the only one that was retrieved. Note the position of the parenthesis for the first() function! We then select the SharePoint Value field from the SharePoint results using the second ?[‘Value’] selector.
You’ll find these to be very fast, as a single item is retrieved from SharePoint and assigned without a loop.
Example #3
Now to show the for-each method. Here’s an overview of the process.
In this example, I’m using Get Items without any renaming of the action. Note there is no Filter Query applied, so all SharePoint items are retrieved from the list. Since this list is pretty small (< 10 items) it’s no big deal retrieving all the items.
An “Apply to each” loop is used that iterates through the Value data. The “value” object from Get_Items contains all the SharePoint list items; in this case there are eight items in the list. It is essentially the body function: body(‘Get_Items’)?[‘value’].
A condition action is used to filter the results. In this case we are looking for items where the Title is equal to the string “AdminEmailCC.” There is only one in the list.
Assuming it’s found in the list, the “yes” branch sets the variable value. The SharePoint item field named “Value” is selected (it has the email address). Note the code that goes along with this:
items(‘Apply_to_each’)?[‘Value’]
Now let’s look at the execution of all these options in a Flow test. Note that the “Apply to each” loop is the time hog.
The “Apply to each” has to cycle through eight items in the list.
Seven of the eight results do not satisfy the condition. Notice the Expression result=false.
The seventh item is the one we’re looking for, so Expression result=true. At this point we can set the AdminEmailCC variable.
Example #4
Now here’s another method that may be the best one of all. This method uses a single Get Items action and subsequently filters the SharePoint results with an Array Filter action.
Next is the action to filter the array. Value is the list of items from Get items. It should be pretty obvious we are filtering by the Title column to get the row where Title = ‘AdminEmailCC’.
Now the magic occurs. We will initialize a new variable using the expression:
json(string(first(body(‘Filter_AdminEmailCC’))))?[‘Value’]
Like the other solutions, we get the first (and only) item from the array. Then we convert it to a string so the json() function will work. Using the json() function we can select the Value field. And that’s it!
We get the value for our admin email.
The nice thing about this solution is it hits the SharePoint list only once. After all the results are retrieved from SharePoint, it’s simply a task of filtering the array to get the specific row and then initialize a variable with the specific field from the single valued array.
And to wrap it up, I send myself an email with the results.
Are you a user? 