Unanswered

Relationships on Existing Columns

(0) Share

Report

Posted on by sperry1625

763 Super User 2024 Season 1

I am importing data from Azure SQL. The data will be synched back and forth. The Azure SQL database already has relationships between the tables via certain fields. How do I recreate these relationships in Dataverse? The way it is natively done in Dataverse will not work.

Any assistance is appreciated.

Thank you.

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (6)

Answers (1)

Bipin D365 971 on at

Like (0)

Report

Hi,

You can create Custom attribute in both the entities to store the data as you are saying creating lookup field will not do work.

For your load/sync you will then rely on the custom attribute/column data to relate two table.

Please mark my answer verified if this is helpful!
Regards,
Bipin Kumar
Follow my Blog: https://xrmdynamicscrm.wordpress.com/

Was this reply helpful? Yes No
Ram Prakash Duraisamy 5,593 Super User 2025 Season 2 on at

Like (0)

Report

Hello @sperry1625,

There are three types of Relationships available in Dataverse

1. 1:N
2. N:1
3. N:N

For 1:N and N:1 we can easily create a lookup and Map it easily.

For eg

Consider there are 2 tables One called ACCOUNT and Another called Opportunity. I want to make a Relationship between these two for that,

Create a LOOKUP in Opportunity Table with Referring to Account Table, thats it 🙂

Now one Account Can have Many Opportunity. Hence way N:1 will work for the same.

For Creating Relationship

1. Navigate to https://make.powerapps.com
2. Click Dataverse
3. select Table where you want to perform this Operation
4. Click Relationship and Process it 🙂

For N:N --> We cannot have Lookup, there is an Option Called SUBGRID against that only we can use

Let me know if you need more clarity for the same.

Please mark as Answer if it is helpful and provide Kudos

Subscribe : https://www.youtube.com/channel/UCnGNN3hdlKBOr6PXotskNLA
Blog : https://microsoftcrmtechie.blogspot.com

Was this reply helpful? Yes No
Devikumari Krishna 988 Super User 2024 Season 1 on at

Like (0)

Report

Hi @sperry1625 ,

Can you brief one or two relationships which you feel is not possible in Data verse and you already have them in your Azure SQL tables ?
We can help you with better suggestions then.

If you like my response please give kudos.

My Blog: https://d365topicsbydk.com
My Youtube channel : https://www.youtube.com/channel/UCxSIryP2ah2VpEFr-Z72t1A

Regards
Devi

Kind Regards
Devi

Was this reply helpful? Yes No
Verified answer

sperry1625 763 Super User 2024 Season 1 on at

Like (1)

Report

There is a solution to this. This is how I made this work...

1. Create Lookup fields in your tables
When I created my tables I added a Lookup field to the foreign table. So, Table A has a reference to Table B. I created a lookup between the two tables.
2. During Import, store the PK value from foreign table in Lookup Field
When you are creating your dataflows store the PK from the foreign table into the Lookup field you created. When you do this, Dataverse will convert the entry into a Lookup to the foreign table. So, in my case I stored the PK from Table B into the Lookup field I created in Table A and Dataverse created the relationship.

Relationships created in this way do not show up under "Related" for a table, but I am still working on how to do that. It may or may not be possible, we shall see.

Thank you to all who tried to help. I hope this helps others.

Was this reply helpful? Yes No
AdamGill1965 311 on at

Like (1)

Report

I understand that this is an old topic but I have a question relating to the actual Columns which are being used in each Relationship: I have a 2 Tables (Table A and Table B) - in Table A, I have a Column called 'Email', in Table B, I have a Column called 'UserID'. I want to create a 1:N Relationship between the Tables, using 'Email' in Table A, against 'UserID' in Table B. From what I can determine, if you define a new Relationship between 2 Tables, there is no option to say which Columns should be used to define that Relationship - unless I am missing something?

Thanks!
Adam

Was this reply helpful? Yes No
Sjoerd 3 on at

Like (0)

Report

I have the same issue, very frustrating, I create a table from excel with load headers and one with load lines, LoadID is the primary key for the headers, Load Lines have a key composed of loadID and Item Number.

So I have both tables but I am not able to create a one-to-many relation between LoadHeader.LoadID and LoadLine.LoadID...

Was this reply helpful? Yes No