Power Platform Community Forum Thread Details

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?

Categories:

Connecting to Data

Dataflows

Quick Links