Additional Filtering needed.. Either additional or current Combobox or Search can be used

[Lasherza]

Hi all,

Thank you all for the help so far. I will be taking further studies to enrich my knowledge that i have so far learnt from you guys.

I am now battling to add additional filtering , taken suggestions from @RandyHayes who has helped me build this up to this point.

I have a gallery , which is currently on filtered by a combobox based on a Yes/No field. I would like to add a filter based on the Title.

The "search" title must be able to include any part of the title to bring back the record.

Current formula on items on the gallery is :

Sort(Filter('Vandalism and Insurance List','Submitted to Finance'.Value=Dropdown2.SelectedText.Value),ID,Descending)

Again appreciate the help!

[RandyHayes]

@Lasherza 

I assume you are adding a TextInput control in your app to allow for user entry to search the Title...if so, then your formula would be the following:

With({_items: 
    Sort(
        Filter('Vandalism and Insurance List',
            'Submitted to Finance'.Value=Dropdown2.Selected.Value
        ),
        ID,Descending
    )
    },

    Search(_items, yourTextInput.Text, "Title")
)

Also, you were using a deprecated property (SelectedText) in your formula.  I have replaced that with Selected.Value.  If Value is not the correct column name, then replace Value with the proper column name.

I hope this is helpful for you.

[Lasherza · Accepted solution]

@RandyHayes to the Rescue! - Thanks again!

Yes added the search input as i would like to have that. The formula seems to "want" to work, but i failed to mention.

Search(_items, TextInput1.Text, "Title") . 

The column in the SP List is actually a Lookup column named Site. I know that adds complexity, i am not using the "primary" key column in the SP List which i assume is normally title..

[RandyHayes · Accepted solution]

@Lasherza 

If it is a lookup column, then you can change to this:

With({_items: 
    Sort(
        Filter('Vandalism and Insurance List',
            'Submitted to Finance'.Value=Dropdown2.Selected.Value
        ),
        ID,Descending
    )
    },

    Search(AddColumns(_items, "_lookup", Site.Value), yourTextInput.Text, "_lookup")
)

[Lasherza]

No Frikken way!!! . Thats IT!! . Amazed. This is GOAT stuff.

I have not seen any filtering that looks remotely like that.... I have a lot to learn! if ever.....

Thank you Sir @RandyHayes !

[RandyHayes]

@Lasherza 

PowerApps - keep it super simple!!  

Key points of the formula:

1) We did the initial filtering into a With Scoped variable called _items.  This is because your Filter function is delegable, but the Search function is not.  So, no need for delegation warnings!!

2) _items is the resulting table from the Filter.  In order to use Search you must supply the names of text columns you want to search on.  The lookup column is not a text column.  However, the Value in the lookup is...but you need to "pull" it out.  That was done with the AddColumns, where we just made another column in the return of the AddColumns function that would have the lookup.Value...then we can specify it to search on and Search will be happy.

And that's it!! 😁

[RandyHayes]

@Lasherza 

PowerApps - Keep It Super Simple!!  

Key points of the formula:

1) We did the initial filtering into a With Scoped variable called _items.  This is because your Filter function is delegable, but the Search function is not.  So, no need for delegation warnings!!

2) _items is the resulting table from the Filter.  In order to use Search you must supply the names of text columns you want to search on.  The lookup column is not a text column.  However, the Value in the lookup is...but you need to "pull" it out.  That was done with the AddColumns, where we just made another column in the return of the AddColumns function that would have the lookup.Value...then we can specify it to search on and Search will be happy.

And that's it!! 😁

[Lasherza]

Ooops @RandyHayes may have sung your praises too soon lol... It seems on selection of the gallery item/record which takes the app to another screen with the record detail pops an error on the Item property which is set to

BrowseGallery.Selected

The screen is blank too.

[Lasherza]

So smart... AddColumns is really useful here!

Just that small issue now where the selected record throws an error and a blank "detail" screen.

[RandyHayes · Accepted solution]

@Lasherza 

Yes, so adding a column changes the schema of the record in your gallery.  Using the .Selected in a form will provide a record that does not match the schema of the datasource.

SOOO....just like AddColumns is useful, DropColumns is equally useful.

Formula Changes to:

With({_items: 
    Sort(
        Filter('Vandalism and Insurance List',
            'Submitted to Finance'.Value=Dropdown2.Selected.Value
        ),
        ID,Descending
    )
    },

    DropColumns(
        Search(AddColumns(_items, "_lookup", Site.Value), yourTextInput.Text, "_lookup"),
        "_lookup"
    )
)

We only need the added column for the use of the Search function...so the resulting table from Search can be passed to DropColumns and told to drop that added column.  This will then result in a table with records that match the Schema of the datasource for your Form (I am assuming you are getting the error in the Form, as that is the only control that would complain about the added column).