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

Powerapps clicking icon will filter the list based on a particular word in one of the columns

Hi, In PowerApps, I am trying to set up a filter system for a series of icons, whereby when clicking one, it filters the list of contacts based on a particular word in one of the columns. See attached screenshots...So for example, I click the 'Hammer' icon and it filters all the records that have 'Carpenter' in the 'Trade Type' column. And then if I tap the 'Paint Roller' icon, it filters all the records to show all those that have 'Painter' in the 'Trade Type' column

 

Can I please have some help on how to code this?

 

Cheers.

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft Employee

Re: Powerapps clicking icon will filter the list based on a particular word in one of the columns

Hello @Trublu30,

I'm sorry I missed your message. 

The following formula should do what you are looking for:

 

SortByColumns(
    If(
        IsBlank(SearchInput.Text),
        Filter(
            Trades,
            myFilter in t1
        ),
		If (CountRows(Split(SearchInput.Text, " ")) > 1,
		Filter( Trades, First(Split(SearchInput.Text, " ")).Result in t4 && Last(FirstN(Split(SearchInput.Text, " "), 2)).Result in t1),
        Filter(
            Trades,
            SearchInput.Text in t2 || SearchInput.Text in t3 || SearchInput.Text in t1 || SearchInput.Text in t4
        ))
    ),
    "t2",
    If(
        SortDescending1,
        Descending,
        Ascending
    )
)

In the formula above, I replaced the column names as following for simplicity:
Trade_x0020_Type = t1
FullName = t2
Company = t3
Trade_x0020_Location = t4

 

 

I'm also attaching a test app to demonstarte the formula above. You should be able to import it after un-zipping it. After loading, click on both buttons from the app.

View solution in original post

30 REPLIES 30
Microsoft Employee

Re: Powerapps clicking icon will filter the list based on a particular word in one of the columns

Hello @Trublu30,

Let's assume your source list (or table) is named "Trated" and the field name that containes the trade types is "TradeType".

In this case you can set the following formula to your icon's "OnSelect" property: Set(myFilter, "carpenter") and then bind the gallery to:

Filter(Trades, myFilter in TradeType)

This will remove all the gallery items that do not contain the word "carpenter" which I belive is what you're looking for.

You can bind other items in the similar way. Please note that the filter is not case sensitive so both "Carpenter" and "cArPenTeR" will be preserved in filtered list in the example above.

Trublu30
Level: Powered On

Re: Powerapps clicking icon will filter the list based on a particular word in one of the columns

@dinusc, thanks for your reply.

 

This makes sense but when I set these scripts, clicking the icon does nothing. No change to the records.

 

By the way, I added the Filter(Trades, myFilter in TradeType) to my galleries 'Items' control, in which I also have other search scripts. So this is what I have in there now:

 

Filter( Trades, myFilter in Trade_x0020_Type || SearchInput.Text in FullName || SearchInput.Text in Company || SearchInput.Text in Trade_x0020_Type || SearchInput.Text in Trade_x0020_Location  )

 

Is this correct?

Microsoft Employee

Re: Powerapps clicking icon will filter the list based on a particular word in one of the columns

Are you trying to apply changes to records when clicking on icons? I was under the impression that you just want to use them for filtering the content. Please note that you can use "Patch" function to apply changes and that you can also have more than one formulas in the OnSelect proeprty. The formulas would have to be separatet by Semicolons.

Your formula looks correct.

Trublu30
Level: Powered On

Re: Powerapps clicking icon will filter the list based on a particular word in one of the columns

All I am trying to do is show the records that have that particular filter in their Trade Type field.

Trublu30
Level: Powered On

Re: Powerapps clicking icon will filter the list based on a particular word in one of the columns

When I said 'No change to the records', I was simply meaning that nothing happens...the records that I see in the current screen dont change to the ones that should show up with the filter.

Microsoft Employee

Re: Powerapps clicking icon will filter the list based on a particular word in one of the columns

I see. My guess then is that other conditions in your Filter formula allows all the records. Please test with this:

Filter( Trades, myFilter in Trade_x0020_Type )

If that works then it's possible that your SearchInputText.Text is empty and therefore it causes all the records to show up. If so then you will have to include it conditionally. For example, you can have the following as your filter formula:

IsBlank(SearchInput.Text, Filter( Trades, myFilter in Trade_x0020_Type ),

Filter( Trades, myFilter in Trade_x0020_Type || SearchInput.Text in FullName || SearchInput.Text in Company || SearchInput.Text in Trade_x0020_Type || SearchInput.Text in Trade_x0020_Location  ))

Trublu30
Level: Powered On

Re: Powerapps clicking icon will filter the list based on a particular word in one of the columns

Ok, so when I do the test formula, yes it works, but all the records with the filter term show up automaitcally, I dont have to click the icon. So something is wrong there.

 

And when using the formula you suggest, IsBlank(SearchInput.Text, Filter( Trades, myFilter in Trade_x0020_Type ), Filter( Trades, SearchInput.Text in FullName || SearchInput.Text in Company || SearchInput.Text in Trade_x0020_Type || SearchInput.Text in Trade_x0020_Location  )) I get no records showing and the icon or search does nothing. See attached screenshot. 

 

I'm only new to all this, so maybe I'm doing something wrong!

 

Appreciate your help.

Microsoft Employee

Re: Powerapps clicking icon will filter the list based on a particular word in one of the columns

So when you apply a short formula, the records a filtered without clicking on the icon, right? This happens because you previously clicked on it and therefore your myFilter is not empty. You will have to reset it. For example, if you have "Show All" icon then you can set its OnSelect property to Set(myFilter, "")

In regards to the long formula, it is my fault. I typed it without checking. Below is the proper formula:

If(IsBlank(SearchInput.Text), Filter( Trades, myFilter in Trade_x0020_Type ),
Filter( Trades, myFilter in Trade_x0020_Type || SearchInput.Text in FullName || SearchInput.Text in Company || SearchInput.Text in Trade_x0020_Type || SearchInput.Text in Trade_x0020_Location  ))

The formula basically saus: If the SearchInput.Text is empty (blank), apply the short formula. If not, then apply the long formula.

Hope this helps.

Trublu30
Level: Powered On

Re: Powerapps clicking icon will filter the list based on a particular word in one of the columns

Excellent, that worked! I just had to remove the second 

Trades, myFilter in Trade_x0020_Type

that you had in the long part of the script. After doing that, all good!

 

Now, how do I make my sort ascending/descending button to work? Again, as it is currently, it does nothing. I've played around, but cant seem to get it. 

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: 357 members 5,088 guests
Please welcome our newest community members: