cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
grunwald
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

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

The datamodel looks like this:

 

 

 20170901_172107.png.jpg

 

thank you so far.

 

And i have to start from User

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?

Anonymous
Not applicable
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

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

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

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
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Don't miss the call this month on June 16th - 8a PDT

Top Solution Authors
Top Kudoed Authors
Users online (72,011)