locked
Update data in database? RRS feed

  • Question

  • Hi all,

    Can someone advise the best way to update fields on a related entity.  Heres my scenario:

    I have the primary contact on the account screen, what I would like is that when a user looks at the contact record there is an indicator on the contact record to say they are the primary contact for that account.

    My thinking is to do it something like this:

    On the account screen I put some code on the onchange event for the primary contact field like so:

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

    var contid = crmForm.all.primarycontactid.DataValue[0].id;
    contid1 = contid.substring(2,36);

    var connection = new ActiveXObject("ADODB.Connection");
    var connectionString = "Provider=SQLOLEDB;Server=xxxx;Database=xxxx_mscrm;Integrated Security=sspi";
    connection.Open(connectionString);

    var query = "UPDATE FilteredContact SET fieldoncontactscreen = 1 WHERE contactid= " + contid1 ;

    var rs = new ActiveXObject("ADODB.Recordset");
    rs.Open(query, connection, /*adOpenKeyset*/1, /*adLockPessimistic*/2);

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

    2 questions, is this the right way to do things as Im using SQL to update the data and if so hwo do I pass the ContactID as they way Im doing it now I get a string to uniqueindentifier conversion error.

    Thanks in advance for any help


    Tuesday, August 30, 2011 1:45 PM

Answers

  • Hi Bharat,

    Based on your logic, you wanted to put some flag on contact record when primary account is update on account screen correct?

    if yes then you need to put script on Primary contact on change event  on ACCOUNT FORM and it will update this flag on relevant contact record. following exmaple code for this.

    In the following example I added custom field called PrimayContactFlag on contact record , chnage this based on your environment/requirement.

    hope this helps..

    // Prepare variables for updating a contact.
    
    var contactId = crmForm.all.primarycontactid.DataValue[0].id; //this contact has become primary contact for this account 
    
    
    
    //Now update this contact PrimayContactFlag field (new field) to YES 
    
    //So all contact that are PrimayContactFlag field as YES are primary contacts
    
    
    
    // Prepare the SOAP message.
    
    var xml = "<?xml version='1.0' encoding='utf-8'?>"+ 
    
    "<soap:Envelope xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'"+
    
    " xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'"+
    
    " xmlns:xsd='http://www.w3.org/2001/XMLSchema'>"+ 
    
    authenticationHeader+
    
    "<soap:Body>"+ 
    
    "<Update xmlns='http://schemas.microsoft.com/crm/2007/WebServices'>"+ 
    
    "<entity xsi:type='contact'>"+ 
    
    "<PrimayContactFlag>"YES"</PrimayContactFlag>"+ 
    
    "<contact>"+contactId+"</contactid>"+ 
    
    "</entity>"+ 
    
    "</Update>"+ 
    
    "</soap:Body>"+ 
    
    "</soap:Envelope>";
    
    // Prepare the xmlHttpObject and send the request.
    
    var xHReq = new ActiveXObject("Msxml2.XMLHTTP");
    
    xHReq.Open("POST", "/mscrmservices/2007/CrmService.asmx", false);
    
    xHReq.setRequestHeader("SOAPAction","http://schemas.microsoft.com/crm/2007/WebServices/Update");
    
    xHReq.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
    
    xHReq.setRequestHeader("Content-Length", xml.length);
    
    xHReq.send(xml);
    
    // Capture the result
    
    var resultXml = xHReq.responseXML;
    
    
    
    // Check for errors.
    
    var errorCount = resultXml.selectNodes('//error').length;
    
    if (errorCount != 0)
    
    {
    
     var msg = resultXml.selectSingleNode('//description').nodeTypedValue;
    
     alert(msg);
    
    }
    
    // Display a confirmation message and open the updated contact.
    
    else
    
    {
    
    alert("Contact with id = "+contactId+" successfully updated.");
    
    window.open("/sfa/conts/edit.aspx?id={"+contactId+"}");
    
    }
    
    
    
    

     


    MayankP
    My Blog
    Follow Me on Twitter
    • Marked as answer by BharatP Tuesday, August 30, 2011 3:10 PM
    Tuesday, August 30, 2011 2:55 PM
    Answerer

All replies

  • Hi Bharat,

    Direct SQL update is not support way to do this, you can achieve this in CRM 4.0 by putting on change java script code as mentioned in below article..

    http://msdn.microsoft.com/en-us/library/cc677074.aspx

    based on above following pseudo code for this, Hope this helps..

    // Prepare variables for updating a contact.
    var contactId = crmForm.ObjectId; //Since this contact form this will be contact Id (need to make sure this exist)
    Var accountId = crmForm.all.parentcustomerid.DataValue[0].id; //this will be parent account id (need to make sure this exist)
    
    // Prepare the SOAP message.
    var xml = "<?xml version='1.0' encoding='utf-8'?>"+ 
    "<soap:Envelope xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'"+
    " xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'"+
    " xmlns:xsd='http://www.w3.org/2001/XMLSchema'>"+ 
    authenticationHeader+
    "<soap:Body>"+ 
    "<Update xmlns='http://schemas.microsoft.com/crm/2007/WebServices'>"+ 
    "<entity xsi:type='account'>"+ 
    "<primarycontactid>"+contactId+"</primarycontactid>"+ 
    "<accountid>"+accountId+"</contactid>"+ 
    "</entity>"+ 
    "</Update>"+ 
    "</soap:Body>"+ 
    "</soap:Envelope>";
    // Prepare the xmlHttpObject and send the request.
    var xHReq = new ActiveXObject("Msxml2.XMLHTTP");
    xHReq.Open("POST", "/mscrmservices/2007/CrmService.asmx", false);
    xHReq.setRequestHeader("SOAPAction","http://schemas.microsoft.com/crm/2007/WebServices/Update");
    xHReq.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
    xHReq.setRequestHeader("Content-Length", xml.length);
    xHReq.send(xml);
    // Capture the result
    var resultXml = xHReq.responseXML;
    
    // Check for errors.
    var errorCount = resultXml.selectNodes('//error').length;
    if (errorCount != 0)
    {
     var msg = resultXml.selectSingleNode('//description').nodeTypedValue;
     alert(msg);
    }
    // Display a confirmation message and open the updated contact.
    else
    {
    alert("Contact with id = "+contactId+" successfully updated.");
    window.open("/sfa/conts/edit.aspx?id={"+contactId+"}");
    }
    
    

     

     


    MayankP
    My Blog
    Follow Me on Twitter
    Tuesday, August 30, 2011 2:17 PM
    Answerer
  • above solution is for CRM 4.0 and if it is CRM 2011 then refer following article for the same..

    http://social.microsoft.com/Forums/en/crmdevelopment/thread/082b38b7-309f-4d72-baff-61ceaaedc5fb


    MayankP
    My Blog
    Follow Me on Twitter
    Tuesday, August 30, 2011 2:19 PM
    Answerer
  • Hi Mayan,

     

    Ive never used this technique before, very new to all this.

     

    I have tried they example you have given:

    http://msdn.microsoft.com/en-us/library/cc677074.aspx which works fine.

    The bit Im struggling with is linking the account entity to the contact entity.

    I am trying to put the code onchange on the account screen so the contact is updated immediately.

     

    Tuesday, August 30, 2011 2:40 PM
  • Hi Mayan,

    Me being thick, I managed to work it out.....Thanks for your help

    Tuesday, August 30, 2011 2:51 PM
  • Hi Bharat,

    Based on your logic, you wanted to put some flag on contact record when primary account is update on account screen correct?

    if yes then you need to put script on Primary contact on change event  on ACCOUNT FORM and it will update this flag on relevant contact record. following exmaple code for this.

    In the following example I added custom field called PrimayContactFlag on contact record , chnage this based on your environment/requirement.

    hope this helps..

    // Prepare variables for updating a contact.
    
    var contactId = crmForm.all.primarycontactid.DataValue[0].id; //this contact has become primary contact for this account 
    
    
    
    //Now update this contact PrimayContactFlag field (new field) to YES 
    
    //So all contact that are PrimayContactFlag field as YES are primary contacts
    
    
    
    // Prepare the SOAP message.
    
    var xml = "<?xml version='1.0' encoding='utf-8'?>"+ 
    
    "<soap:Envelope xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'"+
    
    " xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'"+
    
    " xmlns:xsd='http://www.w3.org/2001/XMLSchema'>"+ 
    
    authenticationHeader+
    
    "<soap:Body>"+ 
    
    "<Update xmlns='http://schemas.microsoft.com/crm/2007/WebServices'>"+ 
    
    "<entity xsi:type='contact'>"+ 
    
    "<PrimayContactFlag>"YES"</PrimayContactFlag>"+ 
    
    "<contact>"+contactId+"</contactid>"+ 
    
    "</entity>"+ 
    
    "</Update>"+ 
    
    "</soap:Body>"+ 
    
    "</soap:Envelope>";
    
    // Prepare the xmlHttpObject and send the request.
    
    var xHReq = new ActiveXObject("Msxml2.XMLHTTP");
    
    xHReq.Open("POST", "/mscrmservices/2007/CrmService.asmx", false);
    
    xHReq.setRequestHeader("SOAPAction","http://schemas.microsoft.com/crm/2007/WebServices/Update");
    
    xHReq.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
    
    xHReq.setRequestHeader("Content-Length", xml.length);
    
    xHReq.send(xml);
    
    // Capture the result
    
    var resultXml = xHReq.responseXML;
    
    
    
    // Check for errors.
    
    var errorCount = resultXml.selectNodes('//error').length;
    
    if (errorCount != 0)
    
    {
    
     var msg = resultXml.selectSingleNode('//description').nodeTypedValue;
    
     alert(msg);
    
    }
    
    // Display a confirmation message and open the updated contact.
    
    else
    
    {
    
    alert("Contact with id = "+contactId+" successfully updated.");
    
    window.open("/sfa/conts/edit.aspx?id={"+contactId+"}");
    
    }
    
    
    
    

     


    MayankP
    My Blog
    Follow Me on Twitter
    • Marked as answer by BharatP Tuesday, August 30, 2011 3:10 PM
    Tuesday, August 30, 2011 2:55 PM
    Answerer