Filter using LookUps on SharePoint Lists: An error occurred on the server. Server Response: Not a valid connector Error response

[maggie]

Hey PowerApp friends,

I've brought this error to Microsofts attention, but I haven't heard back from them and this app is in production having an issue. I'm using gallery to filter a SharePoint lists, but for each condition I'm having to use lookups to reference a different lists. I've used similar code elsewhere but it gives me an error on my production app, test apps, and brand new apps. The error I'm recieving after putting this code in says "An error occurred on the server. Server Response: Not a valid connector Error response." I've posted filter code below. Insight is welcomed, thank you so much!

SortByColumns(Filter(Table1,
     User().Email = 'Created By'.Email ||
     User().Email = LookUp(Table2, UniqueID = UniqueID).Supervisor.Email ||
     User().Email = LookUp(Table3, DepartmentCode = LookUp(Table2, UniqueID = UniqueID).DepartmentCode).Department_x0020_Director.Email),
"Created",Descending)

[v-xida-msft · Accepted solution]

Hi @maggie ,

Could you please share more details about the error message within your app?

Based on the issue that you mentioned, I have made a test on my side, and don't have the issue that you mentioned. According to the error message you provided, it seems to tell that there is something wrong with the SP List connection in your app.

Please consider remove the SP List connections in your app, then re-add them again in your app, check if the issue is solved.

Please take a try to modify your formula as below:

Set the OnStart property of App to following:

Set(CurrentUserEmail, User().Email)

then modify your formula as below:

SortByColumns(
             Filter(
                    Table1,
                    CurrentUserEmail = 'Created By'.Email ||
                    CurrentUserEmail = LookUp(Table2, UniqueID = Table1[@UniqueID]).Supervisor.Email ||
                    CurrentUserEmail = LookUp(Table3, DepartmentCode = LookUp(Table2, UniqueID = Table1[@UniqueID]).DepartmentCode).Department_x0020_Director.Email
             ),
            "Created",
             Descending
)

re-load your app (fire the OnStart property of App), then check if the issue is fixed.

Also please consider save your SP Lists records into collections, then use the Collections as data source instead of the Original SP List within your app.

Please set the OnStart property of the App control to following:

ClearCollect(List1Collection, Table1);
ClearCollect(List2Collection, Table2);
ClearCollect(List3Collection, Table3)

Then modify your SortByColumns(...) formula as below:

SortByColumns(
             Filter(
                    List1Collection,
                    User().Email = 'Created By'.Email ||
                    User().Email = LookUp(List2Collection, UniqueID = List1Collection[@UniqueID]).Supervisor.Email ||
                    User().Email = LookUp(List3Collection, DepartmentCode = LookUp(List2Collection, UniqueID = List1Collection[@UniqueID]).DepartmentCode).Department_x0020_Director.Email
             ),
             "Created",
             Descending
)

please take a try with above solution, then re-load your app (fire the OnStart property of the App), check if the issue is solved.

Note: Please make sure that you set the "Data row limits for Non-delegable queries" option within Advanced settings of App settings of your app to maximum value -- 2000.

Best regards,