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

[Trublu30]

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.

[Trublu30]

I have just discovered another small issue...when I first open the app on my mobile, there is no records showing. See attached screenshot. This must be something to do with the filter script, as when I press my reset in the bottom left corner (OnSelect=Set(myFilter, "")) it shows up all the records in alphabetical order, which is what I want. What do I need to change to show the Alphabetical list by default when opening the app?

[Trublu30]

Hi @dinusc, could I please have your assistance on my last two questions? I have really appreciated your help here...and I trust my questions will help other users when coming across this type of configuration.

Look forward to your help.

[dinusc]

For the first issue, please try to disable the "Explicit Column Selection" from the app settings.

For the second issue, you can use the SortBy function in your formula. THe folloiwng informaiton should help: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-sort

[Trublu30]

Hi @dinusc, thanks for responding!

So when you say 'the first issue', were you referring to my post "I have just discovered another small issue...when I first open the app on my mobile, there is no records showing. See attached screenshot. This must be something to do with the filter script, as when I press my reset in the bottom left corner (OnSelect=Set(myFilter, "")) it shows up all the records in alphabetical order, which is what I want. What do I need to change to show the Alphabetical list by default when opening the app?"...??

If so, disabling the "Explicit Column Selection" made no difference. I thinks its something to do with the script.

As to my other question, what I was wanting to acheive was, combining this

Filter( Trades, First(Split(SearchInput.Text, " ")).Result in Trade_x0020_Location && Last(FirstN(Split(SearchInput.Text, " "), 2)).Result in Trade_x0020_Type)

 with this

SortByColumns(If(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 )), "FullName", If(SortDescending1, Descending, Ascending))

so that I get the ability to filter ascending or descending, search by a single word or search both columns with double words. But i just cant figure out how to write that so it works?

Thanks for your help on this!

[dinusc]

I see. The first issue then can be resolved by setting the "reset" formula in the "OnStart" property of the first screen (Copy Set(myFilter, "") to Screen1.OnStart)

The second question. You say: "so that I get the ability to filter ascending or descending, search by a single word or search both columns with double words". My question is how do you choose the searching type? The intended behaviour is clear when the SearchInput.Text is empty since you just use the pre-filtered condition (myFilter) to search by trade type. When the SearchInput.Text is not empty however, how do you want the user to choose whether the content of that SearchInput.Text needs to be split (first formula) or used as a condition in other columns (second formula). For example, you could have a RadioButton with Split = Yes/No option. This will define your combined formula.

[Trublu30]

@dinusc, I got the first issue sorted...just set the OnStart for my first screen to set(myFilter, "") and it works perfect!

As to the second question, if you look back at this thread, I have a sort ascending/descending button. When clicking that all it does is sort by FullName. And then I have a search bar that I can search by Trade_x0200_Type, Trade_x0200_Location, Company and FullName. So this is the script I currently have for doing all that:

SortByColumns(If(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 )), "FullName", If(SortDescending1, Descending, Ascending))

But now I want to add in the extra search functionality of typing two words that pick up from two different columns, using this script:

Filter( Trades, First(Split(SearchInput.Text, " ")).Result in Trade_x0020_Location && Last(FirstN(Split(SearchInput.Text, " "), 2)).Result in Trade_x0020_Type)

But I still want to maintain the other search options. So I need to combine them somehow? 

[dinusc]

In your first formula, you would have to replace your Filter(...) with :

If (CountRows(Split(SearchInput.Text, " ")) > 1, Filter(...), Your_Second_Formula)

The idea is that if the SearchInput.Text is empty, it will filter by "muFilter". If its content can be split, use the first formula. If it is not empty and cannot be split, use your second formula.

[Trublu30]

Thanks for your reply @dinusc. But I'm really struggling with this. Either I'm missing something or you havent got the full picture. So Ive attached a screen shot showing exactly the 4 different search and filter functions I am trying to achieve with this app.

Can you please take a look at this and then see what I am doing wrong?

The way I intepret what you have explained in your last reply, gives me this formula, but its obviously not correct:

SortByColumns(If(IsBlank(SearchInput.Text), If (CountRows(Split(SearchInput.Text, " ")) > 1, 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 )), Filter( Trades, First(Split(SearchInput.Text, " ")).Result in Trade_x0020_Location && Last(FirstN(Split(SearchInput.Text, " "), 2)).Result in Trade_x0020_Type), "FullName", If(SortDescending1, Descending, Ascending))

Look forward to your helpful reply!

[Trublu30]

Hello @dinusc,

Just wondering if you are able to help me with my last response here? If you take a look at that screenshot I added it makes it clear what I am trying to do.

Many thanks

[dinusc · Accepted solution]

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.