cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jesenavaranjan
Level: Powered On

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

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 

PowerApps Staff Mr-Dang-MSFT
PowerApps Staff

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

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.

Eelman
Level: Powered On

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

@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

Get all SPList data pic.jpgThis SPList contained around 4500 items

Hope this helps.

Cheers

 

 

cyberco01
Level 8

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

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
Level: Powered On

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

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
Level 8

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

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

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

@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.

 

ID grab from SharePoint.jpg

 

Cheers

Super User
Super User

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

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.

Sahil_s
Level: Powered On

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

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.

Eelman
Level: Powered On

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

@Sahil_s 

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

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

SecondImage

PowerApps Monthly Community Call

Tomorrow, September 18th at 8am PDT

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Top Kudoed Authors
Users Online
Currently online: 110 members 3,911 guests
Please welcome our newest community members: