Answered

Delegation issue in Dataverse filtering looked-up data

(0) Share

Report

Posted on by MalyMajo

Hello,

I am facing issue querying through data in my canvas app. There are 2 Dataverse tables - 'Projects' and 'Assignments' (see examples at the bottom).

Here is a scenario:

I am logged in to the app as User().Email = 'support2'. This means, that I am supporting projects 'Project1' and 'Project2'.

When I open app, I want to see all the resources assigned to the projects, where I am assigned.

So the formula in Items of Resources gallery, looks like this:

Filter(Assignments, User().Email in LookUp(Projects, ProjectName = ProjectName).SupportTeam)

Is there a way to make this delegable? Assignments table contains 2000+ rows.

Projects table

ProjectName	SupportTeam
Project1	support1; support2; support3...
Project2	support2; support4; support5
Project3	support6

Assignments table

Resource	ProjectName
resource1	Project1
resource2	Project1
resource3	Project2
resource4	Project3

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (1)

Sort by

Rajkumar_M 3,747 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @MalyMajo

Create a collection on app start to store the projects supported by the logged-in user. This is a one-time operation and will not be affected by delegation limits.

// On the OnStart event of the app
ClearCollect(
ColSupportedProjects,
Filter(
Projects,
User().Email in SupportTeam
)
);

Use the created collection to filter the Assignments table. Since the collection ColSupportedProjects will have all the projects that the logged-in user supports, you can use it to filter the Assignments table.

// Items property of the Resources gallery
Filter(
Assignments,
ProjectName in ColSupportedProjects.ProjectName
)

Use AddColumn function also

Filter(Assignments, User().Email in
LookUp(
AddColumn(
Projects, ProjectName,prjna),prjna = ProjectName).SupportTeam
)

Thanks!

If my response has been helpful in resolving your issue, I kindly request that you consider clicking "Accept as solution" and "giving it a thumbs up" as a token of appreciation.

Was this reply helpful? Yes No
MalyMajo 87 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Rajkumar_404 ,

second formula (Items property of the Resources gallery) is non-delegable due to 'in'.

Was this reply helpful? Yes No
Verified answer

MalyMajo 87 on at

Like
a
(0)

Report
Copy link

Link copied!

Ok, seems like the second formula was delegable in the end, but combination of LookUp with in is non-delegable.

We have found a workaround, where we patch the project data to assignments table when resource is onboarded. It is not the best solution in terms of data redundancy, but it works.

Was this reply helpful? Yes No