web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / Fill lookup field with...
Microsoft Dynamics CRM (Archived)

Fill lookup field with data using PowerShell

(0) ShareShare
ReportReport
Posted on by Community Member

Hi,

I receive a .csv file daily and use PowerShell to update/insert the information into CRM.

I am changing the field new_endcustomer from string to lookup because I want to see only related information in another form (account form) but it needs some kind of link to do that of course. But now Microsoft Dynamics gives me the error when I run my script:

"Exception calling "Update" with "1" argument(s): "System.InvalidCastException: Microsoft Dynamics CRM has experienced an error."

I can't post the entire script here, it's too long, but the 2 lines that I feel cause the issue are these:

$entity.Attributes["new_endcustomer"] = $_."End Customer"
$service.Update($entity)

The value that's in the end customer field in the csv matches the name of the Account in my CRM (which the lookup field is linked to) but I am still getting this error. I can find absolutly nothing useful online so I am hoping that I can receive some guidance as on how to solve this issue here.

How can I fill up a lookup field using PowerShell into Microsoft CRM 2016?

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Rahul-Waghmare Profile Picture
    Rahul-Waghmare 990 on at

    Hello Jeroen,

    Please try this,

    $lookupObject = New-Object -TypeName Microsoft.Xrm.Sdk.EntityReference;

    $lookupObject.LogicalName = "account";

    $lookupObject.Id = "[myGUID]";

    $targetRecord["mylookupfield"] = [Microsoft.Xrm.Sdk.EntityReference] $lookupObject;

    $service.Update($recordToUpdate);

    Please mark my answer as suggested, if you able resolve the issue.

    Regards,

    Rahul

  • Community Member Profile Picture
    Community Member on at

    I have filled up the the variables likes this: could you please verify if I did it right? Because I still get errors, I am unsure what MyGUID is too.

    $lookupObject = New-Object -TypeName Microsoft.Xrm.Sdk.EntityReference;

    $lookupObject.LogicalName = "account"; <-----you filled this in, it's the entity I have my lookup field connected to

    $lookupObject.Id = "[xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx]"; <------ Here I put my UserId I got as an answer from a WhoAmIRequest. Perhaps you mean the ID from the lookup field? I don't know how to retrieve it, it sais it has to be 32 digits with 4 dashes (-), which happened to match a UserID so I tried that.

    $targetRecord["new_endcustomer"] = [Microsoft.Xrm.Sdk.EntityReference] $lookupObject; <-------- new_endcustomer is the name of the lookup field.

    $service.Update($_."End Customer"); <---- $_."End Customer" is the field in the "End Customer" column in the .csv that i'm currently scanning.

    These are the errors I still get:

    - I find this one odd because I can verify myself that "TestAccount" exists.

    Exception calling "Update" with "1" argument(s): "The entity with a name = 'TestAccount' was not found in the MetadataCache."

    - This error I already explained above

    Exception setting "Id": "Cannot convert value "[xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx]" to type "System.Guid". Error: "Guid should contain 32 digits with 4 dashes

    So my main questions are what is GUID? And are my parameters set correctly?

    Thank you in advance!

  • Suggested answer
    Rahul-Waghmare Profile Picture
    Rahul-Waghmare 990 on at

    Hi,

    That ID should be your lookup field value which you want to update for the record.

    Suppose you want to update the contact lookup in Account form in that case you will pass ContactID as "myGUID", it should not be your UserID.

    and you can try below logic to parse the guid.

    $lookupObject.Id = [System.Guid]::"[Lookup-GUID]"

    Regards,

    Rahul

  • Community Member Profile Picture
    Community Member on at

    Hi Rahul,

    But there is no value in the lookup field yet since I get errors inserting the information in that lookup field 'new_endcustomer'.

    But I feel like I'm getting closer to a solution here. This is what I have now:

    $entity = New-Object Microsoft.Xrm.Sdk.Entity("new_shipmentreport")
    $entity.Attributes[$id] = New-Object Microsoft.Xrm.Sdk.EntityReference("Account", $_."End Customer");
    $entity.Id = $id
    write-host $entity.Id (is this: 1f808956-538c-e611-80ca-005056ba67fe)
    $service.Update($entity)

    But I'm still with this converting error: New-Object : Cannot convert argument "1", with value: "Magellan Netzwerke", for "EntityReference" to type "System.Guid":
    "Cannot convert value "Magellan Netzwerke" to type "System.Guid". Error: "Guid should contain 32 digits with 4 dashes
    (xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx).""

    What do you think? So "Magellan Netzwerke" is the value I want in the record of the lookup field. Magellan Netzwerke is the value of $_."End Customer".

    (got it from here: http://stackoverflow.com/questions/22981106/how-to-add-a-value-to-a-lookup-field)

    In the link is explained that the last parameter from EntityReference should be the value you want to insert. But to me it makes no sense that he requires a guid then.

    And thank you for your patience! I know I have little experience with Microsoft Dynamics and PowerShell but I try my best.

    Kind regards

  • Suggested answer
    Rahul-Waghmare Profile Picture
    Rahul-Waghmare 990 on at

    Sorry for the late reply.

    Your error itself is having answer for your question. You are passing the text of the lookup value whereas you need to pass GUID of it. From where you are getting that name "Magellan Netzwerke", try to get the ID of it inplace of name.

    Regards,

    Rahul

  • Community Member Profile Picture
    Community Member on at

    I know this is old, but worth a shot...

    What is the service variable set to?  I have the same issue, but I don't have anything setup for service...  I just connect via connect-crmonline

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics CRM (Archived)

#1
SA-08121319-0 Profile Picture

SA-08121319-0 4

#1
Calum MacFarlane Profile Picture

Calum MacFarlane 4

#3
Alex Fun Wei Jie Profile Picture

Alex Fun Wei Jie 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans