cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Kathryn_Marie Helper III
Helper III

Filter by another table

Hi,

 

I need to filter a page of records based on the logged in user. I have the Logged in user listed in Table 1, but I want to filter Table 2 based on a linked ID. For example

Table 1

ID-123 - Record 1 - User 1

ID-234 - Record 2 - User 1

ID-345 - Record 3 - User 2

 

Table 2

LinkedID-123 - Item 1

LinkedID-123 - Item 2

LinkedID-345 - Item 3

 

So the user clicks the filter button, and if User 1 is logged in, they'll see item 1 & 2.

 

I've tried this code which has no error, but also does nothing:

 

Filter(Table1, User().FullName && ID in Table2.linkedID) 

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft
Microsoft

Re: Filter by another table

Hi @Kathryn_Marie ,

I'm guessing then that you want the user to be able to see everything first and then filter on demand?

If your table is pointing to a datasource and you want it to filter on the push of a button, you'll need to create a condition on the table source to control what it displays based on whether the button is pushed or not.

eg: 

Table Items: Property

<source>

Conditional Table Items: Property 

If button pressed, Filter<source>, otherwise <source>

 So you need to have some toggle variable for your button so you know when it's been pressed, so the table can perform the relevant condition.  Add a button, set it's OnSelect: property to 

UpdateContext({varFiltered: !varFiltered})

This creates a toggle variable - basically every time you push the button it will change the boolean (True/False) value varFiltered to the opposite of what it currently is.  By default, non-defined booleans are False when the app starts.

To help keep track of where the toggle is, set the Text: property of the button to;

 

If(varFiltered, "Unfilter", "Filter")

 

You can then set your Items: property on the Data Table to;

If(varFiltered, Filter(Items, crb20_linkeddivision_id in Filter(Divisions, crb20_owner = User().FullName).crb20_division_id), Items)

Assuming the filter expression is correct, this should apply your filter whenever the variable is toggled to True.

Hope this helps,

RT

View solution in original post

6 REPLIES 6
Microsoft
Microsoft

Re: Filter by another table

Hi @Kathryn_Marie ,

The example is a little confusing;

You said you wanted to filter Table2, but your expression is filtering Table1.

Can I assume you want to filter Table2 by the currently logged on user records in Table1?

Also - What is the name of the column where the user name/identifier is stored in Table1?

If I have it right, it looks like you want to filter Table2 based on a filter of Table1, so something like this;

Filter(Table2, linkedID in Filter(Table1, [usernamecolumn]=User().FullName).ID)

Kind regards,

RT

 

 

Kathryn_Marie Helper III
Helper III

Re: Filter by another table

Yes, that's what I am after, sorry for the confusion. So now I have:

 

Filter(Items, crb20_linkeddivision_id in Filter(Divisions, crb20_owner = User().FullName).crb20_division_id)

 

Logically it looks right, but nothing happens when I click the fliter button.

 

Microsoft
Microsoft

Re: Filter by another table

Hi @Kathryn_Marie 

I'm not sure how your screen is laid out, but the filter function provides an output in the form of a table - unless you're rendering or collecting that output somehow, you won't see it.

You would need to be applying that function to the Gallery you're using to display the result, so you'd either need to put the function on the Items: property of the Gallery you're using to show the output, or Collect() the result of the function into a collection and and show that on the Items: property of a gallery.

Hope this makes sense,

RT  

 

Kathryn_Marie Helper III
Helper III

Re: Filter by another table

Sorry, I did reply to this and it disappeared for some reason! My screen just has a data table and a filter button. when you first navigate to the screen you should see everything in the data table. When the logged in user clicks the button it should filter the list. 

 

I have the data source for the table as items, so I think I need to add something to update the table data?

Microsoft
Microsoft

Re: Filter by another table

Hi @Kathryn_Marie ,

I'm guessing then that you want the user to be able to see everything first and then filter on demand?

If your table is pointing to a datasource and you want it to filter on the push of a button, you'll need to create a condition on the table source to control what it displays based on whether the button is pushed or not.

eg: 

Table Items: Property

<source>

Conditional Table Items: Property 

If button pressed, Filter<source>, otherwise <source>

 So you need to have some toggle variable for your button so you know when it's been pressed, so the table can perform the relevant condition.  Add a button, set it's OnSelect: property to 

UpdateContext({varFiltered: !varFiltered})

This creates a toggle variable - basically every time you push the button it will change the boolean (True/False) value varFiltered to the opposite of what it currently is.  By default, non-defined booleans are False when the app starts.

To help keep track of where the toggle is, set the Text: property of the button to;

 

If(varFiltered, "Unfilter", "Filter")

 

You can then set your Items: property on the Data Table to;

If(varFiltered, Filter(Items, crb20_linkeddivision_id in Filter(Divisions, crb20_owner = User().FullName).crb20_division_id), Items)

Assuming the filter expression is correct, this should apply your filter whenever the variable is toggled to True.

Hope this helps,

RT

View solution in original post

Kathryn_Marie Helper III
Helper III

Re: Filter by another table

This is perfect! Thanks 🙂

Helpful resources

Announcements
MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

firstImage

New Ranks and Rank Icons released on April 21!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power Apps Community!

Power Platform 2020 release wave 1 plan

Power Platform 2020 release wave 1 plan

Features releasing from April 2020 through September 2020

Top Solution Authors
Top Kudoed Authors
Users online (5,077)