cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AKB2021
Helper I
Helper I

Identifying particular row entry in a table

Hello All,

 

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

 

UsernameAssigned roleDate assigned
user 1Admin10-20-2021
user 2Viewer10-18-2021
user 2Change Management09-12-2021
user 2Project manager04-23-2021
user 3Admin03-11-2021
user 1Viewer03-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.

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User
Super User

@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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

5 REPLIES 5
BCLS776
Super User
Super User

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

_________________________________________________________________________________________
Help the community help more users by choosing to "Accept as Solution" if this post met your needs. If you liked the post and want to show some appreciation, please give it a Thumbs Up.
C-Papa
Super User
Super User

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
Super User
Super User

@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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

AKB2021
Helper I
Helper I

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
Super User
Super User

@AKB2021 

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

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (3,326)