Filter by another table

[Anonymous]

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

[RusselThomas]

Hi @Anonymous ,

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

[Anonymous]

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.

[RusselThomas]

Hi @Anonymous 

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  

[Anonymous]

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?

[RusselThomas · Accepted solution]

Hi @Anonymous ,

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

[Anonymous]

This is perfect! Thanks 🙂