Filter a filter

[grunwald]

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!

[Anonymous]

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]

The datamodel looks like this:

 

thank you so far.

And i have to start from User

[Anonymous]

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]

https://powerapps.microsoft.com/en-us/tutorials/function-table-shaping/

For reference

[Anonymous]

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]

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]

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]

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 · Accepted solution]

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.