500 item limit in CDM entity search filter(need to switch to asp.net app if this exists for long)

[AmitLoh-Powerap]

500 item limit in CDM entity search filter, this makes it very dofficult to use for any business scenario(export, data analysis)

because I have 50k records and search filter may return sometimes 5k or 20k and I need to analyze this data(so export)

Currently its only exporting 500 first items which does not meet any business criteria(imagine you are doing google search and it returns only 3 items), sadly if this is permanant issue like sp list 5k limit I will have to inform this to our sponsors of the project and most likely as it does not meet business need to filter and export we will have to do asp.net app which we did not want to do.

I will atleast need some good workaround. One thing I observed is there is export data link in CDM screen(can you give me some workaround based on that?)

[Anonymous]

@Anonymous 
I have applied the code on Screen.OnVisibile and now i have written the following code in my dropdown.items:
Distinct(Filter(datasource_temp,'Candidate Name'.DisplayName in ComboBox1.Selected.DisplayName, Date = DatePicker2_1.SelectedDate),'Project Name') 
I simply just replaced my Sharepoint list from collection ,but still not working. Am i using correct query and database ? 

[cyberco01]

I tried the below solution in my combo-box as follows :-

SortByColumns(ShowColumns(FirstN(Search(Filter(VendorsV2_3,TextInput1.Text in 'Search name'), TextInput1.Text, "VendorSearchName"),50),"VendorSearchName"),"VendorSearchName",Ascending).

This works perfectly fine ( Thanks @PaulD1 ), if i put a text-box above the combo-box and search in the text-box and corresponding value gets filtered in the combo-box.

But when i replace the TextInput1.Text to Combobox1.SearchText, nothing happens.

Can anyone help me with this.

[Anonymous]

@Anonymous 

Could you share a screenshot of your app showing what you are trying to achieve? I'm struggling to visualise you problem.

Also, what exactly is "... still not working"?

[Anonymous]

@Anonymous Never mind, I figured it out and your solution has worked perfectly for me.
Thanks.

[Anonymous]

@Anonymous 

No worries, happy to help 🙂 

[Anonymous]

I continue with this problem, I managed to execute the code correctly, and I can collect lots without problems, the problem is that you cannot store in a "collection" (also try SET) an ID above the number 2000.

Spoiler

That is, although it filters only 6 rows from ID 1997 to 2003 it only collects until ID 2000 (only 3 rows).

The intention of my code is to store a copy of a SharePoint list locally with 5,000 items, then work it locally and use SEARCH on a column.

Do you know if there is a new block?

[Mr-Dang-MSFT]

Hi @Anonymous,

It sounds like you want to collect all 5000 records from SharePoint to be able to use Search(). You are doing this because you do not want to use StartsWith as it is non-delegable with SharePoint.

There is a significant tradeoff here. 

• Getting >2000 records at a time is a heavy operation. If each record has 10 columns, that's a total 'size' of 2000*10. If any columns are complex, it's greater. This pattern impacts your user's experience: they will wait several seconds.

I recommend not using PowerApps as you would the back-end, but instead surfacing the minimum amount of data to act.

If you must use SharePoint for this scenario, you can to use StartsWith() for much better performance than collecting 5000 records. You can apply other filters ahead of the StartsWith() to pare it down even more. Another option is to use a SharePoint customized form: you have the full capabilities of SharePoint with the action taking you want to do in PowerApps.

(Side note: this thread is about CDM and filter and yours is about SP and Search. I'd suggest starting a new thread in the future so that your question appears at the top of its own and doesn't require context-switching)

[lseow]

Hello @Mr-Dang-MSFT  or anyone,

I have 4000 records in an Excel table on OneDrive for Business, and have a couple of follow-up questions based on this thread.

1) Would the ForAll() approach recommended for my data source as well? The given example seems to be using CDS as the data source.

2) If I don't want to bring all 4000 records locally into the app, and I bind the control, i.e. datatable directly with the data source, I only got 2000 records back. Is it because Excel on OneDrive for Business is a non-delegable data source?

Thanks,

Lilian

[Mr-Dang-MSFT]

Hi @lseow ,

1. The method shown is for any data source with sequential id column and was CDS in this case. 

2. The Excel and Google Sheets connectors do not see records beyond the record limit. They are perfect for prototyping because they're fast and malleable, but I would not recommend them as the final resting place for your important data.

If you need some more records than 2000 and don't expect it to grow, I have a method using a flow. But in general, I'd suggest keeping your app as lightweight as possible and filtering to bring in only what is going to be acted upon.

[liad-nizri]

i use google sheets as data source, i need more then 10K raws.

i try your solution but i get only 2000 raws.

you can see Attachments.

you have solution for it?

thanks a lot!