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

How to filter and then filter again to avoid delegation?

Hi

 

I would like to filter/search data without the function StartsWith because with StartsWith, user must search with the exact keyword which is not user friendly. But I have the data around 12k rows in SharePoint List.

So I came with the idea to 1. user select the dropdown and then 2. user can filter by any keyword themselves.

 

For example,

 

my SharePoint List 

Name                             Group (for dropdown)

People Management        A

Manager                           A

Entrepreneur                    A 

Cash Delivery                   B

Process Control                B

 

So if I choose dropdown A, and search with 'Man', I will find both People Management and Manager

 

Please let me know if this could fetch the data from my database not only the first 500 or 2000 rows. Otherwise I guess I have to separate my SharePoint List manually.

And if it works, how should it be coding, I tried looking at the resources but mostly they are using 'StartsWith'.

 

Thank you so much TT

 

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @JULY1 ,

This would not work with rows exceeding 2000 but you could Collect an in app collection.

You would have to add a unique ID that is not the default ID.

Then you could collect all 12k row of data in the collection.

 

 

Clear(colGallery);
With(
   {
      wSets: 
      With(
         {
            wLimits: 
            With(
               {
                  wLimit: 
                  Sort(
                     YourDataSource,
                     NewID,
                     Descending
                  )
               },
               RoundDown(
                  First(wLimit).NewID / 2000,
                  0
               ) + 1
            )
         },
         AddColumns(
            RenameColumns(
               Sequence(
                  wLimits,
                  0,
                  2000
               ),
               "Value",
               "LowID"
            ),
            "HighID",
            LowID + 2000
         )
      )
   },
   ForAll(
      wSets As MaxMin,
      Collect(
         colGallery,
         Filter(
            YourDataSource,
            NewID > MaxMin.LowID && NewID <= MaxMin.HighID
         )
      )
   )
)

 

Change NewID as your unique ID and YourDataSource in the code.

 

 

You would possibly create a Indexed column in sharepoint from this new ID. (Search online for this)

 

Then use the following filter to display the results in the gallery.

Gallery Items property:

 

 

 

With(
    {
        _filter1: Filter(
            colGallery,
            Group = Dropdown1.Selected.Value
        )
    },
    Filter(
        _filter1,
        TextInput10_1.Text in Name)
)

 

 

 

Is that all you wanted?
_____________________________________________________________________________________
Please give a thumbs up if I resolved your issue! Please click Accept as Solution to close the topic!

View solution in original post

4 REPLIES 4
JULY1
Frequent Visitor

I tried this 

 

Filter('SPList',Group=Dropdown1.Selected.Result) ; Filter('SPList',TextInput10_1.Text in Name)

 

But it only works separately.

Hi @JULY1 ,

This would not work with rows exceeding 2000 but you could Collect an in app collection.

You would have to add a unique ID that is not the default ID.

Then you could collect all 12k row of data in the collection.

 

 

Clear(colGallery);
With(
   {
      wSets: 
      With(
         {
            wLimits: 
            With(
               {
                  wLimit: 
                  Sort(
                     YourDataSource,
                     NewID,
                     Descending
                  )
               },
               RoundDown(
                  First(wLimit).NewID / 2000,
                  0
               ) + 1
            )
         },
         AddColumns(
            RenameColumns(
               Sequence(
                  wLimits,
                  0,
                  2000
               ),
               "Value",
               "LowID"
            ),
            "HighID",
            LowID + 2000
         )
      )
   },
   ForAll(
      wSets As MaxMin,
      Collect(
         colGallery,
         Filter(
            YourDataSource,
            NewID > MaxMin.LowID && NewID <= MaxMin.HighID
         )
      )
   )
)

 

Change NewID as your unique ID and YourDataSource in the code.

 

 

You would possibly create a Indexed column in sharepoint from this new ID. (Search online for this)

 

Then use the following filter to display the results in the gallery.

Gallery Items property:

 

 

 

With(
    {
        _filter1: Filter(
            colGallery,
            Group = Dropdown1.Selected.Value
        )
    },
    Filter(
        _filter1,
        TextInput10_1.Text in Name)
)

 

 

 

Is that all you wanted?
_____________________________________________________________________________________
Please give a thumbs up if I resolved your issue! Please click Accept as Solution to close the topic!

JULY1
Frequent Visitor

This worked when it was changed from .Value to .Resulted

With(
    {
        _filter1: Filter(
            colGallery,
            Group = Dropdown1.Selected.Value
        )
    },
    Filter(
        _filter1,
        TextInput10_1.Text in Name)
)

 

But the data it filtered got less than before.

I never used the collection, Let me know if there's something wrong here.

1. I created a button and then copied and pasted this 'ClearCollect(colGallery,YourDataSource)' on OnSelect.

2. I copied the above filter to my gallery.

3. Then just played around on searching and clicking the button then it showed so much less.

I don't know if I should have other 'Collect'? but my powerapps is only for viewing data and no add or edit.

 

If you insert a Text Label and set the Text property to :

 

CountRows(Gallery.AllItems)

 

You will probably see only 2000..

 

As I said you would have to create a new unique ID column in your SharePoint.

Then if you follow these steps to create a Indexed column : https://social.technet.microsoft.com/wiki/contents/articles/53459.sharepoint-steps-to-create-indexin... 

Just set the column as your New unique ID.

 

Then you will be able to collect more than 2000 rows of data in your in-app collection and therefore filter through it using the filter above..

 


_____________________________________________________________________________________
Please give a thumbs up if I resolved your issue! Please click Accept as Solution to close the topic!

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Power Apps Ideas

Changes to Ideas Coming

We are excited to announce a new way to share your ideas for Power Apps!

Top Solution Authors
Top Kudoed Authors
Users online (3,028)