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

Filtering indexed, SharePoint List of 18,000 entries.

Hello everyone,

 

I am currently building a canvas app where I have  indexed columns on a SharePoint list of 18,000 entries. The list only has 3 columns, Customer Full Name, Email and Event (since there are only 3 columns I indexed all). I would like my customers to be able to filter the list via their email to see what events they attended.

 

I inserted a gallery and connected it to the SharePoint list. All 18,000 entries showed up. I created a textbox called 'FilterTextBox' and inserted the HintText "Insert Email Address". On the gallery I inserted the delegable coding: Filter('EPIC Test Events List', FilterTextBox.Text = Email).

 

When this coding is inserted and I try filtering by email the list just returns blank. I don't get the yellow triangle delegation warning since I am using the "=" function however the list does not populate as shown in the attached screenshot. Any suggestions on how I can fix this? Any assistance provided here would be greatly appreciated!

 

 

 

5 REPLIES 5
Super User II
Super User II

@dr9106 

 

Try the following:

Filter('EPIC Test Events List', StartsWith(Email,FilterTextBox.Text))

 

Regards

Krishna Rachakonda

If this reply helped you to solve the issue, please mark the post as Accepted SolutionMarking this post as Accepted Solution, will help many other users to use this post to solve same or similar issue without re-posting the issue in the group. Saves a lot of time for everyone.

Hi @rsaikrishna,

 

I tried the code you provided and there was some improvement. The list automatically came up when I corrected the coding, however it still it does not filter the SharePoint list when I run the app. The first screenshot below shows the list, the second shows me filtering by "Andy.LastName@company.com" and as you can see it remains blank:

 

dr9106_0-1601306644328.png

dr9106_1-1601306703961.png

I don't know if it's because the email addresses have a "." in them for example my company's email addresses are FirstName.LastName@CompanyName.com. 

 

Let me know what you think. I checked the list on the SharePoint site and the columns types are all "Single Line of Text".

@dr9106 

 

Looks like the case of the entered text and the column value not matching.

 

Try converting to lower case with Lower() function.

 

Also, try searching with exact email id exists in SP list to check it comes in the search when you run the app.

 

Regards

Krishna Rachakonda

If this reply helped you to solve the issue, please mark the post as Accepted SolutionMarking this post as Accepted Solution, will help many other users to use this post to solve same or similar issue without re-posting the issue in the group. Saves a lot of time for everyone.

Hi @rsaikrishna,

 

The exact email address is in the SharePoint List, however I'm having difficulty incorporating the Lower() function in the Filter function.

 

  • Filter('EPIC Test Events List', Lower(Email), StartsWith(Email,FilterTextBox.Text))

Would this be the correct approach here? From what I've been reading this shouldn't be this difficult as long as the columns were indexed and I'm using the StartsWith function. Let me know what you think.

 

Frequent Visitor

Hi everyone,

 

An update on this. I re-uploaded my excel sheet of 18,000 entries to my SharePoint Site with all lower cases. I then indexed the "Email" column and tried filtering using the: Filter(EPIC Test Events List, StartsWith( Lower(Email), FilterTextBox.Text)).

 

When I did this I get a delegation warning saying the starts with part of the formula would not work correctly on large data sets.

 

Any further suggestions on how I could get this to work? Keep in mind I am linking to this data from the Canvas App, don't know if that could be a problem.

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

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