Collections, Delegation & workarounds for Filter - SQL

[Lefty]

Hi,

I've learnt the following the hard way and need some help please I am using SQL tables and views and its a canvas app.

I have set my Data row limit to 2 in settings and found that a lot of my data is no longer being displayed in the various different screens, even though in most screens I have no delegation warnings in my code (this was the reason for setting the data row limit to 2, so I can identify where I will face problems).

I had created collections, thinking I would overcome the issue where after 2000 records (once I set this to the max data row limit), the collection would capture all data +2000 but this is not the case, and the data row limit value is what is being captured by the clearcollect fx, i.e. if i set it to 2 then 2 records are stored in the collection.

This is one of many issues, the following code snippets do not present me with any delegation warnings, yet setting the data row limit to 2, only presents me with 2 records from the SQL view, and of course creating a collection did not do any better, any ideas how I overcome this:

      GroupBy(
      Search(
             Filter(Time_view, vAllowOverrideUG = true || UGroup=vUGroup), // variables set at App OnStart
             TextInpSearch_1.Text,
             "UName", "Role", "UnitDes"),
             "UName", "GroupedDATA"
     )
     

 Second issue, which leads me to believe my issue is with filtering again directly calling the SQL table or using collections, if the data row limit is set to 2, I only get 2 records:

Filter(Profile,
    //colProfiles, 
          
      (
         (vAllowOverrideUG || PRegion = vUGroup) 
         || PRegion = "" 
         
      )
)

Is there a better way around my scenarios?

In the following instance I will have to use collection, where the 'Not' part of the code does throw a delegation warning, and I had initially thought I had overcome this by using collections, but it was wishful thinking as the delegation warning had gone, but clearly the delegation issue had no:

GroupBy(
    Search(
        Filter(
            colJobMapping,
            Not(PIndex exactin GalDeleteProfile.AllItems.PIndex)
            &&         
      (
         (vAllowOverrideUGroup || Region = vUGroup) 
         || Region = ""
       )
        ),
        TextInpSearchProfileToTitle.Text,
        "JobD"
    ),
    "PIndex",
    "Region",
    "ProfileGroupedDATA"
)

I have read I can create multiple collections and then combine them (do I need to know how many records I have at any given time), the data set is expected to really grow, with 0000s of records expected to be part of the various tables.

Any help/guidance on this would be appreciated.  

[WarrenBelz]

Hi @Lefty ,

Before I delve deeper into your issues, GroupBy is a "hidden" delegation limit and will only act on the first xxxx (Delegation limit) of records, as are Collections (they will only collect the same). The second one has me a bit puzzled as it should be Delegable assuming both the "v" items are Variables.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[Lefty]

Hi @WarrenBelz 

Thanks for having a look.

is there a work around for the GroupBy function if that indeed is what part of my issue is? I couldn't think if what else to use, as I wanted to group all UName

Yes both the "v" items are variables being set at App OnStart here is how I am setting them:

Set(vUGroup, 
    LookUp(Time_view, EmailAdd=User().Email).UGroup
);
Set(vAllowOverrideUG, 
    User().Email in Filter(Time_view, DepartmentC = "RET321").EmailAdd 

[WarrenBelz]

Hi @Lefty,

For the second item, you might try some different bracketing like this (if I am reading your logic correctly)

Filter(
   Profile,
   (
      vAllowOverrideUG || 
      (
         !vAllowOverrideUG && 
         (Len(pRegion) = 0 || PRegion = vUGroup) 
      )
   )
)

however back to your question on GroupBy - you can "pre-filter" as below, but you also need to output (the input can be any size data) from the With() statement less than your delegation limit.

With(
   {
      wList:
      Filter(
         colJobMapping,
         !(PIndex exactin GalDeleteProfile.AllItems.PIndex) &&         
         (
            (vAllowOverrideUGroup || Region = vUGroup) 
            || Len(Region) = 0
         )
      )
   },
   GroupBy(
      Search(
         wList,
         TextInpSearchProfileToTitle.Text,
         "JobD"
      ),
      "PIndex",
      "Region",
      "ProfileGroupedDATA"
   )
)

 Note that Len() =0 covers IsBlank, IsEmpty and = ""

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[Lefty]

Hi @WarrenBelz 

Thanks for your efforts in coming back to me, I have attempted both suggestions and feedback on both, sorry for the lengthy response, as I need to explain (don't know how to make the comments in a different colour for ease of reading):

I have again for testing purposes set the data row limit to 2 (to allow me to see if I will face any hidden delegation issues, but only get 2 records doing it as you have suggested but also a slightly couple of changes as mentioned in the code comments:

Filter(
   Profile, // This is not a collection, so I expected the data row limit not to cause only 2 records to appear, but it only displays 2 records (I have 20+ test items currently)
   (
      vAllowOverrideUG || //Setting this variable at App OnStart, to override and display all values to a user, regardless of what region they are in
      (
         !vAllowOverrideUG && 
         (Len(pRegion) = 0 // I no longer need this, although I have tried it as is and as you suggested, but even then only got 2 records (I am the admin (vAllowOverrideUG), so I expect to see all records), now the SQL team have made a change and the record will never be blank either will be set to "ALL", or a specefic region which is where vUGroup comes in
|| PRegion = vUGroup) 
      )
   )
)

I've tried your second suggestion and oddly I still get 2 records, I changed it slightly, in that rather than pointing to colJobMapping I changed it to the data source, as the data row limit of 2 will always apply to my collection, but even with me setting to filter the SQL table directly I only got 2 records, issue is though I need to use a collection in this instance as I am doing a Not function on my gallery, which displays a delegation warning anyways:

With(
   {
      wList:
      Filter(
         colJobMapping, // tried both a collection (which as expected would only show 2 records) but also the SQL table, but again only 2 records
         !(PIndex exactin GalDeleteProfile.AllItems.PIndex) &&         
         (
            (vAllowOverrideUGroup || Region = vUGroup) 
          //  || Len(Region) = 0 // I removed this as this no longer applies 
         )
      )
   },
   GroupBy(
      Search(
         wList,
         TextInpSearchProfileToTitle.Text,
         "JobD"
      ),
      "PIndex",
      "Region",
      "ProfileGroupedDATA"
   )
)

Any ideas where the issues are please?

I have noted another odd thing, I have a ComboBox and it's Items property is simply set to JobT which is a SQL Table, but even this ComboBox displays 2 items there are again 20+.... is this really how it ought to behave, when I have no Filter, Group etc. functions on this ComboBox and all I have done is set the data row limit to 2?

[WarrenBelz]

@Lefty ,

Before we go any further here, setting your Delegation limit to 2 will do exactly that (retrieve 2 records in a With() statement or collection) as both of those are "hidden" Delegation limits. The point of the With() statement is a "pre-filter" in which the input can be of any size if the contained filter is Delegable, but it only "stores" record numbers up to your Delegation limit. If you then are able to retrieve under 2,000 records with this (I assume Not() is delegable in SQL)

Filter(
   colJobMapping, 
   !(PIndex exactin GalDeleteProfile.AllItems.PIndex) &&         
   (vAllowOverrideUGroup || Region = vUGroup) )
)

then the rest of the code is processed locally and not subject to Delegation limitations. To check this, but the code above in a Gallery with your limit set to 2 and see what it returns.

A Combo Box Search function is subject to Delegation, but I would have thought it would return all items.

This one is interesting

Filter(
   Profile,
   (
      (vAllowOverrideUG || PRegion = vUGroup) 
      || PRegion = ""    
   )
)

and assuming vAllowOverrideUG is a Boolean Variable, PRegion is a Text field in Profile and vUGroup is a Text Variable, it should definitely be Delegable.

[Lefty]

EDITED ----I've been doing some more testing and have updated what I initially responded with in green

Hi @WarrenBelz 

Thanks

So I have removed the With part of the code, and only used the following on the gallery Items property, but still only get 2 items displayed in the gallery:

Filter(
   colJobMapping, // I changed this to the data source (SQL Table), as the collection is only collecting 2 items, so this test wouldn't work anyways?
   !(PIndex exactin GalDeleteProfile.AllItems.PIndex) &&         
   (vAllowOverrideUGroup || Region = vUGroup) )
)

But, if i remove the Not part of the code then I get all records, even if I have set the Data row limit to 2:

!(PIndex exactin GalDeleteProfile.AllItems.PIndex) &&

The problem is, this is important part of my solution as well, I do not want the user to select an item already present in the GalDeleteProfile (can do some logic to not allow the same item to be added, but not a good user experince)

PRegion is a Text field in Profile Table, and so is vUGroup, and yes vAllowOverrideUG  is true/false. This is how I am capturing the values in the variables on App OnStart:

Set(vUGroup,
    LookUp(T_view, EmailAd=User().Email).UGroup
);
Set(vAllowOverrideUG, 
    User().Email in Filter(T_view, DCode = "YTR564").EmailAd

and back to the ComboBox, yes I still only get 2 records when I set its item property to this:

Filter(
   Profile, // SQL Table
   (
      (vAllowOverrideUG || PRegion = vUGroup) 
      || PRegion = "ALL"    
   )
)

 What am I doing wrong 

[WarrenBelz]

Hi @Lefty ,

The second item continues to make no sense to me - you can probably change the bracketing (as I would), but if all of that is Text, it should be Delegable.

Filter(
   Profile,
   (
      vAllowOverrideUG || 
      (PRegion = "ALL" || PRegion = vUGroup) 
   )
)

For the first one, if Not() is not Delegable in SQL (I was unsure), the principle is to get anything Delegable into the With() statement (such as your Search) hoping it will filter under your limit and then do the rest at the bottom. I do not have your latest version, so you might have a go with this in mind.

[Lefty]

Thanks for all your efforts @WarrenBelz 

I have just attempted to change the bracketing around, but again I get the same results if I set the data row limit to 2, then I only get 2 records.

I haven't used the With function much, so will need to really play around with this if I have any chance to understanding the problems.

@timl Would you have any ideas why I've struggled with this, and any suggestions of overcoming my problems. Apologies we have been trying a few things, so the post is long