Share
Report
32Okay, 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?
All responses (
Answers (
