web

You’re offline. This is a read only version of the page.

News and Announcements icon

Dynamics 365 CommunityForums Microsoft Dynamics CRM (Archived) Using EntityReference ...

Microsoft Dynamics CRM (Archived)

Using EntityReference to get the guid of records for lookup fields

(0) Share

Report

Report

Posted on by Community Member

I am using SSIS for data migration (create and update) into CRM 2013 database from another database.

I am facing a challenge with writing code to get the guid records in lookup fields. For example, I have two lookup fields on the form of a custom entity (gems_accountauditor). One lookup field looks into account entity and the other looks into a custom entity (gems_auditor).

I wrote the code below using entityreference for the account lookup field. Records are created when I run the SSIS package but only the first record from the source database has its account lookup field containing data. All the other records (a little over 100) have their account lookup field empty even though all those accounts exist in crm.

What am I missing in the code below? Is there a better way of having all the records getting their lookup fields to contain data?

if (!Row.listedCompany_IsNull)

{

String cNumber = Row.listedCompany.ToString();

QueryExpression qe = new QueryExpression();

ColumnSet cs = new ColumnSet();

FilterExpression fe = new FilterExpression();

ConditionExpression ce = new ConditionExpression();

cs.AddColumns("accountid");

ce.AttributeName = "gems_listedcomp";

ce.Operator = ConditionOperator.Equal;

ce.Values.Add(cNumber);

fe.AddCondition(ce);

qe.ColumnSet = cs;

qe.Criteria = fe;

qe.EntityName = "account";

EntityCollection result = organizationservice.RetrieveMultiple(qe);

foreach (Entity act in result.Entities)

{

string value = act.Attributes["accountid"].ToString();

Guid Value = new Guid(value);

EntityReference Account = new EntityReference("account", Value);

newAccountAuditor["gems_account"] = Account;

}

}

The second lookup field has it's own problem. I tweaked the code above to suit it's requirement but when I run the SSIS package, this particular field throws a runtime error ("An Unexpected error occured"). What could be the problem?

if (!Row.listedCompany_IsNull)

{

String cNumber = Row.listedCompany.ToString();

QueryExpression qe = new QueryExpression();

ColumnSet cs = new ColumnSet();

FilterExpression fe = new FilterExpression();

ConditionExpression ce = new ConditionExpression();

cs.AddColumns("accountid");

ce.AttributeName = "gems_listedcomp";

ce.Operator = ConditionOperator.Equal;

ce.Values.Add(cNumber);

fe.AddCondition(ce);

qe.ColumnSet = cs;

qe.Criteria = fe;

qe.EntityName = "account";

EntityCollection result = organizationservice.RetrieveMultiple(qe);

foreach (Entity act in result.Entities)

{

string value = act.Attributes["accountid"].ToString();

Guid Value = new Guid(value);

EntityReference Account = new EntityReference("account", Value);

newAccountAuditor["gems_account"] = Account;

}

}

*This post is locked for comments

I have the same question (0)

All responses Img

All responses (5)

Answers Img

Answers (1)

ashlega 34,477 on at

Like (0)

Report

Hi,

could you confirm if act.Attributes["accountid"].ToString(); returns an actual GUID?

Technically, you could replace those 4 lines in the for loops with just one:

newAccountAuditor["gems_account"] = act.ToEntityReference();

Although, that will only help if it's not a GUID there (I'm thinking it might be entity reference.. but it's the primary key field, so it might be a Guid)

Was this reply helpful? Yes No
Verified answer

Nithya Gopinath 17,078 on at

Like (0)

Report
Hi Edwin,

Try using the code below.

foreach (Entity act in result.Entities) { Guid Value = (Guid)act.Attributes["accountid"]; EntityReference Account = new EntityReference("account", Value); newAccountAuditor["gems_account"] = Account; }

Hope this helps.

Was this reply helpful? Yes No
Community Member on at

Like (1)

Report
Maybe the problem within your query expression.

Try this QueryExpressio Format:

QueryExpression query2 = new QueryExpression("new_budget");
string[] cols2 = { "new_budgetid", "owningbusinessunit" };
query2.Criteria = new FilterExpression();
query2.Criteria.AddCondition("owningbusinessunit", ConditionOperator.Equal, userid);
query2.ColumnSet = new ColumnSet(cols2);

Guid budgetid = Guid.Empty;

var budgetlkpid = _service.RetrieveMultiple(query2);
budgetid = (Guid)budgetlkpid[0].Attributes["new_budgetid"];
budgettrx["new_budgetid"] = new EntityReference("new_budget", budgetid);

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Alex,

I can confirm that line of code returns a GUID because when I execute the package, the first record created has it's lookup field filled with the correct data. The only problem is even though the other records are created, their lookup field doesn't contain any data.

Was this reply helpful? Yes No
ashlega 34,477 on at

Like (0)

Report

Hi Edwin,

are you sure that "if" evaluates as "true"?

if (!Row.listedCompany_IsNull)

You might try to set cNumber to something pre-defined if the condition is false and see what happens..

Also, how/where do you create (in-memory and in CRM) that newAccountAuditor object?

Was this reply helpful? Yes No

Helpful resources

Quick Links

Leaderboard > 🔒一 Microsoft Dynamics CRM (Archived)

#1

SA-08121319-0 4

#1

Calum MacFarlane 4

#3

Alex Fun Wei Jie 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans