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

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

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

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

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.

 

Super User
Super User

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

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?

Super User
Super User

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

Re: Filter by another table

This is perfect! Thanks 🙂

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: 144 members 5,327 guests
Recent signins:
Please welcome our newest community members: