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?)

[jesenavaranjan]

Hi! You can use two collections and then join them together.  Given you have some sort if ID to sort on. I created one to combat this issue. 
E.g.
Concurrent(    ClearCollect(        Collection1,        Filter(            Table1,            ID < 2000        )),
    ClearCollect(       Collection2,        Filter(            Table1,            ID >= 2000 && ID < 4000        )),    
;
    ClearCollect(        TableX,   Collection1,       Collection2    )
 

The question is always why do we need this many data points? So I sorted by ID based on records which had been touched at all, so any records beyong 4000 which hadn't been touched do not appear in my power app.

& then use TableX ,Hope this helps! Jese 

[Mr-Dang-MSFT]

Hi @abd3127 ,

Thanks for asking--my solution is very dated. I created it at a time when I needed to aggregate my data. I needed to perform a sum against the entire data source. At the time, there were not ways of doing this as I would hit delegation issues. So I pulled in all the data I had at the time, which was thousands of records and not very performant. Now, I've overcome those issues with SQL Views and aggregates in the Common Data Service.

I would not suggest pulling in all of that data today even if you could. When designing an app, you should only bring in the relevant data to keep the app as lightweight as possible. You can search for some patterns on this forum for filtering your data further to minimize the load.

[Anonymous]

@abd3127 I built a solution for this for SharePoint Lists last week but would agree with @Mr-Dang-MSFT and question why you need all the data getting collected into your App. The solution for SP users is to get PAs to capture SP Views thereby saving the Filter() / Sort() / etc delegation issues.

My solution isn't overly elegant but it works and here it is (apologies for the long post):

The solution is to use SharePoint ID numbers.

1. First, sort by ID Descending, collect that into collection1 (col1)

2. Then, sort by ID Ascending, collect that into collection2 (col2)

3. Build an array of 'Missing ID numbers' as a collection - call it colTarget. Calculated by (MinID col1) - (MaxID col2)

4. Use ForAll() with Collect() to get the missing ID rows one by one into collection3 (col3)

5. [optional] Collect col1, col2 & col3 into finalCol

Step 4 can take awhile if 'the gap' between Min/Max is large so just be mindful of that. I put a label in my Dev App (see below pic) and set it to CountRows(col3) and you can see it tallying up as data gets collected - gives you an idea of data collection speed.

You could always build a smaller array (Step 3) of missing IDs that gets tacked onto col1 if you don't need all of the data Eg. (MinID col1) - 500 instead of (MinID col1) - (MaxID col2)?

Note: my Step 3 is adjusted from @Prem_ddsl's cool code here Dynamic Loops

Step 1:

ClearCollect(col1,SortByColumns(yourSPList,"ID",Descending))

Step 2:

ClearCollect(col2,SortByColumns(yourSPList,"ID",Ascending))

Step 3:

//specify target rowcount

Set(vMaxRows,Min(col1,ID)-Max(col2,ID))

//define a base collection from which you will generate your target collection. A colBase of 60 will be able to collect up to 60x60 = 3600 items. Set colBase's size to meet your needs.
ClearCollect(colBase,[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60]);

//setup collections to be used as inner and outer counters
ClearCollect(colOuter,colBase);ClearCollect(colInner,colBase);Set(vOuterMax,CountRows(colOuter));

//generate array of missing ID numbers 
ForAll(colOuter,ForAll(colInner,Collect(colTarget,{RowId: (Max(col2,ID)+colInner[@Value])+(vOuterMax*(colOuter[@Value]-1))})));

RemoveIf(colTarget,RowId>=Min(col1,ID));  // remove unwanted ID numbers from ID array

Step 4:

Clear(col3));

ForAll(colTarget,Collect(col3,Filter(yourSPList,ID=RowId)));

Step 5:

ClearCollect(finalCol, col1, col2, col3)

As  I say, not elegant but effective.

And the pic of my Dev App counting fields

This SPList contained around 4500 items

Hope this helps.

Cheers

[cyberco01]

I am using D365 as datasource for my combo box. I have more than 2k records to be populated on combo box. How can achieve it. I am not understanding where to paste the code to test. Completely new to powerapps. Please guide and help.

[jesenavaranjan]

You have to build more than 1 collction.

And then join the collections together to build a dataset of the combined data.

Collections store the data on Entry,in the cache.

    ClearCollect(        Table1, Filter(
            Datasource,
            ID<2000
        ));  ClearCollect(        Table2, Filter(
            Datasource,
            ID>2000
        )); 

ClearCollect(Finaltable, Table1,Table2).

I hope this helps understand the pattern.
   

[cyberco01]

@jesenavaranjan,
I do not have an id field instead i hve a vendor account field tht is alphanumeric. Kindly help.

[Anonymous]

@jesenavaranjan 

Your Filter() code on ID still won't get more than 2000 records from Sharepoint. This is a limitation within PowerApps.

As a test, I ran your code on a SP list that I have access to with over 4500 records and the stats speak for themselves - only 2000 records total returned by PowerApps. I have set the data row limit to 2000 in Adv settings.

Cheers

[PaulD1]

I had similar requirement and took a slightly different approach.

Instead of a combo box, I use a Gallery with a text 'searchbox' above. 

The gallery populates with the first 50 items from D365 that contain the text in the searchbox.

SortByColumns(
    ShowColumns(
        FirstN(
            Search(
                Filter(
                    Accounts,
                    'Account type' = 866100002
                ),
                txtSearchGalAccounts.Text,
                "name"
            ),
            50
        ),
        "name",
        "accountid",
        "accountnumber",
        "_fbh_accounttypece_label",
        "_fbh_primaryaddress_value"
    ),
    "name",
    Ascending
)

When the user clicks on an entry in the Gallery, the entry is selected and passed to the relevant screen/control.

Advantages: Deals with any number of records - if >50 items returned, user should just keep typing to make the search more specific, users find a Gallery much easier to use on mobile devices (where the combo box is a UI nightmare).

Disadvantages: Must be online, every search is querying D365 directly, results can take a few seconds to appear.

[Anonymous]

I have been getting delegation warning in my dropdown,comboboxes and gallery and reason is showing up beacuse of "in" operator. Also there is more than 2000 records in my sharepoint list. So where should i put above code? In dropdown.items or in Screen.Onvisible? I am a bit confused on that part. Help me out here.

[Anonymous]

@Anonymous 

You need to collect all your sharepoint data into collections in the App first then run your dropdowns, combos, galleries off of the collections not your SP list. That way 'in' should work for you.

Run my code in the App start or Screen.OnVisible properties