cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
HansHeintz
Level 8

Filter with datasource in if construction that evaluates true: different results then without if construction

I am trying to let my users choose to search recent data (which is cached in a collection) or all data (the sql datasource itself).

If I do 

ClearCollect(ZoekDieren;SortByColumns(Filter('[dbo].[dier]_1';eZoekTekst.Text in naam);"identificatie_nummer_asiel";Descending))

I get expected results with a certain search string.

But If I do

ClearCollect(ZoekDieren;SortByColumns(Filter(If(!Toggle2.Value;'[dbo].[dier]_1';CachedDieren);eZoekTekst.Text in naam);"identificatie_nummer_asiel";Descending))

with toggle2.value=false (so !toggle2.value=true) I get only results from the first part of the table, so it is doing its delegation thing on me.

 

  • zoekdieren is the collection that is the items propert of my gallery, cacheddieren is collected from the table at app start
  • If I toggle I get different results altogheter so that is not the problem.
  • ; not , is normal in holland

 

How can this be?

2 REPLIES 2
Super User
Super User

Re: Filter with datasource in if construction that evaluates true: different results then without if construction

Hi @HansHeintz 

I would try placing your If function outside of your Filter function. Perhaps that might solve your delegation problem?

 

ClearCollect(ZoekDieren;
             SortByColumns(
                           If(!Toggle2.Value;
                              Filter('[dbo].[dier]_1';eZoekTekst.Text in naam);
                              Filter(CachedDieren ;eZoekTekst.Text in naam)
                           );
                           "identificatie_nummer_asiel";
                           Descending
              )
)
Community Support Team
Community Support Team

Re: Filter with datasource in if construction that evaluates true: different results then without if construction

HI @HansHeintz,

How many records stored in your SQL Table? More than 2000?

 

Based on the issue that you mentioned, I think this issue may be related to Delegation issue. When you load data from your '[dbo].[dier]_1' table into the CachedDieren collection, in default, it could only load 500 records from '[dbo].[dier]_1' table into the CachedDieren collection.

1) If the amount of your  '[dbo].[dier]_1' table records is not more than 2000, please consider set the "Data row limit for Non-delegable queries" option to maximum value -- 2000 within Advanced settings of App settings of your app, then you could load 2000 records at most from '[dbo].[dier]_1' table into the CachedDieren collection, then try your formula again, check if the issue is solved.

 

2) If the amount of your  '[dbo].[dier]_1' table records is more than 2000, please check and see if the alternative solution mentioned within the following thread would help in your scenario:

https://powerusers.microsoft.com/t5/General-Discussion/Pulling-in-large-ish-SQL-tables/m-p/243777#M7...

Set the OnStart property of the App control to following (I assume that there are 10000 records stored in your SQL Table😞

Concurrent(
    ClearCollect(col1, Filter('[dbo].[dier]_1', recordID >= 1 && recordID <= 2000)),
    ClearCollect(col2, Filter('[dbo].[dier]_1', recordID >= 2001 && recordID <= 4000)),
    ClearCollect(col3, Filter('[dbo].[dier]_1', recordID >= 4001 && recordID <= 6000)),
    ClearCollect(col4, Filter('[dbo].[dier]_1', recordID >= 6001 && recordID <= 8000)),
    ClearCollect(col5, Filter('[dbo].[dier]_1', recordID >= 8001 && recordID <= 10000))
);
ClearCollect(CachedDieren, col1, col2, col3, col4, col5)

Note: I assume that there is a Identity(1,1) column (e.g. recordID column) in your SQL Table to identify the row index.

then modify your formula as below:

ClearCollect(
ZoekDieren;
SortByColumns(
Filter(
If(!Toggle2.Value; '[dbo].[dier]_1'; CachedDieren);
eZoekTekst.Text in naam
);
"identificatie_nummer_asiel";
Descending
)
)

Or

ClearCollect(
ZoekDieren;
SortByColumns(
If(
!Toggle2.Value;
Filter('[dbo].[dier]_1', eZoekTekst.Text in naam),
Filter(CachedDieren, eZoekTekst.Text in naam)
);
"identificatie_nummer_asiel";
Descending
)
)

then re-load your app (fire the OnStart proprety of the App control), then check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
thirdimage

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

thirdimage

Power Apps 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

Top Solution Authors
Top Kudoed Authors
Users online (4,740)