cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
beejo
Frequent Visitor

Yet another delegation/Filter() question

Okay, I'm trying to understand this.  Say I have a 'Team' Dataverse table with a 'Team Name' key, and then a 'Team Member' table with 'Team Name' as a lookup on 'Team.Team Name'.  If I create a dropdown of team names, then create a gallery of members when a team name is selected via:

 

 

Filter('Team Member', 'Team Name' = TeamName_Dropdown.SelectedText.Value)

 

 

... I get the much-adored delegation warning.

 

I see here that lookup columns don't seem to be delegable, but...

 

1) WHY?  In an SQL-based database, "select * from team_member where team_name = ?" is a common pattern, and if team_member.team_name is a foreign key, it's going to indexed and very quick.  I get that Dataverse may not be SQL-based, but I am flummoxed why filtering on a foreign key (effectively) is a penalty rather than a benefit.

 

2) What's the best practice here from a database-design standpoint?  I could maintain a "shadow" column that mirrors the lookup column and filter on that, but yuck.  Should I make the lookup column of 'Team Member' the Guid of 'Team' and just leave 'Team.Team Name' free-floating?  That's how I would do it in an SQL database, just with the foreign key being integer rather than UUID4.

 

Thanks!  This is a little frustrating.

 

EDIT: I see that you apparently can't create a Guid-type column in 'Team Member' to link to 'Team'.  So I could use an auto-number column in 'Team' ('Team.Id' I guess) and do a lookup column 'Team Member.Team_Id' and then do the free-floating 'Team.Team Name' thing.  But if that's the best practice, it seems like Dataverse is intrinsically non-normalizable; with SQL I could do "select team.name as team_name, team_member.name as team_member_name from team, team_member where team.team_id = ? and team_member.team_id = team.team_id" and everything would be nice and fast... and normalized.  But am I out of luck with that sort of thing here?

1 REPLY 1
AhmedSalih
Super User
Super User

Hello, @beejo, What do you have in the Items Property of the TeamName_Dropdown?

 

Regards,

Ahmed

If my reply helped you, please give a 👍. And if it has solved your issue, please consider a 👍 & Accepting it as the Solution to help other members of the community find it more.

My Blog: www.powerplatformplace.com

Helpful resources

Announcements
Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Users online (4,087)