cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Dynamically filter a gallery based on a sharepoint linked column

Hi

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

@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.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

View solution in original post

3 REPLIES 3
Super User III
Super User III

@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.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

View solution in original post

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" )

 

Super User III
Super User III

@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

    )

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

Helpful resources

Announcements
secondImage

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

Top Kudoed Authors
Users online (81,059)