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

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

@mr-dang, @Meneghino, @v-yamao-msft, hpkeong

One thing I noticed is "IN" statement works great when you have data already loaded in collection!

I have data loaded in CachedInstruments4 and I am using checkbox check, uncheck to filter records based on countries selected.

 

OnCheck

Collect(CachedInstruments4,Filter(CachedInstruments3,Country in ThisItem.Country))

 

On uncheck

Remove(CachedInstruments4,Filter(CachedInstruments4,Country = ThisItem.Country))

Meneghino
Level 10

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

Hi @AmitLoh-Powerap

Yes, indeed that is the case for the in operator as a test of membership.

We really need to get this operator properly delegated when used a test of membership.

I have written this post, but have had no luck in getting clarification yet:

https://powerusers.microsoft.com/t5/PowerApps-Forum/quot-in-quot-operator-as-test-of-membership-and-...

mr-dang
Level 10

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

Below are some metrics with my new method. The internet connection used is not as quick as my school internet which my users use.

 

5 entities = 10,803 records total = 34 iterations of up to 500 records = about 22 real groups of 500

 

Elapsed time trials: 44s, 39s, 40s, 44s, 42s

 

  • Average elapsed time: 41.8s
  • Average number of records loaded per second: 258 records
  • Average elapsed time per loop (iteration): 1.22s/loop
  • Average elapsed time to cache 500 records: 1.9s/cache of 500

These tests were run on the same network as a previous test I ran. Below are some comparisons:

  • There were 65% more records this time.
  • There were 24 loops last time. The new test sample has 42% more iterations.
  • The average time elaspsed this time is about the same as the fastest time last time (41s).

Therefore, the changes to PowerApps version 2.0.590 and this method have up to a combined 42-65% improvement in performance. Since I no longer need to write a column identifying which block of 500 a record belongs to, there are also improvements in writing. That represents about 50% less writing time since I previously wrote new records twice: once for the record with the main information, and second for calculating n as RoundUp(PrimaryId/500,0).

 

Although there is a performance improvement, I would still judge 40+ seconds of caching a long time for end-users even if it's a one time block at the start. If it is 40s right now, then with more usage of the app, the time will gradually increase to unsustainable levels. If Sum() or CountRows() could operate on any one of the entities in its entirety or if there were a one-click Cache() function, I could easily cut out at least 10s or more. 

Microsoft Employee
@8bitclassroom
Meneghino
Level 10

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

hi @mr-dang

thanks for the update, very useful.

yesterday I added this idea, please upvote:

https://powerusers.microsoft.com/t5/PowerApps-Ideas/Extend-delegation-to-Sum-and-CountRows/idi-p/231...

 

not sure it will help, but at least they cannot say we did not ask

VipinSinghal
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,

 

 

I am using an excel file as a database in powerapps and i am trying your logic but it is not working, powerapps doesnt read the content where recordid is more than 500. It is only showing me 500 records only. Please help!!! Thanks.

Meneghino
Level 10

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

Excel is not a delegable data source (yet) so that you will only obtain the first 500 records.

See here for some ideas:

https://baizini-it.com/blog/index.php/2017/08/29/powerapps-101-data-storage/

 

VipinSinghal
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, Thanks for the information.

 

So i have to use Azure Sql DB as a database for better result?

 

 

Meneghino
Level 10

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

You would get much better results

tianaranjo
Level 8

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

Hoping for some help here.  I have a SP list with just over 1000 items.  OnStart, I would like to filter to only pull in items if the Date field is this year or last year.  Is this possible?

Meneghino
Level 10

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

The best way to have your cake and eat it (i.e. filter and have the filter operation delegated) would be to create a calculated column in SharePoint as Year([Date]), then use this column (Let's call it DateYear) to filter.  So you can have this expression in the OnStart:

ClearCollect(MyFilteredItems, Filter(MySpList, DateYear=Year(Today()) || DateYear=Year(Today())-1))

If this is not delegated because of the indeterminacy of Today(), then try this in the OnStart:

Set(CurrentYear, Year(Today()));
ClearCollect(MyFilteredItems, Filter(MySpList, DateYear=CurrentYear || DateYear=CurrentYear-1))

Please let me know how you get on

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

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

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (5,027)