Need to import data in a 1:N relationship

(0) Share

Report

Posted on by Community Member

Is it possible to import data in an a 1 to many relationship. I have an entity X which is having relationship with opportunity. An opportunity an have X objects associated with it. The data of X and opportunity is already available in excel file and I can import data in both X and Opportunity. But don't know how we can import data such that when I am finished with import it must show X already associated with Opportunity.

Thanks in advance.

*This post is locked for comments

I have the same question (0)

All responses (17)

Answers (1)

Community Member on at

Like (0)

Report
Copy link

Link copied!

Hi

Is it possible to import data in an a 1 to many relationship. I have an entity X which is having relationship with opportunity. An opportunity an have X objects associated with it. The data of X and opportunity is already available in excel file and I can import data in both X and Opportunity. But don't know how we can import data such that when I am finished with import it must show X already associated with Opportunity.

Thanks in advance.

Was this reply helpful? Yes No
a33ik 84,331 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Please avoid creating duplicated posts.

Was this reply helpful? Yes No
Thomas David Dayman 11,323 on at

Like (0)

Report
Copy link

Link copied!

To import a 1:N from excel, all you need to do is add the opportunity guid value to your custom entity record.

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report
Copy link

Link copied!

My guess at the anatomy of this:

I think you would have to reformat the Excel you have. Save it as a CSV file for formatting and then change it back when you're done.

Your x entities would be referenced as lookups by opportunities so for bulk uploading (assuming that's what you're doing), I guess you would need to get the ID's of the indidual objects e.g. {00000000-0000-0000-00AA-000000666800}

Not sure if it will work but if you wanted to try it you could create a JSON request in the web browser to get the names and ID's of each of the entries for each object.

Eg.

https://<yourOrg>.api.crm<x>.dynamics.com/api/data/v8.2/new_<yourObj>?$select=new_<name>,new_<yourObj>id

Then in firefox for example click on Raw data in the browser. Dump it into a text file. You would then need to run a script on your CSV to

replace each 'name' of Object in x with the '{ID}'.

Your opportunity records for bulk uploading would be like this:

Name: Joe Bloggs' opportunity - date

Object x: {00000000-0000-0000-00AA-000000666800}

Object y: ...

Object z: ...

Can anyone else chime in?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

I can already associate X with opportunity as from X its 1:1 association and is simple. The problem is importing associations from Opportunity to X as its a 1:N relation.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Andrii Butenko yes i created a duplicate because no one was replying and I really need to fix this problem. Thanks

Was this reply helpful? Yes No
Thomas David Dayman 11,323 on at

Like (0)

Report
Copy link

Link copied!

If you already have both the opportunityid and the Xid then all you need to do is create X records and just fill the opportunity lookup field with the opportunityid you have. that will give you the 1:N association from Opportunity to X

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Thanks Thomas,

Yes that creates only one way relationship from X to Opportunity (that is X knows its Opportunity) but it does not show what Xs this opportunity have (1:N relation).

Fro example:

Entity X

--------------------

Name Opportunity

A o1

Opportunity o1

--------------------

Xs: Still empty (List box).

Thanks for your help in advance

Was this reply helpful? Yes No
Thomas David Dayman 11,323 on at

Like (0)

Report
Copy link

Link copied!

Unless i'm missing something. CRM doesn't have 1:1 relationship oob, you would have to do some configuration to make it 1:1.

But lets say for example you added 2 X records and associate them to the same Opportunity. Doesn't that mean that that Opportunity has 2 X records associated to it?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

By 1:1 relationship I mean simple lookup. So X is also looked up with Opportunity (two way relationship).

Yes I can generate two X records and I can associate both of them to opportunity and they are visible over there. But instead of manually associating them I want to import this association information from a XLS (because the opportunity and X records are in thousands and manually associating them will take a lot of time).

Was this reply helpful? Yes No