Microsoft Dynamics 365 | Integration, Dataverse...

Joining Entities on Specific Column Only

(0) Share

Report

Posted on by Dereck

I have a two tables in SQL Server, for the purpose of this question, lets say they are called TableA and TableB.

Both of these tables contain an Id column which can be used to join the two tables together.

For example:

SELECT *
FROM TableA a
INNER JOIN TableB b on  a.Id = b.Id

In Dynamics, I have also created TableA and TableB, and imported the data.

Although I'm confused how to create a relationship based on the one specific Id column. If I create a relationship it seems to want to do its own join, so I'm getting stuck how do to this. Can anyone suggest a guide for this?

All responses (2)

Answers (0)

Anrini on at

Like (0)

Report

RE: Joining Entities on Specific Column Only

I hope this will be useful

www.sqlbi.com/.../
Suggested answer

Wahaj Rashid 11,321 on at

Like (0)

Report
RE: Joining Entities on Specific Column Only

Hi,

Thank you for your query.

In Dynamics 365, you cannot create a relationship based on a custom column. The relationship is created on ID (primary) columns.

For example, Opportunity table is joined using opportunityid column with Opportunity Product table.

However, you can use Alternate Keys in your tables for this purpose:

https://docs.microsoft.com/en-us/dynamics365/customerengagement/on-premises/developer/define-alternate-keys-entity

To give you perspective, let me brief on relationship types and their behavior using your example:

Table A 1 to N (many) Table B: this means Table B will have a lookup of Table A (based on tableAid column).

Table A N to 1 Table B: this means Table A will have a lookup field of Table B (based on tableBid)

Table A N to N Table B: a resolving entity (e.g. new_table_a_table_b) is created automatically with the following columns:

new_table_a_table_b_id (primary key of resolving table)

tableAid

tableBid

Apart from an ID column, Dynamics 365 tables (entities) has a primary column (commonly named name). This is the value shown in the lookup field (for Display purposes, behind the scenes it is connected with the primary id).

You can also create Alternate Keys (as mentioned before) in you entities.

To summarize, you cannot create relationships based on your columns, however, if you still have a requirement you can add both custom columns as alternate keys in the tables and join them using SQL server query.

Example:

Table A

tableAid (primary key created by the system)

tableAno (your custom column)

Table B:

tableBid (primary key created by the system)

tableAid (lookup to table A using primary column)

tableAno (custom column to hold value from Table, without a foreign key)

Now it will be your responsibility to fill tableAno value when a record of table B is linked with Table B (either using a real-time workflow or plugin) or use relationship mapping.