Can this filter idea actually work in Power Apps?

[WGill]

Hello everyone.

I am in desperate need of some help.  Everyone on this site is so much smarter than me with Power Apps but I promise I am trying to learn from you all.

I have a Power App that I am building that allows a user, let's say that is Bob, to create a request.  When Bob submits the data, it is written to the backend Sharepoint site.  In that request, Bob has added people in the request as stakeholders, Kim, and point of contact, Mike.

When the request is assigned to a designer, Bob can go back to the request site and view the status of his request.  It's just information that is read-only, but gets updated by the designer.

Here is where I am running into all kinds of trouble/doubt.  

I can write the formula for the read-only page to show only requests that Bob created BUT I also want Kim and Mike to to go to the page so they can view the projects that they belong too.  How would I do that?

Also, I want to be able to filter by the Request# as well BUT only if the logged-in user shows up anywhere in the request. 

So, if a user goes to the status page and inputs a valid Request#, that data would show if their name was anywhere in the Sharepoint data list.  Bob, Kim, and Mike could look at the data, but Lisa wouldn't see that information.

How do I do that as well?

I can't thank you all enough for your insight and explanation to this beginner. 

Have a wonderful day! 

[KaiE]

Can you provide the code for the gallery? I suppose? 

Can you also provide a screenshot.

Thanks!

[WiZey · Accepted solution]

Hello @WGill ,

Assuming your creator, stakeholder and point of contact are in the same list, you could use "Filter()" to extract all records where their name appear in at least one column like this:

Filter(
  datasource,
  'Created by'.Email = User().Email ||
  Stakeholder.Email = User().Email ||
  'Point of contact'.Email = User().Email
)

The formula above check if the current user is present in at least one of there columns.

Notice how I'm checking email only, that's because Power Apps can't compare two records as they are and can only do so with simple types like texts, numbers or booleans. Comparing email is also a solid method because emails are supposed to be unique in the Active Directory. 

Additionally, concerning the request number, the formula would look like this:

Filter(
  datasource,
  'Created by'.Email = User().Email ||
  Stakeholder.Email = User().Email ||
  'Point of contact'.Email = User().Email,
  'Request#' = Value(TextInput.Text)
)

Assuming your request number is a number-type column and you're using a text input in your app, you should use "Value()" to convert from a text to a numeric value for comparison. 

Don't hesitate to ask if you have any question.

[WGill]

Hi WiZey.  

Thank you for your reply and ANSWER!  

I had another question based on using the email of the Stakeholder.  How would I use a filter if there were multiple Stakeholders? 

For example, the creator would choose a couple of people that would be Stakeholders.  My list in SP allows for multiple people in the column.  I don't know what I would do to allow for that.  

Thank you so much for your help!

[WiZey]

I was kind of confused with your question, so I'm going to answer all three possibilities.

1. Check if one stakeholder exists in a selection list

Your user select multiple stakeholders, and "Filter()" returns all records where its stakeholder exists in the selection list.

Filter(
  datasource,
  Stakeholder.Email in ListOfSelectedEmails
)

2. Check if multiple stakeholders exists in a single selection

Your user select on stakeholder, and "Filter()" returns all records where at least one of their stakeholders corresponds to the selected.

Filter(
  datasource,
  SelectedEmail in Stakeholder.Email
)

3. Check if multiple stakeholders exists in a selection list

Your user select multiple stakeholders, and "Filter()" returns all records where at least one of their stakeholders exists in the selection list.

Filter(
  datasource;
  Sum(
    ForAll(
      ComboBox.SelectedItems;
      If(ThisRecord.Mail in Stakeholder.Email;1;0)
    );
  Value)>0
)

The filter above will compare each selected email to all stakeholders of the record. For each corresponding stakeholder, it will add "1" to a value then check if this value is greater than 0. If so, that means this record holds at least one stakeholder you're looking for.