Unanswered

Dynamically filter a gallery based on a sharepoint linked column

(0) Share

Report

Posted on by Lee123

I have a gallery in my powerapp I'd like to dynamically filter both the field to search and the value of that search based on context variables passed through from a button on another screen. The field that I want to filter by is, in this case, a linked column in my sharepoint list datasource.

the button code, which seems to work fine, is

Navigate(InteractionsBrowseScreen,ScreenTransition.None, {InteractionsFilterValue:DataCardValue17.Text, InteractionsFilterField:"Organisation.Value"})

The InteractionsFilterValue is the default value of the search textbox in the gallery (TextSearchBox1).

The data field of the gallery is

SortByColumns(Filter([@Test_Interactions], StartsWith(InteractionsFilterField, TextSearchBox1.Text)), "Created", If(SortDescending1, Descending, Ascending))

Which I think should work, but it returns no rows.

If I hard-set the filter field rather than using the variable, it works, although I do get a delegation warning

SortByColumns(Filter([@Test_Interactions], StartsWith(Organisation.Value, TextSearchBox1.Text)), "Created", If(SortDescending1, Descending, Ascending))

How can I do this without creating duplicate screens and galleries with different data fields?

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (1)

Verified answer

RandyHayes 76,297 Super User 2024 Season 1 on at

Like (0)

Report
@Lee123

You cannot reference fields by name in PowerApps. You are trying to pass the field name and then use that as a referenced field. This will not work and your filter criteria will evaluate to false and you will have no values.

What you can do is use the name in your filter as a condition:

SortByColumns( Filter(Test_Interactions, Switch(InteractionsFilterField, "Organisation.Value", StartsWith(Organisation.Value, TextSearchBox1.Text), "someothercolumn", StartsWith(someOtherColumn, TextSearchBox1.Text) ) ), "Created", If(SortDescending1, Descending, Ascending) )

I hope this is helpful for you.

Was this reply helpful? Yes No
Lee123 27 on at

Like (0)

Report

Thanks Randy that got me a lot closer, but ended up rewriting the filter as

SortByColumns(
    Filter([@Test_Interactions], ( IsBlank(TextSearchBox1.Text) || StartsWith(Organisation.Value, TextSearchBox1.Text) || TextSearchBox1.Text in Names.Value) )
      ,
       "Created", If(SortDescending1, Descending, Ascending))

and abandoning the InteractionsFilterField.

It almost works as I want it to, but the 'Names' field from the sharepoint list is a multiple value column that is also a lookup column. With the current code I get exact match working fine - so I can enter 'john smith' and get the hits but not 'john'

Any ideas what I could do about that? I tried using Search instead of filter but it won't work with either of the lookup columns, the single value 'organisation' or the multiple value 'names'. Works fine with non-lookup columns.

Search( Test_Interactions, TextSearchBox1.Text, "Organisation.Value", "Names.Value" )

Was this reply helpful? Yes No
RandyHayes 76,297 Super User 2024 Season 1 on at

Like (0)

Report

@Lee123

You'll not be able to use the Search function on a multi-value lookup. You can use the In operator for this.

Filter(

Search( Test_Interactions, TextSearchBox1.Text, "Organisation.Value")

TextSearchBox1.Text in Names.Value

)

Was this reply helpful? Yes No