- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Printer Friendly Page
- Mark as New
- Subscribe to RSS Feed
- Permalink
Need Help with Filtering Text Box/dropdown values based on the value of People Picker (Office 365) selected using SP list and PowerApps.
Basics:
I have a list called "Reviewers". Columns: ID (Integer), Title (Single Line of Text), Reviewer (People Picker), Role (Single Line of Text)
Below are the Rules:
- Reviewer can have more than one Role
- Role can have more than one Reviewers.
- Reviewer cannot have the same role twice.
In my New Form (See attached):
Role is selected based on the Office selected from a Dropdown.
Office values are from a Collection.
Role Values are with If Statement - see below:
If(
DataCardValue23_1.Selected.Value = "Bureaus",
"Bureau Director",
DataCardValue23_1.Selected.Value = "Deputy Secretary",
"Deputy Secretary",
DataCardValue23_1.Selected.Value = "Legal Office",
"Legal Representative",
DataCardValue23_1.Selected.Value = "Budget Office",
"Budget Representative",
DataCardValue23_1.Selected.Value = "Policy Office",
"Policy Representative",
DataCardValue23_1.Selected.Value = "Legislative Affairs",
"Legislative Director"
)
Goal:
When I add a new Reviewer (from Office 365) I want to filter Roles/Offices that are not already assigned to this specific new Reviewer if he/she already exists in the Reviewers List.
Example:
If John Doe is not part of the Reviewers List then all of the Above Roles should be available for him to choose from. However, if he already exists in the Reviewers list having say two Roles: Bureau Director and Deputy Secretary. Then when he is added again, the available options should be limited to: Legal Representative, Budget Representative, Policy Representative and Legislative Director only.
How do I achieve this?
Any help is appreciated.
Thank you,
SueA.
Solved! Go to Solution.
Accepted Solutions
- Mark as New
- Subscribe to RSS Feed
- Permalink
Hi @SueA ,
Please bear in mind that conditional outcomes in the Items of Table controls such as Galleries and Combo Box/Dropdowns are problematic at best and require identical outcome structures for all possibilities, however I thought that presenting the one identical column for both cases would work - there is one correction you might try
With(
{
wRole:
[
"Bureaus",
"Deputy Secretary",
"Legal Office",
"Budget Office",
"Policy Office",
"Legislative Affairs"
],
wReviewer:
Filter(
Reviewers,
Reviewer.DisplayName = cmbNewReviewer.Selected.DisplayName
)
},
If(
IsEmpty(wReviewer),
ShowColumns(
Reviewers,
"Role"
),
Filter(
wReviewer,
!(Role in wRole)
).Role
)
)
Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.
MVP (Business Applications) Visit my blog Practical Power Apps
- Mark as New
- Subscribe to RSS Feed
- Permalink
Hi @WarrenBelz
You were right. Dropdown and Combo gave me hard time as well...I needed to rethink the approach. Thus, I recreated the screen.
Created a button on the top titled "Add New Reviewer" and hid the remaining controls. "On Select" property of this button, I placed the code below:
ClearCollect(ColBasicRoles,
{Role: "Bureau Director"},
{Role: "Deputy Secretary"},
{Role: "Legal Representative"},
{Role: "Budget Representative"},
{Role: "Policy Director"},
{Role: "Legislative Director"}
);
UpdateContext({ShowNewReviewerGrp:true});
With the above local variable, I hid the remaining controls. They became visible on the click of the "Add New Reviewer" button. Got rid of Office dropdown and had ComboBox connected to Office 365 for selecting a new reviewer. For the Role dropdown, I placed the code listed below. Which worked and provided the correct values.
Click of the Submit button, I patched the new record, reset the controls, and hid the controls again.
The code underneath the dropdown control for Roles for the Items property:
Filter(
ColBasicRoles,
!(Role in Filter(
Reviewers,
Reviewer.DisplayName = cmbAddNewReviewerAR.Selected.DisplayName
).Role)
)
I did not know how to go about this for the longest time. Your insights and guidance with the code provided using the With function and comparing the two tables, helped me understand the concept to create the current solution that is now working.
Many thanks to you for that.
With Regards,
SueA.
- Mark as New
- Subscribe to RSS Feed
- Permalink
Hi @SueA ,
Please try:
If(
!IsBlank(LookUp(Reviewers,Reviewer.Email=PeoplerPicker.Selected.Email)),
LookUp(Reviewers,Reviewer.Email=PeoplerPicker.Selected.Email).Role,
If(
DataCardValue23_1.Selected.Value = "Bureaus",
"Bureau Director",
DataCardValue23_1.Selected.Value = "Deputy Secretary",
"Deputy Secretary",
DataCardValue23_1.Selected.Value = "Legal Office",
"Legal Representative",
DataCardValue23_1.Selected.Value = "Budget Office",
"Budget Representative",
DataCardValue23_1.Selected.Value = "Policy Office",
"Policy Representative",
DataCardValue23_1.Selected.Value = "Legislative Affairs",
"Legislative Director"
)
)
Best Regards,
Bof
- Mark as New
- Subscribe to RSS Feed
- Permalink
Hi, @v-bofeng-msft
This is working in reverse. Meaning, the script above shows the Role of the Reviewer that he already has, in the Role Text Box. Also, it does not show the second Role if the same person has more than one Roles...
What I want, is to exclude all the roles that a Reviewer already has and choose from the rest of the roles. Thereby, not allow the duplication of the Roles for the same person.
What complicates the matter is that a Person can already have more than one Roles and the same person can have a third role or a fourth role by choosing one from the available roles. PowerApps read only top Role value and ignoring the remaining Role assignments…
How can I achieve that?
Thank you,
SueA.
- Mark as New
- Subscribe to RSS Feed
- Permalink
Hi @SueA ,
As per your PM sent - does this work ?
With(
{
wRole:
Switch(
DataCardValue23_1.Selected.Value,
"Bureaus",
"Bureau Director",
"Deputy Secretary",
"Deputy Secretary",
"Legal Office",
"Legal Representative",
"Budget Office",
"Budget Representative",
"Policy Office",
"Policy Representative",
"Legislative Affairs",
"Legislative Director"
),
wReviewer:
Filter(
Reviewers,
Reviewer.DisplayName = ReviewerDropdown.Selected.DisplayName
)
},
Filter(
wReviewer,
Role <> wRole
)
)
The separation of the Filter is only for Delegation reasons - I am also assuming that the Role drop-down is single select otherwise this will be a whole new level of complexity.
Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.
MVP (Business Applications) Visit my blog Practical Power Apps
- Mark as New
- Subscribe to RSS Feed
- Permalink
Yes, Office is a single select and the Role is a single Text Box.
However, it is not resolving even after I placed the correct value for Reviewer's People Picker Box value from Reviewer Dropdown in your code to DataCardValue5_1 the actual value.
Also, the logic of the code should work only if a Reviewer exists in the Reviewers list. Also, a Reviewer could exists more than once in the Reviewers list with a different Role. So, would the above code work for that case and where would the code !IsBlank go in the above script?
The PowerApps is not identifying the details of the error as far as formula is concerned....
Any thoughts?
- Mark as New
- Subscribe to RSS Feed
- Permalink
@SueA ,
OK - allowing for the reviewer to have no present records
With(
{
wRole:
Switch(
DataCardValue23_1.Selected.Value,
"Bureaus",
"Bureau Director",
"Deputy Secretary",
"Deputy Secretary",
"Legal Office",
"Legal Representative",
"Budget Office",
"Budget Representative",
"Policy Office",
"Policy Representative",
"Legislative Affairs",
"Legislative Director"
),
wReviewer:
Filter(
Reviewers,
Reviewer.DisplayName = ReviewerDropdown.Selected.DisplayName
)
},
If(
IsEmpty(wReviewer),
ShowColumns(
Reviewers,
"Role"
),
Filter(
wReviewer,
Role <> wRole
).Role
)
)
Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.
MVP (Business Applications) Visit my blog Practical Power Apps
- Mark as New
- Subscribe to RSS Feed
- Permalink
Hi, @WarrenBelz
Thank you for this amazing script!
The script however did not resolve initially and I struggled to find the reason, The IF statement gave hard time. Also, read somewhere that it gets tricky with a Form. So, changed to Text Input/ComboBox for People Picker and a dropdown for the Roles. And, updated the script accordingly, see below:
It finally resolved! But the Roles are not filtering properly.
For Role <> wRole, the <> did not work in the If statement. And, replacement of Not function also did not give correct values either!
Below is the script that resolves but does not work as intended - meaning,
- if a ComboBox Reviewer does not match with the Reviewers List or is a new person, then all the Roles come as is. But, I need them to be Distinct.
- if reviewer matches or exists in the Reviewers list then, it is showing the existing Roles instead of the remaining ones and it is working partially for some reviewers and not for the others...
- Still working towards debugging it...
- Any ideas/suggestions are appreciated.
With(
{
wRole: [
"Bureaus",
"Deputy Secretary",
"Legal Office",
"Budget Office",
"Policy Office",
"Legislative Affairs"
],
wReviewer: Filter(
Reviewers,
Reviewer.DisplayName = cmbNewReviewer.Selected.DisplayName
)
},
If(
IsEmpty(wReviewer),
ShowColumns(
Reviewers,
"Role"
),
Filter(
wReviewer,
(Role in wRole)
).Role
)
)
Thank you for your consideration.
Regards,
SueA.
- Mark as New
- Subscribe to RSS Feed
- Permalink
Hi @SueA ,
Please bear in mind that conditional outcomes in the Items of Table controls such as Galleries and Combo Box/Dropdowns are problematic at best and require identical outcome structures for all possibilities, however I thought that presenting the one identical column for both cases would work - there is one correction you might try
With(
{
wRole:
[
"Bureaus",
"Deputy Secretary",
"Legal Office",
"Budget Office",
"Policy Office",
"Legislative Affairs"
],
wReviewer:
Filter(
Reviewers,
Reviewer.DisplayName = cmbNewReviewer.Selected.DisplayName
)
},
If(
IsEmpty(wReviewer),
ShowColumns(
Reviewers,
"Role"
),
Filter(
wReviewer,
!(Role in wRole)
).Role
)
)
Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.
MVP (Business Applications) Visit my blog Practical Power Apps
- Mark as New
- Subscribe to RSS Feed
- Permalink
Hi @WarrenBelz
You were right. Dropdown and Combo gave me hard time as well...I needed to rethink the approach. Thus, I recreated the screen.
Created a button on the top titled "Add New Reviewer" and hid the remaining controls. "On Select" property of this button, I placed the code below:
ClearCollect(ColBasicRoles,
{Role: "Bureau Director"},
{Role: "Deputy Secretary"},
{Role: "Legal Representative"},
{Role: "Budget Representative"},
{Role: "Policy Director"},
{Role: "Legislative Director"}
);
UpdateContext({ShowNewReviewerGrp:true});
With the above local variable, I hid the remaining controls. They became visible on the click of the "Add New Reviewer" button. Got rid of Office dropdown and had ComboBox connected to Office 365 for selecting a new reviewer. For the Role dropdown, I placed the code listed below. Which worked and provided the correct values.
Click of the Submit button, I patched the new record, reset the controls, and hid the controls again.
The code underneath the dropdown control for Roles for the Items property:
Filter(
ColBasicRoles,
!(Role in Filter(
Reviewers,
Reviewer.DisplayName = cmbAddNewReviewerAR.Selected.DisplayName
).Role)
)
I did not know how to go about this for the longest time. Your insights and guidance with the code provided using the With function and comparing the two tables, helped me understand the concept to create the current solution that is now working.
Many thanks to you for that.
With Regards,
SueA.
Helpful resources
Community will be READ ONLY July 16th, 5p PDT -July 22nd
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!
Summer of Solutions | Week 4 Results | Winners will be posted on July 24th
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 in the Forums 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 of SolutionsSuper UsersNumber of Solutions @anandm08 23 @WarrenBelz 31 @DBO_DV 10 @Amik 19 AmínAA 6 @mmbr1606 12 @rzuber 4 @happyume 7 @Giraldoj 3@ANB 6 (tie) @SpongYe 6 (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. Community MembersSolutionsSuper UsersSolutions @anandm08 10@WarrenBelz 25 @DBO_DV 6@mmbr1606 14 @AmínAA 4 @Amik 12 @royg 3 @ANB 10 @AllanDeCastro 2 @SunilPashikanti 5 @Michaelfp 2 @FLMike 5 @eduardo_izzo 2 Meekou 2 @rzuber 2 @Velegandla 2 @PowerPlatform-P 2 @Micaiah 2 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 Apps anandm0861WarrenBelz86DBO_DV25Amik66Michaelfp13mmbr160647Giraldoj13FLMike31AmínAA13SpongYe27 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 Apps DBO-DV21WarranBelz26Giraldoj7mmbr160618Muzammmil_0695067Amik14samfawzi_acml6FLMike12tzuber6ANB8 SunilPashikanti8
Check Out | 2024 Release Wave 2 Plans for Microsoft Dynamics 365 and Microsoft Power Platform
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.
Updates to Transitions in the Power Platform Communities
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
Subject | Author | Posted | |
---|---|---|---|
08-03-2023 06:38 PM | |||
04-02-2024 10:33 AM | |||
12-08-2023 12:52 AM | |||
03-20-2024 12:06 PM | |||
11-06-2023 07:43 PM |