Skip to main content

Notifications

Announcements

🌸 Community Spring Festival 2025 Challenge 🌸
Welcome to the Power Platform Communities
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps - Microsoft Dataverse / Dataverse Lookup field...
Power Apps - Microsoft Dataverse
Suggested answer

Dataverse Lookup field: How does it work behind the scene?

(1) ShareShare
ReportReport
Posted on by ctrl_kudorje 125
When setting up a lookup field in a Dataverse table to the another table, how does Dataverse know which fields are being joined between the two tables. How does the setting up of the Lookup column know which fields are the join going to be based on? 
Categories:
Microsoft Dataverse
  • Suggested answer
    ronaldwalcott Profile Picture
    ronaldwalcott 2,044 on at
    Dataverse Lookup field: How does it work behind the scene?
    You are essentially creating a link to the ID of the other table if that is what you are asking. Each record has a unique ID. This is what is used. 
    It is not a SQL join if that is what you are asking. Also SQL joins are created when necessary as the tables are not linked and you are linking them when necessary using a related column. 
  • Suggested answer
    Michael E. Gernaey Profile Picture
    Michael E. Gernaey 38,788 Super User 2025 Season 1 on at
    Dataverse Lookup field: How does it work behind the scene?
     
    It depends a little on the scenario
     
    1. Standard or Custom Tables
    2. Virtual Tables
    3. Many to Many relationships versus N1: or 1:N
     
    For 1:N / N:1
    For Standard or Custom (standard but not system) with 1:N N:1 , dataverse creates a reference between them, not.. per se linked to a specific field, as it can be the ID field, aka the unique identifier, or it can be a secondary key, so its not just limited to the GUID which is why I said its not
    .
    However, when you created your tables, when you create a relationship, it will have to show "something" in the column, so it will show the Primary Column that you assign, so pick that carefully so the data showing makes sense.
     
    This will cause the creating table to have a Column, that will display the Primary Column Data in it (from the other record).
     
    For Many to Many
    Dataverse creates another entire back end "mapping" table that you cannot access or add to. It does this because its a matrix now of references, not a single one. You have no control over this table and you don't see them in the UI as a single column like 1:N or N:1
     
    YOU can create your own Many to Many relationship by created your own table and then adding in rows where you associate records from A to records to B and vice versa
     
    The benefit to this is, you can also add other data points to each row, which you cannot do with the built in hidden many to many. Many of the older timers and I did this because we wanted to track information between the two table or in even a polymorphic relationship, versus just storing GUID1 and GUID2 (and event) GUID3/4/5/ etc
     
    It might be something like
     
    students to classes where classes have many students and students have many classes, but you also want to track IF a user attended or not and what date. Doing your own Many to Many allows that.
     
    For Virtual tables
    You can have 2 types
    1:N / N:1 and Custom multi-table (polymorphic) relationships
     
    Normally you would leverage the primary unique field in Dataverse (the GUID), and then have that as a foreign key in your Virtual Table.
     
    NOTE: You will hear Relationship and Many to one, but then see N:1 or 1:N and there is no wording of Many to one in the visual you pick :-)
     
    This is how they work in the back end.
     
    If this answers your Question please Mark as such and maybe a like :-)
     
    If you have any other questions after please let me know
     
     
     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

🌸 Community Spring Festival 2025 Challenge 🌸
Welcome to the Power Platform Communities

Quick Links

🌸 Community Spring Festival 2025 Challenge 🌸

WIN Power Platform Community Conference 2025 tickets!

Markus Franz – Community Spotlight

We are honored to recognize Markus Franz as our April 2025 Community…

Kudos to the March Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 146,518 Most Valuable Professional

#2
RandyHayes Profile Picture

RandyHayes 76,287 Super User 2024 Season 1

#3
Pstork1 Profile Picture

Pstork1 65,692 Most Valuable Professional

Leaderboard

Featured topics

Product updates

Microsoft Power Platform Community release plans