Designing dataverse tables

[Saideep]

Hi,

I am using two dataverse tables, Main Table and Child Table. In the Main Table I am using a lookup column, say Players, that refers to Child Table column and get values from there. I am displaying this Main Table in a form in Canvas App. With look up column in the form, I can allow multiple selection in the App for Players column, but the data stored in the Main Table for this look up column Players wont store multiple values, it only stores a single value though multiple values are selected in the app. It seems that look up column has this limitation or if there is anything I am missing !

I wanted to know if there is any other way we can do or design dataverse tables in a way that fits the above requirement.

I am using the Child Table because, I have to add or delete values, so the process becomes dynamic.

Thanks.

[MattB_Motive]

Hi @Saideep 

A lookup column in Dataverse creates a one-to-many relationship, so yes, by design you can only assign one record to the lookup column.

[Saideep]

Hi @MattB_Motive ,

Is there an alternate way of doing to achieve the above requirement.

All I need is a way to populate choice values in a combo box to allow multiple selection and all selected values should be stored in that column, and, the ability to dynamically add and delete the choice values. 

Thanks.

[skoofy5]

You've got two choices:

• Option 1:
  • Configure the relationship to be many-many (see also - Create N:N (many-to-many) table relationships in Microsoft Dataverse using solution explorer - Power...)
  • Considerations:
    • Virtual tables (used in N:N relationships) are limited to returning 1000 records. Queries exceeding this limit will fail.
    • N:N relationships rely on an intersect entity (a special table) to manage associations. Custom fields cannot be added to this intersect entity.
    • If you need additional properties or custom behavior, consider creating a custom intersect entity. This gives you more control over the relationship.
  • Configuration: Create a new many-to-many relationship

• Option 2
  • Create a custom intersect table to relate the two tables together:
    • Main Table -> Join Table <- Child Table
  • Considerations:
    • Native N:N relationships can be queried via oData or FetchXML, but not directly through standard interfaces.
    • Unlike native N:N relationships, custom intersect entities do not appear in standard user interfaces (e.g., Advanced Find).
    • You have more flexibility with a custom intersect entity. You can add additional columns, define business logic, and maintain your own IDs for readability and traceability.
    • Keep in mind that the number of custom tables (entities) you can add to an app depends on your licensing. Refer to the Custom Entity Limit in a Model Driven App for details.
    • Custom intersect entities are generally easier to work with during data import processes.
  • Configuration: Create an additional table joined to the others with one-many relationships.

Here's an old article that can talk you through it in a little more detail - https://carldesouza.com/understanding-intersect-entities-and-nn-relationships-in-dynamics-365/

[MattB_Motive]

Hi @Saideep 

As @skoofy5 outlined, you will need to create a many-to-many relationship. This might be helpful to understand how they work in canvas apps: https://youtu.be/L_WaeffrMcc?si=yqT2lgkYnUWXC9oj