As I have posted before, Power Automate is just terrible. This is another of those situations where Microsoft had something "on the shelf" and just threw a new wrapper on it and called it done. The language used is annoying and derived from Azure Logic Apps, the functionality is inconsistent in how it treats certain kinds of complex objects/arrays (I mean, it is consistent in that it is consistently terrible). and the overall experience for version control is lacking (there is none).
However, here are a few tricks I keep coming back to time and again that I've pulled from other people's blogs but then time and again forget how to do just because I hate this platform so very much.
Building comma/semi-colon delimited strings from SharePoint query results
If you have a list of people in a SharePoint list and need to pull those matching values and build out a string say of email addresses that you'd like to send a note to, reset the security on something for, etc., then you'll find yourself doing a bunch of loops or recognizing the completely stupid/obtuse way that Power Automate forces you to do this.
Step 1) Getting your list from SharePoint (or anywhere)
The SharePoint GetItems() step will grab a list of "objects" (record in SharePoint) that Power Automate will treat as an Object() (if you grab the Body from the results of this step) or as an Array() (if you grab the Value from this results of this step).
However you do your query, just know that for most of these scenarios you will use the value that comes out of this step vs. the body.
Step 2) Select your column
Now add a new Select step (it is one of the Data Operations steps) to your Power Automate flow.
Select Add dynamic content for this step and put the value from your GetItems() step above into the From field.
Then in the Map field, click the icon to the right that will show a hint saying Switch Map to text mode.
item()?['directorEmail']
The blue text above should be changed to the correct column name from SharePoint (pray to whatever being you think might help out that nobody changed the name of that column after creation - otherwise you'll need to troubleshoot below).
Click Ok (or Update...I can never tell when Power Automate is going to change modes).
Now the flow should only be grabbing the value of the column w/o any name/value pairings we'd normally see in JSON. Essentially, we should have a single-column unnamed array similar to if we'd hand-built it like: ["email#1","email#2","email#3"...]Step 3) Join it into a string
Now add a new Join step to your flow (again, a Data Operations step).
In the From field, add in the output from your Select step above.
In the Join With field, add in a semi-colon or comma depending upon what you're goal is. For emails or say changing security for individual rows or documents in SharePoint so you can do context-sensitive row-level security, use a semi-colon.
Now that you've got this value built, you can use a single step (no looping required) to send out emails to a group of people, change security for a row/document in SharePoint, etc.
Stop getting Apply to Each auto-populating by getting only the first item returned from SharePoint
If you ever do a Get Items() step from SharePoint, it will always return an object that "may" contain many records even if you only ever will have one. Say if you're checking User Settings for your app. There will always only be one row or none. So how to you tell Power Automate to stop trying to process "all the rows" and just do one?Add a Compose step (from the Data Operations group).
Click on Add dynamic content, and go to the Expressions tab. You're going to use the first() function.
first(outputs('Get_items')?['body/value'])
If you haven't changed the name of your original Get Items step then the exact code above will work, Otherwise, if you type in first( and then click on Dynamic Content tab, then select the Value from your GetItems() step (not the body).
Now Power Automate knows it is just a single RECORD vs. an entire Array/Object and will not force you to loop through everything.
Just point any future steps back at the Outputs() from your Compose step above.
In my above example, I'm actually pointing a Parse JSON() step at a specific column in the single record so I can map it out and process it using:
outputs('getCollabRecord')?['collaborationsJSON']
In your own situation, the blue text above would be Outputs selected from the Dynamic Content of your original step, and the column name (if applicable) would be the red text.
Just make sure you click Update (or OK...I don't know man) so you don't lose your work after you change something.
It is worth noting that this exact same kind of method won't work if you are trying to do this w/ multiple records (like the prior example I have above). You have to instead go the route of using the Select function (see above).
Consistency is a synonym for Power Automate.
Deleting Array item(s)
Just add a Filter Array() step (from the Data Operations group), filter out whatever item(s) you want to remove, then use a Set Variable step to change your original variable you started with to the Body() from your Filter Array step (which no longer contains the items you filtered out).
Querying SharePoint when values contain single quotes
This can especially be a problem if you're taking inputs into a Power Automate flow from a source you don't control. You'll need to fix this for any SharePoint/OData queries in one way, but something totally different if you're trying to put this into say a JSON object.
For a SharePoint query, the fix is:
Yes. That is a lot of single quotes. Just a whooooole bunch of them.
Replace the blue text above w/ the name of your source value (in my case it is an input into my flow) and you'll be good to go.
To put this into a JSON object, the problem is that you have to put a backward slash (\') in front of the single quote. So the following is accurate for if you're going to drop this into a JSON value:
replace(triggerBody()['text'],'''','\''')
Referencing items inside JSON Objects / Arrays
I can't scream loud enough into the void to cover how I hate the expressions language within Power Automate, but know when you're dealing w/ Objects and Arrays specifically, there are a number of shortcuts that Power Automate won't prompt you on how to use them in a way that will make sense.
Let's imagine we have a JSON object passed in or hard-coded into our app of the following:
"name": "John",
"phone": [
{
"home": "123-456-7890",
"cell": "098-765-4321"
}
]
}
 |
| Note that this is an Object variable |
Now, the examples you'll see or if you automatically populate things, you'll almost always see a command to reference the "name" value here written in the Expression editor as follows:
However, there is a far more intuitive method that of course Power Automate doesn't prompt you to do or show you how to do, and that is:
Much simpler. You can refer to any portion of the Object in this manner.
If you decided to try and reference something inside the "phone" section though, you'll need to know one more trick. Firstly, that an Object is just a single item Array in Power Automate speak. That means we know it is just one item so we don't need to tell Power Automate which Object we want to work with.
Any multi-item Object is not an Object but instead an Array.
<pause for confused stare and re-reading of the above two paragraphs>
In their infinite wisdom, Microsoft starts all arrays inside Power Automate at position Zero. Meaning, the first item is 0, the second is 1, etc. (Soooo consistent Microsoft). This is one of those stupid wars that pops up time and again between idiots who think they are smart and the rest of humanity. I learned that 0 was 1 back in high school. It explains why I'm so very confused all of the time.
Note: PowerApps of course starts at 1. Like any sane person would.
In our above example, the "phone" section is an Array inside an Object. How do we know this? Because it is inside square brackets: []. What this means is that we could, in theory, have multiple segments of "phone" (even though we only have one in the above example). So we need to tell Power Automate *which* of the potentially infinite # of Phone array items we want. We want the first one (which will nearly always be the case), so to get the "cell" #, we use the following shorthand similar to above:
variables('testJSON').phone[0].cell
The [0] is placed right after the section header that is an array. Then you just continue as before w/ the same kind of nomenclature. You could again use the Power Automate longhand and confuse yourself, but...why?
To expand on this, imagine we instead received an Array of users and their phone #'s vs. just a single Object. We'd start similar to:
{
"name": "John",
"phone": [
{
"home": "123-456-7890",
"cell": "098-765-4321"
}
]
},
{
"name": "Milo",
"phone": [
{
"home": "123-456-7890",
"cell": "098-765-4321"
}
]
}
]
 |
| Note that this is an Array variable |
Since we're now in the situation where we don't know which Object in the Array we want, we can use the shorthand above similarly:
variables('testJSON')[0].phone[0].cell
This would give us the cell # of the first item in our Array. To get the second item it would be:
The real PiTA re: this within Power Automate is that the "intelligent" prompts don't explain any of this to you and also try to use the longhand version nearly exclusively. Do yourself a favor and adopt this methodology before you hurt yourself or someone on that team at Microsoft.
Other people's useful links
Bhawana's great post: https://www.enjoysharepoint.com/power-automate-array-variable/
No comments:
Post a Comment
Because some d-bag is throwing 'bot posts at my blog I've turned on full Moderation. If your comment doesn't show up immediately then that's why.