Need Help with Filtering Text Box/dropdown values based on the value of People Picker (Office 365) selected using SP list and PowerApps.

[SueA]

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.

[v-bofeng-msft]

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

[SueA]

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.

[WarrenBelz]

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

[SueA]

@WarrenBelz 

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?

[WarrenBelz]

@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

[SueA]

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.

[WarrenBelz · Accepted solution]

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

[SueA · Accepted solution]

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.