Identifying particular row entry in a table

[AKB2021]

Hello All,

Is there a way to identify a particular row record within a table which has duplicate entries for e.g.

Username	Assigned role	Date assigned
user 1	Admin	10-20-2021
user 2	Viewer	10-18-2021
user 2	Change Management	09-12-2021
user 2	Project manager	04-23-2021
user 3	Admin	03-11-2021
user 1	Viewer	03-04-2021

Here I want to check if user 2 has "Change Management" role assigned to them, and if found, retrieve additional details from that database row.

[BCLS776]

You can use a LookUp() to retrieve the record, and additional fields are accessible using .columnName behind the LookUp():

LookUp(myTable, Username = "user 2" && 'Assigned Role' = "Change Management")

Hope that helps,

Bryan

[C-Papa]

hi, yes you can do this by collecting the data using a filter. see the below as an example

ClearCollect(Col_Filter_Results, Filter(MyDataSource, Username = "user  2" && 'Assigned Role' = "Change Management")

you can change "user  2" and  "Change Management" to a text input text or dropdown etc if required. a gallery could then be used to display the results, where the items property would be Col_Filter_Results. if the Col_Filter_Results table has no rows then no records match the filter parameters

[RandyHayes · Accepted solution]

@AKB2021 

I believe the prior responses are missing the fact that you want all of the other rows for that user whenever that user happens to be in the Change Management role.

So, your formula needs to be the following (assuming an Items property on a control such as a Gallery - avoid a collection for this as your app doesn't need that kind of overhead for this):

With({_data: yourDatatable, 
      _usr: "user 2"},

    If(LookUp(_data, Username = _usr && AssignedRole = "Change Management", true),
        {Username: _usr,
         Roles: Filter(_data, Username = _usr)
        }
    )
).Roles

This will return a Record with a column called Roles that contains all of the roles for the given user if they happen to be in the Change Management role.  

If you don't want the Change Management role to appear, but instead all of the other roles, then change to the following:

With({_data: yourDatatable, 
      _usr: "user 2"},

    If(LookUp(_data, Username = _usr && AssignedRole = "Change Management", true),
        {Username: _usr,
         Roles: Filter(_data, Username = _usr && !(AssignedRole = "Change Management"))
        }
    )
).Roles

I hope this is helpful for you.

[AKB2021]

Thanks for all the inputs. Really appreciated.

Currently I am looking to first identify all the roles assigned to user and then based on role(s) assigned, plan to give him/her sufficient access in Powerapps. @RandyHayes pointed out correctly that I would require all the roles assigned to user as that is the basis on which I can identify which access to give them in PowerApps

For e.g.

User 1 has view Admin and Viewer role, but as in a different table I am maintaining that Admin will have "Create", "Edit", "Delete" and "View" access while Viewer role will have only "View" access. So User 1 will be given "Create", "Edit", "Delete" and "View" or Admin access in Powerapps. Same goes for user 2 that user will get "Change Management" access level i.e. "Create", "Edit" and "View" even when they have "Project manager" role when this role should prevent user from performing any action in app.

[RandyHayes]

@AKB2021 

So the formula I provided you should be giving you what you need.  Did you give that a try?