cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Filter a filter

Hi everyone,

 

i am new to powerapps and maybe the solution is easy.

 

I want to create a prefiltered view on Projects.

 

Means:

 

If a users opens the app a userId will be looked up( ---> this works)

 

LookUp('[dbo].[tblUser]';email=User().Email;ID)

I store this value in a textbox named varUserId

 

 

with this userId Filter a m:n table where are useres assigned to projects so the result will

a set of project ids the user is assigned to

Filter('[dbo].[tblProjectUser]';fk_user_id = Value(VarUserID.Text))

When i display this in a DataTable it works.

 

How can i use this to prefilter the gally datasource tblProjects to see only the  Projects assigned to the user?

 

Thank you!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Can I have a go?

  1. OnStart Properties #Loads on app start##
    1. Set(CurrentUser,User()) ##used to lookup other user info##
    2. Set(CurrentUserIDVariable,Lookup(UserTable, CurrentUser.DisplayName=Name,UserID)) ##Gets the UserID stores in global variable)
    3. ClearCollect(ProjectCollection, ProjectTable) ##Creates a collection from all known projects##
    4. ClearCollect(ProjectUserCollection, Filter(ProjectUser, CurrentUserIDVariable=FK_User_ID)) ##Creates a filtered collection of just the current user assignments##
  2. On My Projects Gallery (presents current users projects): SortByColumns(Filter(ProjectCollection,Project.ID=ProjectUserCollection.FK_Project_ID),'Modified",Decending))

##Filters the Projects collection, by the Project ID's in FilteredProjectUserCollection##

##Should only show the project info your assigned projects as multiple items in gallery##

 

This is a good challenge, the data model looks flexible.

That will be pre filtered for the user. You can then add other drop downs to filter status or date etc.

View solution in original post

21 REPLIES 21
Highlighted
Anonymous
Not applicable

Does your Projects table have a field where it is pulling in an id or name of the user that is assigned to that project record?

 

Or do you just have that tblProjectsUser table that basically has like a project id that is associated with a user id

Highlighted

The datamodel looks like this:

 

 

 20170901_172107.png.jpg

 

thank you so far.

 

And i have to start from User

Highlighted
Anonymous
Not applicable

You should be able to accomplish this through collections and the AddColumns function

 

Collect(myProjects, AddColumns('[dbo].[tblProjects]', "UserID", LookUp('[dbo].[tblProjectUser]', fk_project_ID=ID).fk_user_id)

 

What this is doing is creating a collection that has all of your projects with an additional column tacked on called UserID that stores the userID that is associated with a project ID. (This is a way to join tables). I'm not exactly sure how you will have to reference 'ID' since it is the same in all 3 tables, but the idea is generally similar.

 

Then in the gallery in the Items, you could then Filter the collection based on that UserID column in relation to the userID you provided to the textbox

 

Filter(myProjects, UserID=VarUserID.Text)

 

The only drawback, and I am still trying to figure this out for my own purposes, is that AddColumns is not a delegable method and thus only the first 500 records will be returned. If you have less than that in your data purposes however, this methodology should work.

 

Or maybe at least a starting point for you?

Highlighted
Anonymous
Not applicable
Highlighted
Anonymous
Not applicable

I have not tested it but from your picture I think it should be failry simple to filter a gallery to show only projects assigned to specific user using lookup function.

 

Try puting this function in Gallery.Item field

Filter(
    ProjectDataSource,
    LookUp(
        ProjectUserDataSource,
        varUserID=fk_user_id,
        fk_Project_ID
    ) = ID
)

Thank you so far, this is what what i was expected to work.

But i think the Lookup-function only returns a single value and i need more than one Id to be returned.

 

Thank you

Highlighted

I 'll try this, but i am not sure if it works... What happens if 2 or more users a re assigned a project?

 

RIght now i think i have to create a view where i cross join the tables.

But thank you very much for you ideas.

 

Kind regards

Highlighted

I 'll try this, but i am not sure if it works... What happens if 2 or more users a re assigned a project?

 

Right now i think i have to create a view where i cross join the tables.

But thank you very much for you ideas.

 

Kind regards

Highlighted

Can I have a go?

  1. OnStart Properties #Loads on app start##
    1. Set(CurrentUser,User()) ##used to lookup other user info##
    2. Set(CurrentUserIDVariable,Lookup(UserTable, CurrentUser.DisplayName=Name,UserID)) ##Gets the UserID stores in global variable)
    3. ClearCollect(ProjectCollection, ProjectTable) ##Creates a collection from all known projects##
    4. ClearCollect(ProjectUserCollection, Filter(ProjectUser, CurrentUserIDVariable=FK_User_ID)) ##Creates a filtered collection of just the current user assignments##
  2. On My Projects Gallery (presents current users projects): SortByColumns(Filter(ProjectCollection,Project.ID=ProjectUserCollection.FK_Project_ID),'Modified",Decending))

##Filters the Projects collection, by the Project ID's in FilteredProjectUserCollection##

##Should only show the project info your assigned projects as multiple items in gallery##

 

This is a good challenge, the data model looks flexible.

That will be pre filtered for the user. You can then add other drop downs to filter status or date etc.

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (7,219)