cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
grunwald
Level: Powered On

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
nickduxfield
Level 8

Re: Filter a filter

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

Re: Filter a filter

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

grunwald
Level: Powered On

Re: Filter a filter

The datamodel looks like this:

 

 

 20170901_172107.png.jpg

 

thank you so far.

 

And i have to start from User

Anonymous
Not applicable

Re: Filter a filter

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

Re: Filter a filter

Anonymous
Not applicable

Re: Filter a filter

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
)
grunwald
Level: Powered On

Re: Filter a filter

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

grunwald
Level: Powered On

Re: Filter a filter

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

grunwald
Level: Powered On

Re: Filter a filter

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

nickduxfield
Level 8

Re: Filter a filter

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
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 338 members 5,917 guests
Please welcome our newest community members: