I feel like this should not be so hard, but I have watched numerous videos and read many posts and still can't get something to work.
Here is what I want to do:
We have a share point list that tracks customers coming to our company including. I need to pull 3 fields from this list. Name of company (easy), Date they are going live (easy), and the name of the primary contact within our department who will help them (people pickers are the worst).
The other issue is the list comes in and is flited to current year - which yields about 191 records, I have a Filter Array to pull a specific type of customer and a certain platform they are on - yielding about 25 records currently. I have tried to add that to my initial filter on my get items and that has failed.
The people picker is set to allow multiple records, in my case it won't hold multiple but for other filters of the list (different platform) it could. I know that doesn't change how we need to work with the data, but it is just not an option to take that ability away and make this simpler.
So here is what does work
My Get items - filtered to this year
My Filter array to add my additional two conditions
I then have 2 selects currently - the first one pull the company and date field I need
My second pull the name picker
I can't go back to the get items because it will run though all 191 records.
I have a compose for a count (no issues here) and I have a parse JSON off the Select 1
This all works nicely. The issue is getting the Display Name from here to them add to my Company and Date fields.
I am not getting nice fields anytime I try to make that connection. I have had multiple errors from the array standpoint. I also have had were I get a list of all names in that field and not the one that goes with each applicable record.
In the end I want to send a email with a nice HTML table for the next week when these customers go live with us. I am at a loss lately with what else to do.
Thanks!
Solved! Go to Solution.
@Mikey_01 If you try to use the multi-person column dynamic content in your Create an HTML table action, it'll output an array of items.
You'll need to use an expression to extract and join the display names without using an Apply to Each action. Before you delete the dynamic content, hover over it to get the dynamic content key which is the text between the square brackets.
The expression you'll need to use is below.
join(xpath(xml(json(concat('{"body":{"value":', item()?['YourColumn'] , '}}'))), '/body/value/DynamicContentKey/text()'), '[separator] ')
item()?['YourColumn']
: Get the multi-choice people column.concat
: This concatenates the static strings {"body":{"value": and }} with the dynamic content from item()?['YourColumn'].json
: This converts the concatenated string into a JSON object.xml
: This converts the JSON object into an XML format.xpath
: Extract DyanmicContentKey
text values.join
: Join the names into a single string with a separator
Copy/paste the function into the Expression field. Locate this part of the expression: item()?['YourColumn']
Replace the text between the single quotes with the text for your dynamic content key. In my case it's Multi_x002d_Person.
Press OK.
Run a test and review the outputs.
You can replace DisplayName in the expression with Email and the comma with a semi-colon if you'd like to output a string of email addresses with a semi-colon as a separator.
@Mikey_01 You should be able to Compose a Filter Query with your conditions you have in your Filter Array action. This would reduce your flow by one action. I'd recommend using the Classic Designer (toggle off the new one) as it's still buggy.
Create a New Flow for testing purposes.
You should be able to include the conditions below into your Filter Query field. Can you confirm the type of columns you are filtering by?
The filter query syntax is a bit different and you'll need to use the eq operator. You will need to use the internal column name of your columns. Keep in mind that the internal column name may not always match the name displayed in your Sharepoint list. If you aren't sure how to get the Internal Column name, you can refer to this section of one of my YT Tutorials.
The filter query should look something like this. Keep in mind that the equals to (eq) operator is looking for an exact match.
It isn't necessary to fill in the top count field, unless you want to limit the number of items returned. Tip: While testing and building your flow, (when possible) you can limit the number of items to a smaller number to speed up your flow run (by reducing the number of items returned). This may not be possible in some cases.
If your SP list contains more than 100 items you will need to toggle on pagination and set a threshold in the Settings of the Get Items action.
Whenever I use a Filter Query in a Get Items action, I always like to return the count of items returned in a Compose action. This is helpful when building a flow and can also be used to troubleshoot your flow.
Insert a Compose action. Add an Expression. Use the length() function.
Select the Dynamic content tab and insert the value dynamic content from the Get Items action into the length() function.
Run a test. It's always best to run tests at each major section of your flow as it's easier to troubleshoot as you are building rather than trying to troubleshoot after you've "completed" your flow.
Let's try to get the Filter Query working so it filters out all the items as expected from your SP List first.
... more to come.
In the meantime you may find these YT Tutorials helpful:
Are you using the Microsoft Power Automate Filter Array Action wrong?
In this video tutorial I’ll show you 3 practical ways to use the Filter Array action and how to use it properly.
1️⃣ Cross-Referencing Data
2️⃣ Filtering by Key
3️⃣ Substring Matching
Did you know that the Condition action has a limit of 10 conditions? Although it might look like the Filter Array action can only accept one condition—this is not true. By using the advanced mode you can enter multiple conditions into a Filter Array action with an expression.
IN THIS VIDEO:
✅ 3 Ways to Use the Filter Array Action
✅ How to use the Scope Action to Group Actions
✅ How to Check the Number of Items returned from a Filter Array Action
✅ How to Cross-Reference Data in Excel with a SharePoint List
✅ How the Filter Array Action Works
✅ How to Access the Dynamic Content from a Filter Array Action
✅ How to Filter Items by a Key
✅ How to Filter Items by Matching a Substring
✅ How to Use Multiple Conditions in a Filter Array Action
----
How to Handle Multi-Person SharePoint Item Notifications in Outlook and MS Teams with Power Automate
Do you want to know how to send an email to all users assigned to a SharePoint item? If so—this Power Automate tutorial is for YOU 🫵.
However, that tutorial only covered a scenario where each task is assigned to a single user. What if your task is assigned to more than one user?
In this Microsoft Power Automate tutorial I’ll cover how to create a flow that is triggered by selecting an item. This automation will send an email to all assigned users for the selected task. I’ll also show you how to adjust your flow so that you can send a message in Teams. Then I’ll show you how to collect a list of unique users that have been assigned to multi-person tasks. Using this concept, we’ll edit the flow from the previous tutorial so that it works with a multi-person column.
IN THIS VIDEO:
✅ How to trigger a flow from a selected file
✅ How the multi-person column data outputs
✅ How to use the Select action to get all assigned to users of a SharePoint item
✅ How to use the Join action to convert an array of email addresses into a string
✅ How to send a single email to all assigned to users of a SharePoint item
✅ How to send an email to each assigned to user of a SharePoint item
✅ How to send a Teams message to each user assigned to a SharePoint item
✅ How to return a list of unique users from the multi-person column in a SharePoint list
✅ How to use a Filter Array action to return all SharePoint list items assigned to each user
✅ How to group Power Automate actions together with the Scope action
✅ How to edit a previous flow
---
For more flow troubleshooting tips—check out this YT Tutorial: 5 Power Automate Troubleshooting FAQs and Helpful Tips for Creating Better Flows
In this Power Automate tutorial, I explore 5 frequently asked questions that pop up when troubleshooting a flow. If you’d like to to level up your Power Automate flow skills and learn how to troubleshoot your Power Automate flow—this tutorial is for you!
IN THIS VIDEO:
✅ How to troubleshoot a false Condition action result
✅ How to get dynamic content when it isn’t selectable from the list of dynamic content
✅ How to troubleshoot an Apply to Each action that isn’t looping through
✅ How to troubleshoot a skipped Apply to Each action
✅ How to troubleshoot a Filter Query
✅ How to use a SharePoint yes/no column in a Filter Query
✅ How to use Compose actions to troubleshoot a Power Automate flow
✅ How to troubleshoot multiple emails being sent
✅ How to troubleshoot multiple Teams messages being sent
---
How to Work with 📆 Dates in Power Automate | Example Scenarios and Tips & Tricks
Are you easily stumped when working with Dates in Power Automate? In this Power Automate tutorial, I’ll show you how to compose an expression that will return a future date, a past date, how to count the number of days between two dates, how to check for a birthdate and anniversary date as well as tips and tricks when working with dates in Power Automate.
I’ll cover some common use cases and concepts that can help you to build better Power Automate flows. Feel free to skip ahead using the timestamps listed below. I’ve also linked a few other tutorials that you might be interested in as well.
IN THIS VIDEO:
✅ 4 Date Functions You Need to Know
✅ How to use the Convert Time Zone Action
✅ How to Get a Future Date
✅ How to Get a Past Date
✅ How to Return SharePoint Items Due in a Number of Days
✅ How to Return SharePoint Items Due within a Date Range
✅ How to write a Filter Query for SharePoint Items Due in 30, 60 and 90 Days
✅ How to Calculate the Number of Days between Two Dates
✅ How to Check for a Birthdate and Anniversary Date
✅ How to work with Dates and Times in Excel
✅ How to use a Manual Trigger with Date Input
✅ How to Output Dynamic Text with an Expression
✅ How to Check if a Date Falls on a Weekend
Hello @creativeopinion ,
So, the issue I ran into before was these are two selector values and they don't seem to play nice in the get items. One is a Software type selector and customer type designation selection. I finally got the fields to line up but then I get no results.
Compose Action expression
I can confirm when I use my filter array setup that I do get 25 records
@Mikey_01 You didn't confirm what type of columns these are. Are they single choice? Multi-choice? Text? Look Up?
To troubleshoot your filter query, I would recommend adding a condition at a time. First, run a test with the first condition—check the Compose action output.
Add another condition in. Run a test—check the output.
If the filter query above doesn't output any items, try replacing the second condition with the third one.
You can also remove the parenthesis from the first condition (not necessary).
The fields are single-select values
I can get the POType ='New Customer' to work no issue.
My STU_x002f_BUS is the trouble spot. It is called System in the list screen. I have used the following options:
System
System_Value
STU_x002f_BUS
STU_x002f_BUS Value
STU_x002f_BUS_Value
STU_x002f_BUS/Value
So I am at a loss on that one. I either get the field can't be found or it has been deleted.
@Mikey_01 You will need to use the internal column name of your column. Keep in mind that the internal column name may not always match the name displayed in your Sharepoint list. If you aren't sure how to get the Internal Column name, you can refer to this section of one of my YT Tutorials.
Okay, even though I had the STU_x002f_BUS multiple times as the field name, this time, copying it from the web address was the trick. I am Filtered to my 25 records.
I am so turned around now on what my next steps should be.
Does anyone know how to get my display name from my people picker and combine it with data from the record to then create an HTML table of possibly multiple records?
Basically I want a table that has Customer Name / A go live Date / and just the display name of their consultant (the people picker allows multiple).
@Mikey_01 If you try to use the multi-person column dynamic content in your Create an HTML table action, it'll output an array of items.
You'll need to use an expression to extract and join the display names without using an Apply to Each action. Before you delete the dynamic content, hover over it to get the dynamic content key which is the text between the square brackets.
The expression you'll need to use is below.
join(xpath(xml(json(concat('{"body":{"value":', item()?['YourColumn'] , '}}'))), '/body/value/DynamicContentKey/text()'), '[separator] ')
item()?['YourColumn']
: Get the multi-choice people column.concat
: This concatenates the static strings {"body":{"value": and }} with the dynamic content from item()?['YourColumn'].json
: This converts the concatenated string into a JSON object.xml
: This converts the JSON object into an XML format.xpath
: Extract DyanmicContentKey
text values.join
: Join the names into a single string with a separator
Copy/paste the function into the Expression field. Locate this part of the expression: item()?['YourColumn']
Replace the text between the single quotes with the text for your dynamic content key. In my case it's Multi_x002d_Person.
Press OK.
Run a test and review the outputs.
You can replace DisplayName in the expression with Email and the comma with a semi-colon if you'd like to output a string of email addresses with a semi-colon as a separator.
@creativeopinion I want to appreciate how you solved this issue: from recommending a better filter method to the final solution.
Your final solution essentially uses XPath as an inline select action 🤯. This opens a lot of possibilities. XPath already provides a way to do lookups, inline filters.
Dear Community Members, We'd like to let you know of an upcoming change to the community platform: starting July 16th, the platform will transition to a READ ONLY mode until July 22nd. During this period, members will not be able to Kudo, Comment, or Reply to any posts. On July 22nd, please be on the lookout for a message sent to the email address registered on your community profile. This email is crucial as it will contain your unique code and link to register for the new platform encompassing all of the communities. What to Expect in the New Community: A more unified experience where all products, including Power Apps, Power Automate, Copilot Studio, and Power Pages, will be accessible from one community.Community Blogs that you can syndicate and link to for automatic updates. We appreciate your understanding and cooperation during this transition. Stay tuned for the exciting new features and a seamless community experience ahead!
We are excited to announce the Summer of Solutions Challenge! This challenge is kicking off on Monday, June 17th and will run for (4) weeks. The challenge is open to all Power Platform (Power Apps, Power Automate, Copilot Studio & Power Pages) community members. We invite you to participate in a quest to provide solutions to as many questions as you can. Answers can be provided in all the communities. Entry Period: This Challenge will consist of four weekly Entry Periods as follows (each an “Entry Period”) - 12:00 a.m. PT on June 17, 2024 – 11:59 p.m. PT on June 23, 2024 - 12:00 a.m. PT on June 24, 2024 – 11:59 p.m. PT on June 30, 2024 - 12:00 a.m. PT on July 1, 2024 – 11:59 p.m. PT on July 7, 2024 - 12:00 a.m. PT on July 8, 2024 – 11:59 p.m. PT on July 14, 2024 Entries will be eligible for the Entry Period in which they are received and will not carryover to subsequent weekly entry periods. You must enter into each weekly Entry Period separately. How to Enter: We invite you to participate in a quest to provide "Accepted Solutions" to as many questions as you can. Answers can be provided in all the communities. Users must provide a solution which can be an “Accepted Solution” in the Forums in all of the communities and there are no limits to the number of “Accepted Solutions” that a member can provide for entries in this challenge, but each entry must be substantially unique and different. Winner Selection and Prizes: At the end of each week, we will list the top ten (10) Community users which will consist of: 5 Community Members & 5 Super Users and they will advance to the final drawing. We will post each week in the News & Announcements the top 10 Solution providers. At the end of the challenge, we will add all of the top 10 weekly names and enter them into a random drawing. Then we will randomly select ten (10) winners (5 Community Members & 5 Super Users) from among all eligible entrants received across all weekly Entry Periods to receive the prize listed below. If a winner declines, we will draw again at random for the next winner. A user will only be able to win once overall. If they are drawn multiple times, another user will be drawn at random. Individuals will be contacted before the announcement with the opportunity to claim or deny the prize. Once all of the winners have been notified, we will post in the News & Announcements of each community with the list of winners. Each winner will receive one (1) Pass to the Power Platform Conference in Las Vegas, Sep. 18-20, 2024 ($1800 value). NOTE: Prize is for conference attendance only and any other costs such as airfare, lodging, transportation, and food are the sole responsibility of the winner. Tickets are not transferable to any other party or to next year’s event. ** PLEASE SEE THE ATTACHED RULES for this CHALLENGE** Week 1 Results: Congratulations to the Week 1 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge. Community MembersNumber SolutionsSuper UsersNumber Solutions Deenuji 9 @NathanAlvares24 17 @Anil_g 7 @ManishSolanki 13 @eetuRobo 5 @David_MA 10 @VishnuReddy1997 5 @SpongYe 9JhonatanOB19932 (tie) @Nived_Nambiar 8 @maltie 2 (tie) @PA-Noob 2 (tie) @LukeMcG 2 (tie) @tgut03 2 (tie) Week 2 Results: Congratulations to the Week 2 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge. Week 2: Community MembersSolutionsSuper UsersSolutionsPower Automate @Deenuji 12@ManishSolanki 19 @Anil_g 10 @NathanAlvares24 17 @VishnuReddy1997 6 @Expiscornovus 10 @Tjan 5 @Nived_Nambiar 10 @eetuRobo 3 @SudeepGhatakNZ 8 Week 3 Results: Congratulations to the Week 3 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge. Week 3:Community MembersSolutionsSuper UsersSolutionsPower Automate Deenuji32ManishSolanki55VishnuReddy199724NathanAlvares2444Anil_g22SudeepGhatakNZ40eetuRobo18Nived_Nambiar28Tjan8David_MA22 Week 4 Results: Congratulations to the Week 4 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge. Week 4:Community MembersSolutionsSuper UsersSolutionsPower Automate Deenuji11FLMike31Sayan11ManishSolanki16VishnuReddy199710creativeopinion14Akshansh-Sharma3SudeepGhatakNZ7claudiovc2CFernandes5 misc2Nived_Nambiar5 Usernametwice232rzaneti5 eetuRobo2 Anil_g2 SharonS2
On July 16, 2024, we published the 2024 release wave 2 plans for Microsoft Dynamics 365 and Microsoft Power Platform. These plans are a compilation of the new capabilities planned to be released between October 2024 to March 2025. This release introduces a wealth of new features designed to enhance customer understanding and improve overall user experience, showcasing our dedication to driving digital transformation for our customers and partners. The upcoming wave is centered around utilizing advanced AI and Microsoft Copilot technologies to enhance user productivity and streamline operations across diverse business applications. These enhancements include intelligent automation, AI-powered insights, and immersive user experiences that are designed to break down barriers between data, insights, and individuals. Watch a summary of the release highlights. Discover the latest features that empower organizations to operate more efficiently and adaptively. From AI-driven sales insights and customer service enhancements to predictive analytics in supply chain management and autonomous financial processes, the new capabilities enable businesses to proactively address challenges and capitalize on opportunities.
We're embarking on a journey to enhance your experience by transitioning to a new community platform. Our team has been diligently working to create a fresh community site, leveraging the very Dynamics 365 and Power Platform tools our community advocates for. We started this journey with transitioning Copilot Studio forums and blogs in June. The move marks the beginning of a new chapter, and we're eager for you to be a part of it. The rest of the Power Platform product sites will be moving over this summer. Stay tuned for more updates as we get closer to the launch. We can't wait to welcome you to our new community space, designed with you in mind. Let's connect, learn, and grow together. Here's to new beginnings and endless possibilities! If you have any questions, observations or concerns throughout this process please go to https://aka.ms/PPCommSupport. To stay up to date on the latest details of this migration and other important Community updates subscribe to our News and Announcements forums: Copilot Studio, Power Apps, Power Automate, Power Pages