Sample JavaScript to retrieve using Web API in Dynamics CRM 2016 / 365

function executeWebApiQuery(query, type) {
    var result = null;
    var clientURL = Xrm.Page.context.getClientUrl();
    var req = new XMLHttpRequest()
    req.open(type, encodeURI(clientURL + query), false);
    req.setRequestHeader("Accept", "application/json");
    req.setRequestHeader("Content-Type", "application/json;charset=utf-8");
    req.setRequestHeader("OData-MaxVersion", "4.0");
    req.setRequestHeader("OData-Version", "4.0");
    req.onreadystatechange = function () {
        if (this.readyState == 4) {
            req.onreadystatechange = null;
            if (this.status == 200) {
                result = JSON.parse(this.response);

            }
            else {
                var error = JSON.parse(this.response).error;
                alert("Error : " + error.message);
            }
        }
    };
    req.send(null);
    return result;
}


var query = "/api/data/v8.1/businessunits(" + BUID + ")?$select=name";
var type = "GET";
var queryResult = executeWebApiQuery(query, type);
if (queryResult.Value.length > 0) {
    var BUName = queryResult.Value[0].name;
}

Reference : http://crmjavascripts.blogspot.com/2016/03/retrieve-record-in-crm-2016-using-web.html

MS CRM - retrieve more than 5000 records C# using Fetchxml

     string fetchtogetactivecustomer = @"<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>
  <entity name='contact'>
    <attribute name='fullname' />
    <attribute name='contactid' />
    <order attribute='fullname' descending='false' />
    <filter type='and'>
      <condition attribute='statecode' operator='eq' value='0' />
    </filter>
  </entity>
</fetch>";
 EntityCollection customersList = GetAllRecords(fetchtogetactivecustomer, service);

        public static EntityCollection GetAllRecords(string fetchxml ,  IOrganizationService service)
        {
            EntityCollection recordsList = new EntityCollection();
            // Define the fetch attributes.
            // Set the number of records per page to retrieve.
            int fetchCount = 5000;
            // Initialize the page number.
            int pageNumber = 1;

            // Specify the current paging cookie. For retrieving the first page,
            // pagingCookie should be null.
            string pagingCookie = null;

            while (true)
            {
                // Build fetchXml string with the placeholders.
                string xml = CreateXml(fetchxml, pagingCookie, pageNumber, fetchCount);

                // Excute the fetch query and get the xml result.
                RetrieveMultipleRequest fetchRequest1 = new RetrieveMultipleRequest
                {
                    Query = new FetchExpression(xml)
                };

                EntityCollection returnCollection = ((RetrieveMultipleResponse)service.Execute(fetchRequest1)).EntityCollection;

                foreach (var c in returnCollection.Entities)
                {
                    recordsList.Entities.Add(c);
                }



                // Check for morerecords, if it returns 1.
                if (returnCollection.MoreRecords)
                {

                    // Increment the page number to retrieve the next page.
                    pageNumber++;

                    // Set the paging cookie to the paging cookie returned from current results.                          
                    pagingCookie = returnCollection.PagingCookie;
                }
                else
                {
                    // If no more records in the result nodes, exit the loop.
                    break;
                }
            }
            return recordsList;
        }


        public static string CreateXml(string xml, string cookie, int page, int count)
        {
            StringReader stringReader = new StringReader(xml);
            XmlTextReader reader = new XmlTextReader(stringReader);

            // Load document
            XmlDocument doc = new XmlDocument();
            doc.Load(reader);

            return CreateXml(doc, cookie, page, count);
        }

        public static string CreateXml(XmlDocument doc, string cookie, int page, int count)
        {
            XmlAttributeCollection attrs = doc.DocumentElement.Attributes;

            if (cookie != null)
            {
                XmlAttribute pagingAttr = doc.CreateAttribute("paging-cookie");
                pagingAttr.Value = cookie;
                attrs.Append(pagingAttr);
            }

            XmlAttribute pageAttr = doc.CreateAttribute("page");
            pageAttr.Value = System.Convert.ToString(page);
            attrs.Append(pageAttr);

            XmlAttribute countAttr = doc.CreateAttribute("count");
            countAttr.Value = System.Convert.ToString(count);
            attrs.Append(countAttr);

            StringBuilder sb = new StringBuilder(1024);
            StringWriter stringWriter = new StringWriter(sb);

            XmlTextWriter writer = new XmlTextWriter(stringWriter);
            doc.WriteTo(writer);
            writer.Close();

            return sb.ToString();
        }

MS CRM 2011 - Enable button based on users security role - Custom rule using javascript

// JavaScript source code
function VisibleButtonBasedOnUserRole() {
    try
    {
        var CRMRole = UserHasRole("System Administrator");
        if (CRMRole) {
            return true;
        } else {
            return false;
        }
    }
    catch (ex) {
        alert(ex.message);
    }

}

function UserHasRole(roleName) {
    var serverUrl = Xrm.Page.context.getServerUrl();
    var BUId = getAuthorizedBU();
    var oDataEndpointUrl = serverUrl + "/XRMServices/2011/OrganizationData.svc/";
 
    oDataEndpointUrl += "RoleSet?$top=1&$filter=Name eq '" + roleName + "' and BusinessUnitId/Id eq(guid'" + BUId + "')";
    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);

        var requestResults = eval('(' + service.responseText + ')').d;


        if (requestResults != null && requestResults.results.length == 1) {
            var role = requestResults.results[0];

            var id = role.RoleId;

            var currentUserRoles = Xrm.Page.context.getUserRoles();

            for (var i = 0; i < currentUserRoles.length; i++) {
                var userRole = currentUserRoles[i];
         
                if (GuidsAreEqual(userRole, id)) {
                    return true;
                }
            }
        }
    }

    return false;
}

function getAuthorizedBU() {

    var BUId = null;
    var serverUrl = Xrm.Page.context.getServerUrl();
    var UserID = Xrm.Page.context.getUserId();

    var oDataEndpointUrl = serverUrl + "/XRMServices/2011/OrganizationData.svc/";
    oDataEndpointUrl += "/SystemUserSet?$select=BusinessUnitId&$filter=SystemUserId eq (guid'" + UserID + "')";


    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);

        var requestResults = eval('(' + service.responseText + ')').d;

        if (requestResults != null && requestResults.results.length == 1) {
            var businessUnit = requestResults.results[0];
            BUId = businessUnit.BusinessUnitId.Id;
        }
    }

    return BUId;
}

function GetRequestObject() {
    if (window.XMLHttpRequest) {
        return new window.XMLHttpRequest;
    }
    else {
        try {
            return new ActiveXObject("MSXML2.XMLHTTP.3.0");
        }
        catch (ex) {
            return null;
        }
    }
}


function GuidsAreEqual(guid1, guid2) {
    var isEqual = false;

    if (guid1 == null || guid2 == null) {
        isEqual = false;
    }
    else {
        isEqual = guid1.replace(/[{}]/g, "").toLowerCase() == guid2.replace(/[{}]/g, "").toLowerCase();
    }

    return isEqual;
}

Accessing WCF Service at WCF Client using Custom Binding created programmatically

           
               EndpointAddress remoteAddress = new EndpointAddress(WebServiceURL);

                TestServiceClient serviceClient = new TestServiceClient(GetBinding(), remoteAddress);

                serviceClient.ClientCredentials.UserName.UserName = userNameValue;
                serviceClient.ClientCredentials.UserName.Password = passwordValue;
                serviceClient.Open();

                UpdatePaymentStatusRequest newRequest = new UpdatePaymentStatusRequest();
                newRequest.Status="Paid";
                UpdatePaymentStatusResponse newResponse=newRequest.UpdateStatus(newRequest);

                serviceClient.Close();




 public static CustomBinding GetBinding()
        {
            CustomBinding binding = new CustomBinding();
            binding.Name = "Name_of_your_binding";
            binding.CloseTimeout = TimeSpan.Parse("00:05:00");
            binding.OpenTimeout = TimeSpan.Parse("00:05:00");
            binding.ReceiveTimeout = TimeSpan.Parse("00:10:00");
            binding.SendTimeout = TimeSpan.Parse("00:10:00");
            binding.Elements.Add(new TextMessageEncodingBindingElement(MessageVersion.Soap12, System.Text.Encoding.UTF8));
            //   HttpsTransportBindingElement hbe = new HttpsTransportBindingElement();
            HttpTransportBindingElement hbe = new HttpTransportBindingElement();
            //  hbe.RequireClientCertificate = true;
            hbe.AllowCookies = false;
            hbe.AuthenticationScheme = System.Net.AuthenticationSchemes.Basic;
            hbe.BypassProxyOnLocal = false;
            hbe.HostNameComparisonMode = HostNameComparisonMode.StrongWildcard;
            hbe.KeepAliveEnabled = true;
            hbe.ManualAddressing = false;
            hbe.MaxBufferPoolSize = Convert.ToInt64(int.MaxValue);
            hbe.MaxBufferSize = int.MaxValue;
            hbe.MaxReceivedMessageSize = Convert.ToInt64(int.MaxValue);
            hbe.ProxyAuthenticationScheme = System.Net.AuthenticationSchemes.Anonymous;
            hbe.Realm = "";
            hbe.TransferMode = TransferMode.Buffered;
            hbe.UnsafeConnectionNtlmAuthentication = false;
            hbe.UseDefaultWebProxy = true;
            binding.Elements.Add(hbe);
            return binding;
        }

Reference :

http://technologybooth.blogspot.sg/2013/08/initializing-wcf-client-programmatically.html

MS CRM 2011 - Send Email with attachment ( C# )

      private static void SendEmail(string fromUserEmailId, List<string> toUsersEmails, string attachmentfileLocation, IOrganizationService orgService)
      {       
          EntityReference fromUser=GetUserbyEmailId(fromUserEmailId,orgService);
           List<Entity>  emailFromList =new List<Entity>();
          if(fromUser!=null)
          {
                Entity emailFrom=new Entity("activityparty");
                emailFrom["partyid"] = fromUser;
                emailFromList.Add(emailFrom) ;
          }
         List<Entity> emailToList=new List<Entity>();        
          foreach (string toUser in toUsersEmails)
          {
            
              EntityReference toUserID = GetUserbyEmailId(toUser, orgService);
              if (toUserID != null)
              {
                  Entity emailTo = new Entity("activityparty");
                  emailTo["partyid"] = toUserID;
                  emailToList.Add (emailTo);
              }
          }
            Entity newEmail = new Entity("email");
            newEmail["from"] = emailFromList.ToArray<Entity>();
            newEmail["to"] = emailToList.ToArray<Entity>();
            newEmail["subject"] = "test subject";
            newEmail["description"] = " test description";
            newEmail["directioncode"] = true;
            Guid newEmailId= orgService.Create(newEmail);
            AddAttachmentToEmail(new EntityReference(newEmail.LogicalName, newEmailId), attachmentfileLocation, orgService);
       
              // Use the SendEmail message to send an e-mail message.
              SendEmailRequest sendEmailreq = new SendEmailRequest
              {
               EmailId = newEmailId,
               TrackingToken = "",
               IssueSend = true
              };
              SendEmailResponse sendEmailresp = (SendEmailResponse)orgService.Execute(sendEmailreq);
      }
    
    
      private static void AddAttachmentToEmail(EntityReference email, string fileLocation, IOrganizationService service)
      {
          // Open a file and read the contents into a byte array
          FileStream stream = System.IO.File.OpenRead(fileLocation);
          byte[] byteData = new byte[stream.Length];
          stream.Read(byteData, 0, byteData.Length);
          stream.Close();
        
          // Encode the data using base64.
          string encodedData = System.Convert.ToBase64String(byteData);
          // Add attachment
          Entity newAttachment = new Entity("activitymimeattachment");
          newAttachment["body"] = encodedData;
          newAttachment["filename"] = Path.GetFileName(fileLocation);
          newAttachment["objectid"] = email;
          newAttachment["objecttypecode"] = "email";
          // Create the attachment
          service.Create(newAttachment);
      }
      private static EntityReference GetUserbyEmailId(string emailID, IOrganizationService orgService)
      {
          string fetchQuerytoGetSystemUser = @"<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>
                                              <entity name='systemuser'>
                                                <attribute name='fullname' />
                                                <attribute name='businessunitid' />
                                                <attribute name='title' />
                                                <attribute name='address1_telephone1' />
                                                <attribute name='systemuserid' />
                                                <order attribute='fullname' descending='false' />
                                                <filter type='and'>
                                                  <condition attribute='internalemailaddress' operator='eq' value='" + emailID + "' />"
                                                + @"</filter>
                                              </entity>
                                            </fetch>";
          EntityCollection usersList = orgService.RetrieveMultiple(new FetchExpression(fetchQuerytoGetSystemUser));
          if (usersList != null && usersList.Entities != null && usersList.Entities.Count > 0)
          {
              return new EntityReference(usersList[0].LogicalName, usersList[0].Id);
          }
          return null;
      }
 

SSIS Package Configuration - Connection String using Environment variable

http://vsstack.blogspot.com/p/blog-page_6.html

https://www.youtube.com/watch?v=Ap5Mc6lS_PI


https://anothersqlgeek.wordpress.com/2013/03/28/ssis-dynamic-connections-part-1/
 

MS Excel - Using Index Match instead of Vlookup

=INDEX ( Column I want a return value from , MATCH ( My Lookup Value , Column I want to Lookup against , Enter “0” ))

 Example,

=IFERROR(INDEX(Sheet5!I:I,MATCH('duplicated clean'!A3,Sheet5!A:A,0)),"")

Reference:

http://www.randomwok.com/excel/how-to-use-index-match/

http://fiveminutelessons.com/learn-microsoft-excel/how-use-index-match-instead-vlookup