locked
Retrieving data from sql server based on a dynamics crm field RRS feed

  • Question

  • I have the following function to write but not sure how to go ahead with it...

    Based on a user entering a postcode on a dynamics form I need to query an SQL server database, and retrieve two columns from the query result to populate two other fields on the dynamics form.

    What is the best practice for doing this \ how do I get started with this?

    My first thought was through javascript but googling that I came across sites saying it isn't best practice, due to security issues, to do it this way but they didn't really explain the correct way.

    So what \ how do I do this?

    regards,

    Matt

    Thursday, September 19, 2013 9:53 AM

All replies

  • Hi,

    I would do this as a plugin, executing on change of the postcode (pre operation is probably best as you wouldn't have to worry about saving the record a second time).

    Inside the plugin, you can access the db using standard .net code and populate the correct attributes on the target record.

    You can set up the connection details in the configuration for the step so that the code doesn't need recompiling for different environments or if the db changes

    Chris

    Thursday, September 19, 2013 10:09 AM
  • Chris,

    Ok, thanks for the reply.

    As said I am new to the development side of things so any pointers on where to get started?

    Other option is I have an entity within CRM I can add these fields to so could include it in there, but again don't know really where to start with this either.

    regards,

    Matt

    Thursday, September 19, 2013 10:22 AM
  • Hi Matt,

    Your best bet for plugins is to do a search for a tutorial, there are loads out there. You may need to do a separate search for configuration settings.

    I would give you instructions on how to go about creating a plugin, but unfortunately don't have the time at the moment.

    If the info is held in another record, you could then do it by JavaScript using oData to retrieve the records you need.

    if you need more assistance you can email me at chris.adams@almad-solutions.co.uk

    Chris

    Thursday, September 19, 2013 10:31 AM
  • So far I have got this far:

    function GetPostcodeArea()
    {
    var Area = ReturnAreas("S1");
    alert (Area);
    }
    
    function ReturnAreas(postcodeArea)
    //Function to check if a user has a specific role or not
    {
        var serverUrl = document.location.protocol + "//" + document.location.host + "/" + Xrm.Page.context.getOrgUniqueName();
        var oDataEndpointUrl = serverUrl + "/XRMServices/2011/OrganizationData.svc/";
        oDataEndpointUrl += "areacodeSet?$select=BD,PSC,txtPostcode&$filter=txtpostcode eq '" + postcodeArea + "'";
        var service = GetRequestObject();
    
        if (service != null)
        {
            service.open("GET", oDataEndpointUrl, false);
            service.setRequestHeader("X-Requested-Width", "XMLHttpRequest");
            service.setRequestHeader("Accept", "application/json, text/javascript, */*");
            service.send(null);

    How do I see / extract the fields BD and PSC must admit that I am a little lost with this.

    regards,

    Matt

    Friday, September 27, 2013 3:46 PM