Microsoft Dynamics CRM

Wednesday, July 10, 2013

SQL Server Date Function

----Today
SELECT GETDATE() 'Today'
----Yesterday
SELECT DATEADD(d,-1,GETDATE()) 'Yesterday'
----First Day of Current Week
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)'First Day of Current Week'
----Last Day of Current Week
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6) 'Last Day of Current Week'
----First Day of Last Week
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0) 'First Day of Last Week'
----Last Day of Last Week
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),6) 'Last Day of Last Week'
----First Day of Current Month
SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)'First Day of Current Month'
----Last Day of Current Month
SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)))'Last Day of Current Month'
----First Day of Last Month
SELECT DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))'First Day of Last Month'
----Last Day of Last Month
SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)))'Last Day of Last Month'
----First Day of Current Year
SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)'First Day of Current Year'
----Last Day of Current Year
SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0)))'Last Day of Current Year'
----First Day of Last Year
SELECT DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))'First Day of Last Year'
----Last Day of Last Year
SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)))'Last Day of Last Year'

Tuesday, May 7, 2013

Close Opportunity using Jscript

function UpdateStatus(state, status, entityname) {

    var request = "<s:Envelope xmlns:s=\"http://schemas.xmlsoap.org/soap/envelope/\">";
    request += "<s:Body>";
    request += "<Execute xmlns=\"http://schemas.microsoft.com/xrm/2011/Contracts/Services\" xmlns:i=\"http://www.w3.org/2001/XMLSchema-instance\">";
    request += "<request i:type=\"b:SetStateRequest\" xmlns:a=\"http://schemas.microsoft.com/xrm/2011/Contracts\" xmlns:b=\"http://schemas.microsoft.com/crm/2011/Contracts\">";
    request += "<a:Parameters xmlns:c=\"http://schemas.datacontract.org/2004/07/System.Collections.Generic\">";
    request += "<a:KeyValuePairOfstringanyType>";
    request += "<c:key>EntityMoniker</c:key>";
    request += "<c:value i:type=\"a:EntityReference\">";
    request += "<a:Id>" + Xrm.Page.data.entity.getId() + "</a:Id>";
    request += "<a:LogicalName>" + entityname + "</a:LogicalName>";
    request += "<a:Name i:nil=\"true\" />";
    request += "</c:value>";
    request += "</a:KeyValuePairOfstringanyType>";
    request += "<a:KeyValuePairOfstringanyType>";
    request += "<c:key>State</c:key>";
    request += "<c:value i:type=\"a:OptionSetValue\">";
    request += "<a:Value>" + state + "</a:Value>";
    request += "</c:value>";
    request += "</a:KeyValuePairOfstringanyType>";
    request += "<a:KeyValuePairOfstringanyType>";
    request += "<c:key>Status</c:key>";
    request += "<c:value i:type=\"a:OptionSetValue\">";
    request += "<a:Value>" + status + "</a:Value>";
    request += "</c:value>";
    request += "</a:KeyValuePairOfstringanyType>";
    request += "</a:Parameters>";
    request += "<a:RequestId i:nil=\"true\" />";
    request += "<a:RequestName>SetState</a:RequestName>";
    request += "</request>";
    request += "</Execute>";
    request += "</s:Body>";
    request += "</s:Envelope>";

    //send set state request
    $.ajax({
        type: "POST",
        contentType: "text/xml; charset=utf-8",
        datatype: "xml",
        url: Xrm.Page.context.getServerUrl() + "/XRMServices/2011/Organization.svc/web",
        data: request,
        beforeSend: function (XMLHttpRequest) {
            XMLHttpRequest.setRequestHeader("Accept", "application/xml, text/xml, */*");
            XMLHttpRequest.setRequestHeader("SOAPAction", "http://schemas.microsoft.com/xrm/2011/Contracts/Services/IOrganizationService/Execute");
        },
        success: function (data, textStatus, XmlHttpRequest) {
       window.location.reload(true);
        },
        error: function (XMLHttpRequest, textStatus, errorThrown) {
            alert(errorThrown);
        }
    });

}

XRM 2011 - Microsoft Dynamics CRM 2011 Style Buttons

Adding a button to a Form is a great way to add additional functionality right where the user is already focused. Today we are looking at a few different approaches to adding a button. First, we’re looking at handling the upgrade for those of you who have already used the 4.0 button inside of 2011 or if you had a 4.0 environment and are upgrading. If you have a clean 2011 environment, then feel free to skip down to the “From Scratch” section.

There is already some code floating around to create a 4.0 style button inside of CRM 2011. The button we’re creating will instead create a CRM 2011 style button (i.e. “Example Button”).

The CRM 2011 button starts gray and then when the user hovers over it, the button will look like:

Creating our Web Resource

First,name your Web Resource whatever you’d like. In my case, I’m planning to re-use it in various ways, so I’m throwing it in our JS root directory.

// CRM 2011 Style Button 
// Creates a button from a form field 
// Paul Way - 1/3/2012
function ConvertToButton(fldName, btnLabel, btnWidth, evt){ 
   var btn = '<button id="btn_' + fldName + '" ' + 
                    ' style="width:' + btnWidth + '" ' + 
                    ' class="ms-crm-Button" ' + 
                    ' onmouseover="Mscrm.ButtonUtils.hoverOn(this);" ' + 
                    ' onmouseout="Mscrm.ButtonUtils.hoverOff(this);" ' + 
                 '>' + btnLabel + '</button>'; 
 
   var ctrl = Xrm.Page.ui.controls.get(fldName)._control;
 
   // Add the new button 
   ctrl.get_element().innerHTML += btn;
 
   // Hide the textbox 
   ctrl.get_element().firstChild.style.display = 'none';
 
   // Hide the label (optional) 
   Xrm.Page.ui.controls.get('pager').setLabel('');
 
   // Add Event to the newly created button 
   ctrl.get_element().childNodes[1].attachEvent('onclick', evt);
 
}

Modifying our Form

To use this new function, you’ll first need to add the newly created web resource to the form. You’ll also want to have a separate JS web resource to call the ConvertToButton function. In my case, I already have a web resource for contact specific JavaScript. If I didn’t, I would need to create a new web resource and then place my contact specific code there. Here’s an example screenshot:

By having two web resources for this, you’ll have only one version of your ConvertToButton function throughout your CRM 2011 environment. Let’s say down the road you’ll need to update the function to change the label to “Button”, then you only need to modify one file. In CRM 4.0, you probably had a lot of duplicate code. With CRM 2011, your browser can cache the JavaScript for better performance and it is easier to maintain when organized appropriately.

In our other web resource, the one we setup with the OnLoad, we need the following code.

function contactsOnLoad(){ 
   convertToButton('pager', 'Example Button', '150px', function(){alert("test")}); 
}

Why is this so different then the 4.0 code?

If you’ve used the CRM 4.0 code, you’ll notice the code here is a lot shorter. It’s actually pretty different as well.

For one, instead of modifying the input element we are actually creating a button HTML element. This just means that we can now use about any kind of field instead of just pure textboxes. Not a huge deal, but opens up the email address 3 and other attributes.
Both are really unsupported but the other uses the deprecated crmForm.all.
Finally, the button style was meant for 4.0 whereas now we have the 2011 look-n-feel.

From Scratch

So far we’ve mainly focused on if you already were using the 4.0 code and were upgrading. But what if you don’t have the existing code structure? I’d argue against creating a new attribute just to have a button. When adding a new attribute, you are also adding the attribute to the underlying SQL tables and views. Using an existing field isn’t really a great option either because there is always the chance you will need the field or it will overlap with an application from the MS marketplace.

Instead, I’d recommend creating a button place holder web resource and then embedding the web resource on the page. The web resource is just a JPG image like this:

We then place the image wherever we want on the form. Make sure to set the formatting to one column and one row.

Finally, we need a little bit of code added to the /js/formButton.js web resource:

// CRM 2011 Style Button 
// Creates a button from a form field 
// Paul Way - 1/3/2012
 
function convertWebResourceToButton(fldName, btnLabel, btnWidth, leftMargin, evt){ 
   var btn = '<button id="btn_' + fldName + '" ' + 
                       ' style="margin-left:' + leftMargin + ';width:' + btnWidth + ';" ' + 
                       ' class="ms-crm-Button" ' + 
                       ' onmouseover="Mscrm.ButtonUtils.hoverOn(this);" ' + 
                       ' onmouseout="Mscrm.ButtonUtils.hoverOff(this);" ' + 
                     '>' + btnLabel + '</button>'; 
   
   var ctrl = Xrm.Page.ui.controls.get(fldName)._control.get_element().childNodes[1];
 
   // Replace image with buttom 
   ctrl.innerHTML = btn; 
   
   // Add Event to the newly created button 
   ctrl.firstChild.attachEvent('onclick', evt);
 
}

And as for our OnLoad function, we’ll need to use this instead:

function contactsOnLoad(){ 
   convertWebResourceToButton('WebResource_btnProfInfo', 'Another Approach', '150px', '119px', function(){alert("test2")}); 
}

Thanks to: Paul Way

Thursday, March 21, 2013

Reusable Jscript Library of Common Functions for CRM 2011

I find myself needing the same jscript over and over again when I build out demos. To make life easier I decided to create a function library that I can attach to any CRM form. Here it is. I will add to this over time.

If you have any useful functions that should be included post them in the comments and I’ll incorporate them. The jscript is available here and below. At the end of this post you will see some examples demonstrating the use of these functions.

Warning: these functions are of a ‘demo’ standard, and should be hardened and tested before used in a production setting.

p.s. my older jscript reference post has been updated recently as well, check it out.




// Determine Form Type
// example: GetFormType();
function GetFormType() {
    var FormType = Xrm.Page.ui.getFormType();
    if (FormType != null) {
        switch (FormType) {
            case 1:
                return "create";
                break;
            case 2:
                return "update";
                break;
            case 3:
                return "readonly";
                break;
            case 4:
                return "disabled";
                break;
            case 6:
                return "bulkedit";
                break;
            default:
                return null;
        }
    }
}

// Show/Hide a TAB
// example: HideShowTab("General", false);   // "false" = invisible
function HideShowTab(tabName, visible) {
    try {
        Xrm.Page.ui.tabs.get(tabName).setVisible(visible);
    }
    catch (err) { }
}

// Show/Hide a SECTION
// example: HideShowSection("General", "Customers", false);   // "false" = invisible
function HideShowSection(tabName, sectionName, visible) {
    try {
        Xrm.Page.ui.tabs.get(tabName).sections.get(sectionName).setVisible(visible);
    }
    catch (err) { }
}

// Get GUID value of Lookup Field
// example GetGUIDofLookup("primarycontactid");
function GetGUIDofLookup(fieldname) {
    if (Xrm.Page.data.entity.attributes.get(fieldname).getValue() != null) {
        return Xrm.Page.data.entity.attributes.get(fieldname).getValue()[0].id;
    }
    else return null;
}

// Get Name value of Lookup Field
// example GetNameofLookup("primarycontactid");
function GetNameofLookup(fieldname) {
    if (Xrm.Page.data.entity.attributes.get(fieldname).getValue() != null) {
        return Xrm.Page.data.entity.attributes.get(fieldname).getValue()[0].name;
    }
    else return null;
}

// Get Value of Text Field
// example GetTextField("telephone1");
function GetTextField(fieldname) {
    return Xrm.Page.data.entity.attributes.get(fieldname).getValue();
}

// Get Integer value of Option Set Field
// example GetOptionsetInteger("address1_addresstypecode");
function GetOptionsetInteger(fieldname) {
    return Xrm.Page.data.entity.attributes.get(fieldname).getValue();
}

// Get Text value of Option Set Field
// example GetOptionsetText("address1_addresstypecode");
function GetOptionsetText(fieldname) {
    if (Xrm.Page.data.entity.attributes.get(fieldname).getValue() != null) {
        return Xrm.Page.data.entity.attributes.get(fieldname).getSelectedOption().text;
    }
    else return null;
}

// Get Database Value of a Bit Field
// example GetBitValue("telephone1");
function GetBitValue(fieldname) {
    return Xrm.Page.data.entity.attributes.get(fieldname).getValue();
}

// Get Database Value of a Date Field
// example GetDate("createdon");
function GetDate(fieldname) {
    return Xrm.Page.data.entity.attributes.get(fieldname).getValue();
}

// Sets the time portion of a date field (and sets the date to today if blank)
// Example: SetTime('new_date2', 8, 30);
function SetTime(attributeName, hour, minute) {
        var attribute = Xrm.Page.getAttribute(attributeName);
        if (attribute.getValue() == null) {
            attribute.setValue(new Date());
        }
        attribute.setValue(attribute.getValue().setHours(hour, minute, 0));
}

// Converts a CRM date value into dd-mm-yyyy
// Example:     
//              var ReviewDate = Xrm.Page.data.entity.attributes.get(new_date2).getValue(); 
//              alert(FormatDate(ReviewDate));
function FormatDate(fieldname) {
    var d = Xrm.Page.data.entity.attributes.get(fieldname).getValue();
    if (d != null) {
        var curr_date = d.getDate();
        var curr_month = d.getMonth();
        curr_month++;  // getMonth() considers Jan month 0, need to add 1
        var curr_year = d.getFullYear();
        return curr_date + "-" + curr_month + "-" + curr_year;
    }
    else return null;
}

// Compares a date to today's date
// Example:     
//              var ReviewDate = Xrm.Page.data.entity.attributes.get(new_date2).getValue(); 
//              alert(DateCompare(ReviewDate));
// Returns:  "future date", "date is in the past", or "date is today"
function DateCompare(dateinput) {
    var today = new Date();
    var today_date = today.getDate();
    var today_month = today.getMonth();
    today_month++;
    var today_year = today.getFullYear();

    var dateinput_date = dateinput.getDate();
    var dateinput_month = dateinput.getMonth();
    dateinput_month++;
    var dateinput_year = dateinput.getFullYear();

    if (dateinput != null && dateinput_year > today_year) {  // future year
        return "future date";
    }
    else if (dateinput != null && dateinput_year < today_year) {  // prior year
        return "date is in the past";
    }
    else if (dateinput != null && dateinput_year == today_year && dateinput_month > today_month) { //current year, future month
        return "future date";
    }
    else if (dateinput != null && dateinput_year == today_year && dateinput_month < today_month) { //current year, prior month
        return "date is in the past";
    }
    else if (dateinput != null && dateinput_year == today_year && dateinput_month == today_month && dateinput_date > today_date) { //current year, current month, future date
        return "future date";
    }
    else if (dateinput != null && dateinput_year == today_year && dateinput_month == today_month && dateinput_date < today_date) { //current year, current month, prior date
        return "date is in the past";
    }
    else if (dateinput != null && dateinput_year == today_year && dateinput_month == today_month && dateinput_date == today_date) { //same date
        return "date is today";
    }
    else {
        return null;
    }
}

/////////////////////////////////////////////////////////////////////////////
// Here's a few more courtesy of Paul Kreeck
/////////////////////////////////////////////////////////////////////////////
///Gets the Attributes by its Name.
 GetAttributeByName: function (attributeName)
 {
 return Xrm.Page.getAttribute(attributeName);
 } 

///Sets the attributes required level.
 ///
 ///Values: ‘none’, ‘required’, ‘recommended’
SetAttributeRequiredLevel: function (attributeName, level)
 {
 var attribute = GetAttributeByName(attributeName);
 if (attribute != null)
 {
 return attribute.setRequiredLevel(level);
 }
 return null;
 }
 
///Sets whether data from the attribute will be submitted when the record is saved.
 ///
 ///Values: ‘always’, ‘never’, ‘dirty’
SetAttributeSubmitMode: function (attributeName, submitMode)
 {
 var attribute = GetAttributeByName(attributeName);
 
if (attribute != null)
 {
 if (submitMode == “always” || submitMode == “never” || submitMode == “dirty”)
 {
attribute.setSubmitMode(submitMode);
 }
 else
 {
 throw “Invalid Submit Mode parameter”;
 }
 }
 }

Here are some examples where I utilise the above functions:





function AccountFormOnLoad() {

    var FormType = GetFormType();

    alert("Form type: " + FormType);

    alert(GetDate("createdon"));

    alert(FormatDate("createdon"));

    SetTime('new_date2', 8, 30);

    var FieldDate = GetDate("new_date2");

    alert(DateCompare(FieldDate));

  

    if(FormType == "update") {

        alert("hiding General tab");

        HideShowTab("general", false);

        alert("UNhiding General tab");

        HideShowTab("general", true);

        alert("hiding address section on general tab");

        HideShowSection("general", "address", false);

        alert("UNhiding address section on general tab");

        HideShowSection("general", "address", true);

        alert("getting GUID of primary contact");

        alert(GetGUIDofLookup("primarycontactid"));

        alert("getting name of primary contact");

        alert(GetNameofLookup("primarycontactid"));

        alert("getting value of telephone1");

        alert(GetTextField("telephone1"));

        alert("getting integer value of address type");

        alert(GetOptionsetInteger("address1_addresstypecode"));

        alert("getting text value of address type");

        alert(GetOptionsetText("address1_addresstypecode"));

    }

}

To reference the jscript library on a CRM form add the shared function library web resource to the Form and then add a form-specific jscript library web resource after that. It is in this second jscript file that your form logic will sit and where you will make function calls against the shared functions library:

To reference the jscript library within a custom Ribbon button’s definition either;

simply reference the library on the form:

or, you can reference the library within the ribbon definition: