Aggregate Fetch XML Calculation C# CRM

Hi Just want to share the Code to Calculate the Aggregate in CRM, especially for those who are in the CRM version, older than 2015 so that there will be no calculated and roll-up field so far.
Using this helper code you just need to pass the Parent Id, and it will do the rest of Calculation and of course you just need to supply the Operator (like SUM, Count, AVG, etc)

public string CalculateAggregateFetchXML(string strEntityName, string strAggregateAttributeName,
                                                       AggregateOperator aggregateOperator, string strFilterXML) {
            string strFetchXML = string.Empty;
            string strAggregateAlias = "nec_alias";

            strFetchXML = string.Format(@" 
                            <fetch distinct='false' mapping='logical' aggregate='true'> 
                                <entity name='{0}'> 
                                    <attribute name='{1}' alias='{2}' aggregate='{3}' />
                                    {4}
                                </entity> 
                            </fetch>", strEntityName, strAggregateAttributeName, strAggregateAlias, aggregateOperator.ToString(), strFilterXML);

            EntityCollection aggregateResult = CrmService.RetrieveMultiple(new FetchExpression(strFetchXML));
            decimal totalValue = 0;

            foreach (var c in aggregateResult.Entities) {
                decimal aggregate2 = 0;
                if (c.Attributes.Contains(strAggregateAlias)) {
                    AliasedValue alias = ((AliasedValue)c[strAggregateAlias]);

                    if (alias.Value is Money) {
                        aggregate2 = ((Money)((AliasedValue)c[strAggregateAlias]).Value).Value;
                    } else if (alias.Value is Int32 || alias.Value is int) {
                        aggregate2 = ((int)((AliasedValue)c[strAggregateAlias]).Value);
                    } else if (alias.Value is Decimal || alias.Value is decimal) {
                        aggregate2 = ((decimal)((AliasedValue)c[strAggregateAlias]).Value);
                    }

                    totalValue = aggregate2;
                }
            }
            return totalValue.ToString();
        }

And the AggregateOperator Enum

public enum AggregateOperator {
            [Description("sum")]
            sum = 1,
            //
            [Description("avg")]
            avg = 2,
            //
            [Description("min")]
            min = 3,
            //
            [Description("max")]
            max = 4,
            //
            [Description("count(*)")]
            count = 5,
            //
            [Description("countcolumn")]
            countcolumn = 5,
        }

This usage is very easy

strTotalAttByRace = CrmContext.CalculateAggregateFetchXML(“entityname” “primaryidfieldname”,
                                                  NEC.ESBU.Helpers.CrmHelper.AggregateOperator.count,strFilterXML);

//strFilterXML You can skip this or use this in case you need more condition

It will result you a string or you can change to Integer, that is the total aggregate result

*Example for Aggregate Result with fetch xml

Is to return total Attendance by Race (that stored in the Contact entity)

Attendance is many to 1 relationship with Contact

strFilterXML = string.Format(@"<filter type='and'> 
                                                <condition attribute='{0}' operator='eq' uitype='{1}' value='{2}' />
                                                <condition attribute='pa_sessionid' operator='eq' uitype='pa_session' value='{3}' />
                                                <condition attribute='pa_type' operator='eq' value='{4}' />
                                            </filter>
                                            <link-entity name='contact' from='contactid' to='pa_participantid' alias='af'>
                                                <filter type='and'>
                                                    <condition attribute='pa_race' operator='eq' value='{5}' />
                                                </filter>
                                            </link-entity>",
                            strAttributeProductIdName, strEntityProductIdName, guidProductId.ToString(), guidSessionId.ToString(),
                            (int)productType, (int)raceType);

This will be very useful if you want to calculate child record, either SUM, COUNT, or AVG for instance, then just pass the parent ID or add more fetch XML to add more filter

*Work for Money, Whole Number, and Decimal data type

Hope this helps!
Thanks

Service calendar appearance configuration

Hi,
A quick post related to the Service Calendar, just in case you want to configure or to change the appearance.

The Link

Please refer to this link:
https://msdn.microsoft.com/en-us/library/gg309501.aspx

Then you can try the CSS

The CSS Code

div.ganttBlockserviceappointmentStatus1
{
    border: 1px solid #FF0000;
    FILTER: progid:DXImageTransform.Microsoft.gradient(GradientType=0,startColorstr='#FF0000',endColorstr='#FF0000');
    background: -moz-linear-gradient(top,  #FF0000 0%, #FF0000 100%);
    background: -webkit-linear-gradient(top,  #FF0000 0%,#FF0000 100%);
    background: -ms-linear-gradient(top,  #FF0000 0%,#FF0000 100%);
    background: linear-gradient(top,  #FF0000 0%,#FF0000 100%);
} 

div.ganttBlockserviceappointmentStatus2
{
    border: 1px solid #00FF00;
    FILTER: progid:DXImageTransform.Microsoft.gradient(GradientType=0,startColorstr='#00FF00',endColorstr='#00FF00');
    background: -moz-linear-gradient(top,  #00FF00 0%, #00FF00 100%);
    background: -webkit-linear-gradient(top,  #00FF00 0%,#00FF00 100%);
    background: -ms-linear-gradient(top,  #00FF00 0%,#00FF00 100%);
    background: linear-gradient(top,  #00FF00 0%,#00FF00 100%);
}

div.ganttBlockserviceappointmentStatus3
{
    border: 1px solid #0000FF;
    FILTER: progid:DXImageTransform.Microsoft.gradient(GradientType=0,startColorstr='#0000FF',endColorstr='#0000FF');
    background: -moz-linear-gradient(top,  #0000FF 0%, #0000FF 100%);
    background: -webkit-linear-gradient(top,  #0000FF 0%,#0000FF 100%);
    background: -ms-linear-gradient(top,  #0000FF 0%,#0000FF 100%);
    background: linear-gradient(top,  #0000FF 0%,#0000FF 100%);
} 

div.ganttBlockserviceappointmentStatus4
{
    border: 1px solid #FFFF00;
    FILTER: progid:DXImageTransform.Microsoft.gradient(GradientType=0,startColorstr='#FFFF00',endColorstr='#FFFF00');
    background: -moz-linear-gradient(top,  #FFFF00 0%, #FFFF00 100%);
    background: -webkit-linear-gradient(top,  #FFFF00 0%,#FFFF00 100%);
    background: -ms-linear-gradient(top,  #FFFF00 0%,#FFFF00 100%);
    background: linear-gradient(top,  #FFFF00 0%,#FFFF00 100%);
} 

div.ganttBlockserviceappointmentStatus6
{
    border: 1px solid #FF00FF;
    FILTER: progid:DXImageTransform.Microsoft.gradient(GradientType=0,startColorstr='#FF00FF',endColorstr='#FF00FF');
    background: -moz-linear-gradient(top,  #FF00FF 0%, #FF00FF 100%);
    background: -webkit-linear-gradient(top,  #FF00FF 0%,#FF00FF 100%);
    background: -ms-linear-gradient(top,  #FF00FF 0%,#FF00FF 100%);
    background: linear-gradient(top,  #FF00FF 0%,#FF00FF 100%);
} 

div.ganttBlockserviceappointmentStatus7
{
    border: 1px solid #00FFFF;
    FILTER: progid:DXImageTransform.Microsoft.gradient(GradientType=0,startColorstr='#00FFFF',endColorstr='#00FFFF');
    background: -moz-linear-gradient(top,  #00FFFF 0%, #00FFFF 100%);
    background: -webkit-linear-gradient(top,  #00FFFF 0%,#00FFFF 100%);
    background: -ms-linear-gradient(top,  #00FFFF 0%,#00FFFF 100%);
    background: linear-gradient(top,  #00FFFF 0%,#00FFFF 100%);
}

div.ganttBlockserviceappointmentStatus8
{
    border: 1px solid #7F7F7F;
    FILTER: progid:DXImageTransform.Microsoft.gradient(GradientType=0,startColorstr='#7F7F7F',endColorstr='#7F7F7F');
    background: -moz-linear-gradient(top,  #7F7F7F 0%, #7F7F7F 100%);
    background: -webkit-linear-gradient(top,  #7F7F7F 0%,#7F7F7F 100%);
    background: -ms-linear-gradient(top,  #7F7F7F 0%,#7F7F7F 100%);
    background: linear-gradient(top,  #7F7F7F 0%,#7F7F7F 100%);
} 

I know that this is limited configuration that you might expect more to configure!
Thanks

SQL Query to list the Entity & Display Name in Microsoft Dynamics CRM

It is just a brief post since I need this in my current project and want to share to you and also in case I forgot where I did save it.

So here is the SQL Query as per mentioned in the Title

The Query

select en.Name, en.LogicalName, displayname.ObjectColumnName, displayname.Label, IsCustomEntity from 

entityview en
inner join LocalizedLabelLogicalView displayname
on en.EntityId = displayname.ObjectId
and displayname.ObjectColumnName = 'LocalizedName'
where IsCustomEntity = 1 -- remove this if you want to show the system entities as well
--and add the isActivity = 0 if you want to filter out the Activity entity
--if you want to remove like post album, filter, etc
--and en.Name not like '%msdyn%'
order by label

Result

If you have multiple language for display name then I believe you need to join to the language ID as well.

Hope this helps! Thanks

Hide ‘Add Existing Button’ or Plus (+) Button In CRM 2013 Subgrid

Brief Introduction

This requirement often comes and the only supported way is to either hide through Security Role (which in most cases this is not possible and using Ribbon Workbench to hide it), but sometimes you need to only hide in some forms and you need to hide all necessary ribbons using Ribbon Workbench (FYI, one of my favorite tool)
So here is the code to hide the + button for Subgrid:

The Code

function hideAddButtonSubgrid(subgridId) {
    try {
        addEventToGridRefresh(subgridId, setAddButtonDisplayNone);
    }
    catch (e) {

    }
}

function setAddButtonDisplayNone(subgridId) {
    var imageId = subgridId + "_" + "addImageButton";
    if (imageId) {
        document.getElementById(imageId).style.display = 'none';
    }
    //var imageId2 = subgridId + "_" + "addImageButtonImage";
    //document.getElementById(imageId2).style.display = 'none';
}

function addEventToGridRefresh(subgridId, functionToCall) {
    // retrieve the subgrid
    var grid = document.getElementById(subgridId);
    // if the subgrid still not available we try again after 1 second
    if (grid == null) {
        setTimeout(function () { addEventToGridRefresh(subgridId, functionToCall); }, 1000);
        return;
    }

    // add the function to the onRefresh event
    grid.control.add_onRefresh(functionToCall);

    var imageId = subgridId + "_" + "addImageButton";
    if (imageId) {
        if (document.getElementById(imageId).style.display.toLowerCase() == "block") {
            setAddButtonDisplayNone(subgridId);
        }
    }
}

How to Call

Just use this code to call:

hideAddButtonSubgrid("mysubgrid_name");

And here enjoy the result

Result

*Before:



*After applied:



*Note: This is undocumented in SDK, so use as per your own risk, I need this as the must-to-have requirement so that I have to try workaround to make it happened to the customer.

Hope this helps!
Thanks.

CRM Error: There is no active transaction. This error is usually caused by custom plug-ins that ignore errors from service calls and continue processing.

Hi guys,

Just a quick one.

Just now I receive this error from my triggered custom plugin:

"There is no active transaction. This error is usually caused by custom plug-ins that ignore errors from service calls and continue processing."

And I believe that this often happen to the development.

Root Cause

Here is the root cause:
1. I have a custom plugin in the onCreate event.

2. Inside my custom plugin, I have Assign function

3. I try to put ‘try and catch’ just to avoid the error

4. Then it hits another error which is: “There is no active transaction. This error is usually caused by custom plug-ins that ignore errors from service calls and continue processing.”

5. So, I put a logger

6. And I have this error, instead:



Which this error is actually CRM System Error:

SecLib::CrmCheckPrivilege failed. Returned hr = -2147220943 on UserId: 01dc8c30-eb68-e511-80f2-00155dad0019 and PrivilegeType: Read

So, my conclusion:

Conclusion

1. My Team/User that I want to assign to does not have Any Security Role that having this Read privilege for the entity record object i want to assign to.

2. I try to skip the CRM Error by putting the try and catch just before the ‘Assign’ request

3. But, it fails to proceed, instead, CRM still insists to block this creation..

4. Because it is also right, you cannot do it anyway, CRM system plugin would still block and there is no way you skip the process that needs another rule to apply.

5. Eventhough i put this try catch and try to cancel the Assignment, I still receive the error:

What to Check

So, if you find this error, please check:

- Whether you have custom plugin/custom workflow active triggered
- Whether you put skipping the error that will have impact to the CRM process, it is not possible
- Actually you better to log the error
- Because you won’t know what it is
- This is not your logic wrong in your custom plugin
- This just you need to fix why CRM cannot proceed?
- Is that because your user/team does not have privilege or you missed some parameters required
- This error might happen like for Assignment, Lead Qualification, Quote creation, etc

Hope this helps.

Call Print Preview in CRM and Put in the Web Resource

Sometimes we need summary and as we know, CRM has the Print preview picture, though to call this you need to click the gear setting icon.

So, just in case you want to show the print preview and you want to modify it or add some component, you can put it in the web resource.

And here is the sample code

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta charset="utf-8" />
<title></title>
<script src="ClientGlobalContext.js.aspx" type="text/javascript"></script>
<!--change this your correct URL can be 
<script src="../../ClientGlobalContext.js.aspx" type="text/javascript"></script>-->
<script>
debugger;

function CallPrintPreview() {
try {

var x = Xrm.Page.ui;

var formId = window.parent.Xrm.Page.ui.formSelector.getCurrentItem().getId();
var recordId = window.parent.Xrm.Page.data.entity.getId();

var objectType = window.parent.Xrm.Page.context.getQueryStringParameters().etc;

var url = Xrm.Page.context.getClientUrl() + "/_forms/print/print.aspx?allsubgridspages=false&formid=" +
formId + "&id=" + recordId + "&objectType=" + objectType;

var iframeSRC = '';
iframeSRC = url;
if (iframeSRC != '') {
document.getElementById("iframePreview").src = iframeSRC;
}
}
catch (e) {

}
}

//YOU CAN ALSO CALL FROM RIBBON AS WELL

//USE THIS FUNCTION IF YOU WANT TO OPEN THE WEB RESOURCE OUTSIDE THE CRM FORM, YOU NEED PARAMETER FROM THE CRM FORM
function CallPrintPreviewByParams(formId, recordId, objectType) {
try {
var url = Xrm.Page.context.getClientUrl() + "/_forms/print/print.aspx?allsubgridspages=false&formid=" +
formId + "&id=" + recordId + "&objectType=" + objectType;

var iframeSRC = '';
iframeSRC = url;
if (iframeSRC != '') {
document.getElementById("iframePreview").src = iframeSRC;
}
}
catch (e) {

}
}

function Submit() {
//put your save button logic here
}

window.onload = CallPrintPreview;

</script>

</head>
<body onload="CallPrintPreview();">
<div id="divIframePrintPreview">
<iframe
id="iframePreview"
src="about:blank"
style="width: 100%; height: 100%; overflow: hidden; border: none; border-width: 0;" frameborder="0"></iframe>
</div>

<!--//you can also add additional in the web resource

<div id="divBody">
<table>
<tr>
<td>Are you sure to submit?</td>
<td></td>
</tr>
<tr>
<td>Remarks:</td>
<td>
<textarea name="comment" form="usrform" style="height: 68px">Enter text here...</textarea>
</td>
</tr>
<tr>
<td colspan="2" align="center">
<button value="Submit" title="Submit" name="btnSubmit" type="button" onclick="Submit();">Submit</button>
</td>
</tr>
</table>



</div>-->
</body>
</html>

*I use this as the concept only, you might use your own creative design based on the requirement

So, you can add the remark, for example for the modification or like signature to say ‘OK’ and submit, simple example here:

Launch from the button/put in the form











Hope this helps!

Thanks.

Visual Studio 2015 is here!

To boost your productivity,

You can try to click this link:
Click me!

And have a look..
Thanks.