Hidden delegation warning with AddColumns while trying to use IN with Filter (Dataverse)

[MichalBr89]

Hi all,

for pas several days I am struggling to fix below code using Dataverse as source...

At the end I need to search currently logged user's Email in Column that contains text string of emails: "john.doe@xy.com; jane. doe@xy.com..."

For that I am using gblUser.Email in SPOCname, SPOCname is added with Addcolumns.

I was happy with the result but recently I found out that it is causing me delegation limit without saying there is any delegation limit warning...

    Filter(
//AddColumns is probably causing hidden delegation warning, however it is needed because we need to add into Assignments table "virtual" column that is pullting SPOC name from Projects table
            AddColumns(
                Filter(
                    assignments_sie,
                    StartsWith(
                        EnterpriseIDLookup.EnterpriseID,
                        TextInputSearchRes.Text
                    ) 
                ),
//Here the SPOCname is added, checking if currently logged inuser is in colFilteredProjects (collection already prefiltered from Projects Table, with projects where currently logged in user is in PMO supporting team)
                "SPOCName", 
                LookUp(
                colFilteredProjects,
                    'ACN Project_Name' = ProjectName.'ACN Project_Name'
                    ).'PMO Supporting Team'
            ),
// in is probably causing delegation warning as well however as we are searchin in text string, we need this
        gblUser.Email in SPOCName
    ))
    )

Thanks for any tips!

BR,

Michal

[BCBuizer]

Hi @MichalBr89 ,

What data source are you using? Dataverse / SharePoint / other?

[MichalBr89]

Sorry @BCBuizer  forgot to mention - Dataverse

[WarrenBelz]

HI @MichalBr89 ,

I will throw in some information here that may assist. AddColumns() is processed locally as it is a Power Apps function (Dataverse knows nothing about it) and therefore the data set that can be used is restricted to your Data Row Limit (as are non-Delegable queries, but this is nothing to do with Delegation). The best you can do here is pre-filter with the Delegable query available to you

With(
   {
      _Data:
      Filter(
         assignments_sie,
         StartsWith(
            EnterpriseIDLookup.EnterpriseID,
            TextInputSearchRes.Text
         ) 
      )
   },
   Filter(
      AddColumns(
         _Data,
         "SPOCName", 
         LookUp(
            colFilteredProjects,   
            'ACN Project_Name' = ProjectName.'ACN Project_Name'
         ).'PMO Supporting Team'
      ),
      gblUser.Email in SPOCName
   )
)

[MichalBr89]

Thanks @WarrenBelz  for this hint, I have used it elsewhere in the different code, however here I wanted to go with different workaround which is:

-I have created already prefiltered collection colFilteredProjects (datasource won't ever have more than 500 / 2000, so I am fine using non delegable IN there)

-afterwards I want to use this prefiltered collection to filter the Assignments table, that have more than 2000+ records, however issue is that I am using IN and it appears that the not all data are return in the collection... 

- I cannot use lookup as there would be multiple matches, I tried to use "=" however i am getting "cannot compare text to table" error

ClearCollect(colFilteredAssignments,Filter(assignments_sie, ProjectName.'Accenture Project_Name' in colFilteredProjects.'Accenture Project_Name'))

- Afterwards I would use the colFIlteredAssignments in Items property of Gallery along with other filters, however as now it is not returning full data set, it seems to be useless at this point 😞

Thanks a lot for any help!

Michal

[WarrenBelz · Accepted solution]

Hi @MichalBr89 ,

You could try this (also if there is only one matching record for each, you can use LookUp instead of Filter)

Clear(colFilteredAssignments);
ForAll(
   colFilteredProjects As _Data,
   Collect(
      colFilteredAssignments,
      Filter(
         assignments_sie, 
         ProjectName.'Accenture Project_Name' = _Data.'Accenture Project_Name'
      )
   )
)

[MichalBr89]

Hi @WarrenBelz  this helped, thanks a lot!

Question - is there any easy way how to refresh a collection? I have colFilteredAssignments in Items property of a gallery, let's say I have comboboxes in the gallery, that are patching some data according to input, I afterwards need to show the updated values, however without refreshing the collection I believe it is not possible.

I have used ClearCollect with the code you mentioned, however it takes approx. up to 10 seconds to collect the data in case a larger dataset which is not the nicest from the user experience. I can hide it behind "loading screen" however it still feels too slow.

Thanks!

[WarrenBelz]

Hi @MichalBr89 ,

You need to Patch to the collection also (much quicker than re-collecting)

[MichalBr89]

Thanks @WarrenBelz  - this works, however it is only reflected on gallery text labels that are tied to colFilteredAssignments collection... I do have some other text label there for example from "ActivityLog" table that is basically connected to Assignments table using lookup column in Dataverse, meaning in text label I am referencing to it as galResourceOverview.Selected.ActivityLogLookup.'Events Change Log', where galResourceOverview is gallery with colFilteredAssignments in items, so this text label is not refreshed by patching colFilteredAssignments, it is strange as before I patch assignments I am patching as well ActivityLog table so the data are for sure there, I have tried to use Refresh(ActivityLog) to see if it will be as well reflected in gallery but no luck.

Edit: it seems to be an issue with this specific case:

I have in a gallery button, that onselect will pop-up window, where I have HTML text an in HTMLtext property I have galResourceOverview.Selected.ActivityLogLookup.'Events Change Log' which is basically not updating after patching the collection...

[WarrenBelz]

@MichalBr89 , 

Without seeing your app or data, that is all a bit hard to even visualise, much less debug. You might consider posting a new thread on this as it is now quite removed from the subject of this one.