Search and filter 'Address' field in gallery

[Heisshena]

Hi 

In my Home Screen, I have a content gallery with Organisation name, address, contact name, contact email fields. 

I also have 2 different search bar, one to search for Organisation name field (TxtSearch), and another search bar for Address field(TxtSearch_2).

Currently the item property of my Content Gallery is set up as below where the gallery needs to be sorted by ID in descending order:
Sort(Filter(Content,StartsWith(Organisation,TxtSearch.Text)),ID,sortOrder.Descending)

I would like to add a filter for my address field into my content gallery item property, so that users are able to search for the full address. 

I've tried many ways but I'm not happy with it. I do not want the address to filter StartsWith, I want the user to be able to search for the full address. The code below is what I tried to update my items property with but it changes the gallery and does not sort by ID and unable to search full address. 

Filter(SortByColumns(Filter(Content,StartsWith(Organisation,TxtSearch.Text)),ID,sortOrder.Descending),StartsWith('Address',TxtSearch_2.Text))

What is the best way to fix this? Please advise. 

Thanks in advance

[Amik]

@Heisshena , try:

Sort(
    Filter(
        Content,
        StartsWith(
            Organisation,
            TxtSearch.Text
        ),
        StartsWith(
            Address,
            TxtSearch_2.Text
        )
    ),
    ID,
    SortOrder.Descending
)

If the full address must be entered in order for the filter condition to trigger, you can use:

Sort(
    Filter(
        Content,
        StartsWith(
            Organisation,
            TxtSearch.Text
        ),
        Len(TxtSearch_2.Text) = 0 || Address = TxtSearch_2.Text
    ),
    ID,
    SortOrder.Descending
)

------------------------------------------------------------------------------------------------------------------------------

If I have answered your question, please mark your post as Solved. Remember, you can accept more than one post as a solution.

If you like my response, please give it a Thumbs Up.

Imran-Ami Khan

[FLMike]

Hello

Question: If they are searching by the full address, wouldn't they only get either 0, 1 or a minimal number of records back?

My point being is you are double sorting, but what is the sort doing, how many records are you getting back with a full address?

If you do not want to use StartsWith, you can simply filter with StringColumn = StringValue, but in that case, you may or may not want to require them to either a) click out of the search type text or b) click the search icon or whatever you have, instead of simply constantly filtering the list as you go, hence also my point below.

I would also recommend, if you can, not to do your filter criteria in your Items property, but in a collection that is then assigned to the Items property or even a Named Formula.

[Heisshena]

@Amik  Thank you for your quick response, this helped but I am getting delegation warning. I do have over 3000 records which currently only showing 2000 since I've changed the settings from 500 to show 2000 data row limit and I'm unable to search for the remainder 1000 in the search bar. Is there a way to fix this?

[Heisshena]

@FLMike  Yeh that makes sense, I do have over 3000 records and using the example that @Amik had provided, it doesn't pick up all the 3000 records. How do I create a collection? is it for the field names? sorry I'm a newbie for PowerApps, still trying to understand the basics.
What is the best way to go about this? 

[Amik]

@Heisshena - the StartsWith function is delegable only with Single Line Text data types. I assuming either the Organisation field or the Address field are not ?

[FLMike]

the only way to make it work is to do pagination and make multiple calls to the back end and keep growing your collection.

Youd have to have some one to tell the query to essentially do a "between" or top etc

It depends on your data but 2,000 is the max it will bring back in a single call

[Heisshena]

@Amik both my Organisation and Address fields are single line text data types

[Amik]

@Heisshena - what is your data source? Excel? SQL?

Please share a screenshot of the expression bar with the delegation warning, and paste your code here as text.

[Heisshena]

 

@Amik my data source is sharepoint list imported from excel which was over 3000 data.

I have shared the screenshot of the delegation warning