CRM Pre-Filtering:

CRM Pre-Filtering:

To enable the pre filtering in the report just we need to add an alias for the filtered views which starts with “CRMAF_”. A query such as “Select name from FilteredAccount” can simply be changed to “Select name from FilteredAccount as CRMAF_Account”. Aliasing the Filtered View with a prefix of CRMAF_ will allow CRM to recognize that you would like to enable this entity for pre-filtering.


When you enable the CRM Pre-filtering functionality using the CRMAF_ method, CRM will take a query such as the following and modify it when it is uploaded into CRM:
This is the sample query:-


SELECT name, accountnumber
FROM FilteredAccount as CRMAF_Account

Becomes:
SELECT name, accountnumber
FROM (@P1) as CRMAF_Account

Then CRM will pass a query to the P1 parameter depending on how the report is being filtered. For example: when we run the report from the Reports area, the Pre-filtering functionality show all Accounts that are Active, and the resulting query would be something like:

SELECT name, accountnumber
FROM (select FilteredAccount.* from FilteredAccount where statecode = 0) as CRMAF_Account


If we are running the same report within a specific Account, the resulting query would be something like:

SELECT name, accountnumber
FROM (select FilteredAccount.* from FilteredAccount where AccountId = ‘’) as CRMAF_Account

If we are running the same report for a list of Accounts (suppose 3 accounts are selected from a view), the resulting query would be something like:

SELECT name, accountnumber
FROM (select FilteredAccount.* from FilteredAccount where AccountId in (’<1staccountid>’, ‘<2ndaccountid>’, ‘<3rdaccountid>’) as CRMAF_Account

AJAX Calls using JavaScript to retrieve data for N:N relationship in MS CRM 4.0

//**************************************************
//AJAX Call to get the Competitors for an Oportunity
//**************************************************


var sOppID = crmForm.ObjectId;

var returnXML="";

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\">" +
GenerateAuthenticationHeader() +
" <soap:Body>" +
" <RetrieveMultiple xmlns=\"http://schemas.microsoft.com/crm/2007/WebServices\">" +
" <query xmlns:q1=\"http://schemas.microsoft.com/crm/2006/Query\" xsi:type=\"q1:QueryExpression\">" +
" <q1:EntityName>competitor</q1:EntityName>" +
" <q1:ColumnSet xsi:type=\"q1:AllColumns\" />" +
" <q1:Distinct>false</q1:Distinct>" +
" <q1:LinkEntities>" +
" <q1:LinkEntity>" +
" <q1:LinkFromAttributeName>competitorid</q1:LinkFromAttributeName>" +
" <q1:LinkFromEntityName>competitor</q1:LinkFromEntityName>" +
" <q1:LinkToEntityName>opportunitycompetitors</q1:LinkToEntityName>" +
" <q1:LinkToAttributeName>competitorid</q1:LinkToAttributeName>" +
" <q1:JoinOperator>Inner</q1:JoinOperator>" +
" <q1:LinkEntities>" +
" <q1:LinkEntity>" +
" <q1:LinkFromAttributeName>opportunityid</q1:LinkFromAttributeName>" +
" <q1:LinkFromEntityName>opportunitycompetitors</q1:LinkFromEntityName>" +
" <q1:LinkToEntityName>opportunity</q1:LinkToEntityName>" +
" <q1:LinkToAttributeName>opportunityid</q1:LinkToAttributeName>" +
" <q1:JoinOperator>Inner</q1:JoinOperator>" +
" <q1:LinkCriteria>" +
" <q1:FilterOperator>And</q1:FilterOperator>" +
" <q1:Conditions>" +
" <q1:Condition>" +
" <q1:AttributeName>opportunityid</q1:AttributeName>" +
" <q1:Operator>Equal</q1:Operator>" +
" <q1:Values>" +
" <q1:Value xsi:type=\"xsd:string\">" + sOppID + "</q1:Value>" +
" </q1:Values>" +
" </q1:Condition>" +
" </q1:Conditions>" +
" </q1:LinkCriteria>" +
" </q1:LinkEntity>" +
" </q1:LinkEntities>" +
" </q1:LinkEntity>" +
" </q1:LinkEntities>" +
" </query>" +
" </RetrieveMultiple>" +
" </soap:Body>" +
"</soap:Envelope>" +
"";

var xmlHttpRequest = new ActiveXObject("Msxml2.XMLHTTP");

xmlHttpRequest.Open("POST", "/mscrmservices/2007/CrmService.asmx", false);
xmlHttpRequest.setRequestHeader("SOAPAction","http://schemas.microsoft.com/crm/2007/WebServices/RetrieveMultiple");
xmlHttpRequest.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
xmlHttpRequest.setRequestHeader("Content-Length", xml.length);
xmlHttpRequest.send(xml);

var returnXML = xmlHttpRequest.responseXML;

if(returnXML != null)
{
var returnNodes = returnXML.selectNodes("//BusinessEntity/q1:competitorid");
if(returnNodes.length > 0)
{

// DEFINE YOUR LOGIC HERE


}
}

Generic method for Ajax calls using javascript to retrieve data in MS CRM 4.0

//limitation – This is the generic method supported only for 1:N or N:1 relationship



// This is a generic method for Ajax calls using javascript to retrieve data
//****************************************************************************
SoapRequestDynamic = function(entityName, searchColumn, searchValue, returnColumn)
{
var returnPacket = "";
for (var i = 0; i < returnColumn.length; i++)
{
returnPacket += " <q1:Attribute>" + returnColumn[i] + "</q1:Attribute>"
}

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\">" +
GenerateAuthenticationHeader() +
" <soap:Body>" +
" <RetrieveMultiple xmlns=\"http://schemas.microsoft.com/crm/2007/WebServices\">" +
" <query xmlns:q1=\"http://schemas.microsoft.com/crm/2006/Query\" xsi:type=\"q1:QueryExpression\">" +
" <q1:EntityName>" + entityName + "</q1:EntityName>" +
" <q1:ColumnSet xsi:type=\"q1:ColumnSet\">" +
" <q1:Attributes>" +
returnPacket +
" </q1:Attributes>" +
" </q1:ColumnSet>" +
" <q1:Distinct>false</q1:Distinct>" +
" <q1:Criteria>" +
" <q1:FilterOperator>And</q1:FilterOperator>" +
" <q1:Conditions>" +
" <q1:Condition>" +
" <q1:AttributeName>" + searchColumn + "</q1:AttributeName>" +
" <q1:Operator>Equal</q1:Operator>" +
" <q1:Values>" +
" <q1:Value xsi:type=\"xsd:string\">" + searchValue + "</q1:Value>" +
" </q1:Values>" +
" </q1:Condition>" +
" </q1:Conditions>" +
" </q1:Criteria>" +
" </query>" +
" </RetrieveMultiple>" +
" </soap:Body>" +
"</soap:Envelope>" +
"";

return xml;
}
// End of SoapRequestDynamic Function

// Start of HttpPostRequest
HttpPostRequest = function(xml)
{
var xmlHttpRequest = new ActiveXObject("Msxml2.XMLHTTP");
xmlHttpRequest.Open("POST", "/mscrmservices/2007/CrmService.asmx", false);
xmlHttpRequest.setRequestHeader("SOAPAction","http://schemas.microsoft.com/crm/2007/WebServices/RetrieveMultiple");
xmlHttpRequest.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
xmlHttpRequest.setRequestHeader("Content-Length", xml.length);
xmlHttpRequest.send(xml);

//alert(xmlHttpRequest.responseXML.xml);
var resultXml = xmlHttpRequest.responseXML.selectSingleNode("//BusinessEntity");

return resultXml;
}
// End of HttpPostRequest

var sObjectID = crmForm.ObjectId;

//Actual Call for Relationships
/// finding out opportunity relationships
//var buXml = HttpPostRequest(SoapRequestDynamic(entityName, searchColumn, searchValue, returnColumn));

var buXml = HttpPostRequest(SoapRequestDynamic("customeropportunityrole", "opportunityid", sObjectID, ["customeropportunityroleid"]));


if(buXml != null )
{
var buNodes = buXml.selectNodes("//BusinessEntity/q1:customeropportunityroleid");
if(buNodes.length > 0)
{
// DEFINE YOUR LOGIC HERE
}
}



// for your referance
//http://social.microsoft.com/Forums/en-US/crmdevelopment/thread/3ec3cfcd-62ea-49b5-9239-14aee58bd7b9

Hide navigation items of an entity in MS CRM 4.0

//define the entity names you want to hide

NavMenuHide("Service Lines","Target Achievements","Account Plan");

function NavMenuHide()
{
if (arguments == null arguments.length == 0) return;
var navBar = document.getElementById("crmNavBar");
if (!navBar) return;
var optionItems = navBar.getElementsByTagName("nobr");
var exitCount = 0;
for (opt=0; opt < optionItems.length ;opt++ )
{
for (a=0; a {
if (optionItems[opt].innerText == arguments[a])
{
// --- hide the menu item
optionItems[opt].parentNode.style.display = "none";
// --- have all of the args been processed?
exitCount ++;
if (exitCount == arguments.length) return;
}
}
}
}

Dynamic Picklist in CRM 4.0

var PAccountType = crmForm.all.new_picklist;
PAccountType.originalPicklistOptions = PAccountType.Options;
var PStartIndex =2;
var PEndIndex =3;
if (PStartIndex > -1 && PEndIndex > -1)
{
// Create a new array, which will hold the new picklist options
var PTempArray = new Array();

// Initialize the index for the temp array
var PIndex = 0;

// Now loop through the original Sub-Industry options, pull out the
// requested options a copy them into the temporary array.
for (var i = PStartIndex; i <= PEndIndex; i++)
{

PTempArray[PIndex] = PAccountType.originalPicklistOptions[i];
PIndex++;
}

PAccountType.Options = PTempArray;

Show the related records of an entity in a tab of the parent form in MS CRM 4.0

Show the related records of an entity in a tab of the parent form.

“tabSet” you can find in the customization, it’s basically the relationship name.
Or else you can run the Fiddler and get the complete URL of the related entity IFRAME (which appears in the parent form) and from the URL you can copy the “tabSet” name.

crmForm.all.new_iframe_name.src =
GetFrameSource(new_contact_new_qualification);

function GetFrameSource(tabSet)
{
if (crmForm.ObjectId != null)
{
var oId = crmForm.ObjectId;
var oType = crmForm.ObjectTypeCode;
var security = crmFormSubmit.crmFormSubmitSecurity.value;
return "areas.aspx?oId=" + oId + "&oType=" + oType + "&security=" +
security + "&tabSet=" + tabSet;
}
else
{
return "about:blank";
}
}

Currency symbol in reports in case of Multi currency in MS CRM 4.0

When we develop a custom report for the MSCRM always it’s a question to show the corresponding currency symbol. Take an example we have a report in which we are showing all the opportunity of value more than 1M (in all currencies).

If you will change the currency format to any of the out of box format it will show just the dollar ($) symbol.

To show the corresponding currency symbol of the opportunity, we need to select one more field in the SQL Query named “crm_moneyformatstring” from the corresponding filtered view. This field (crm_moneyformatstring) is there in every CRM entity if there is at test one currency field in the corresponding entity.

And define the format of the money field by selecting “crm_moneyformatstring” field from the dataset. (right click on the box in the table/matrix, click on the properties, select format , in the format select crm_moneyformatstring from the dataset)

Microsoft Dynamics CRM Pre-Filtering

Just need to alias the filtered views with a name that starts with “CRMAF_”. A query such as “Select name from FilteredAccount” can simply be changed to “Select name from FilteredAccount as CRMAF_Account”. Aliasing the Filtered View with a prefix of CRMAF_ will allow CRM to recognize that you would like to enable this entity for pre-filtering.

When you enable the CRM Pre-filtering functionality using the CRMAF_ method, CRM will take a query such as the following and modify it when it is uploaded into CRM:

SELECT name, accountnumber FROM FilteredAccount as CRMAF_Account

Becomes:

SELECT name, accountnumber FROM (@P1) as CRMAF_Account

Then CRM will pass a query to the P1 parameter depending on how the report is being filtered. For example: If you are running the report from the Reports area and use the Pre-filtering functionality to filter to only show Accounts that are Active, the resulting query would be something like:

SELECT name, accountnumber FROM (select FilteredAccount.* from FilteredAccount where statecode = 0) as CRMAF_Account

If you are within a specific Account and run the report, the resulting query would be something like:

SELECT name, accountnumber FROM (select FilteredAccount.* from FilteredAccount where AccountId = ‘’) as CRMAF_Account

When you are looking at a list of Accounts with 3 selected and choose the option to run the report against the selected records, the resulting query would be something like:

SELECT name, accountnumber FROM (select FilteredAccount.* from FilteredAccount where AccountId in (’<1staccountid>’, ‘<2ndaccountid>’, ‘<3rdaccountid>’) as CRMAF_Account

Custom Tooltips for every field on a CRM Form in MS CRM 4.0

Tooltips are available on every field on a MS CRM form. The current tooltips show the text of the label but we can change the contents of the current tooltips.
The restrictions to the tooltips are:-
1. Its unformatted text
2. And should not be more than 512 characters.

The onLoad code:
crmForm.all.new_attribute_c.title = “contents of the tooltip”

Capturing Previous Value on OnChange of Form Field in MS CRM 4.0

When you change the "Account Number" value, you want to populate a field called "Prior Account Number" with the previous value. The issue is that OnChange events happen AFTER the value has changed, so you can't copy the former value to the Prior Account Number field.

One solution is to use another field to store the previous value. Let's call this field accounttemp. (We will need to include this field on the form so we can access it via javascript, but we can hide it by either putting it on a hidden tab or using javascript to hide the field.)

Once we have our accounttemp and Prior Account Number field added to the Account form, we can add javascript to the OnChange event for the Account Number field. To make this happen we will need to make sure that the following steps happen in order:

1. Value of account temp field is copied to Prior Account Number field
2. Value of Account Number field is copied to the accounttemp field

Here's how that would look in Jscript:
crmForm.all.new_prioraccountnumber.DataValue=crmForm.all.new_accounttemp.DataValue;
crmForm.all.new_accounttemp.DataValue = crmForm.all.accountnumber.DataValue ;

The result is that the first time a value is entered into the Account Number field, the Prior Account Number field will remain blank, but the new value will be copied to the accounttemp field. The next time that the value of the Account number changes, the previous value will be copied to the Prior Account Number field.


Note—this will only work if the values are entered via the form. If you import this data from Scribe or the CRM import utility, the value will not be in the accounttemp field, so the process won't work for the initial change. The workaround is that when you import your account information, import the account number to the account number field AND the accounttemp field.

Load Form Tab on Click of the Tab in MS CRM 4.0

document.all.tab2Tab.attachEvent('onclick', UpdateTab2);

function UpdateTab2()
{
if(crmForm.ObjectId!=null)
{
var oId=crmForm.ObjectId
crmForm.all.new_accountguid.DataValue = oId;
if( oId != null )
{
oId=oId.substr(1,36) crmForm.all.IFRAME_Divisions.src=GetCrmServerUrl() + '/sfa/accts/areas.aspx?oType=1&security=852407&tabSet=new_account_new_divisions'+'&oId=%7b'+oId+'%7d';
}
}
}
function GetCrmServerUrl()
{
var CRM_URL='';
if(AUTHENTICATION_TYPE == 0)
{
CRM_URL = '/' + ORG_UNIQUE_NAME;
}
return CRM_URL;
}

Reorder Navigation Items in MSCRM 4.0

if(document.getElementById('crmNavBar'))
{
//arrabge the navigation Item in order you want to display
var oTempArray = new Array('navInfo','nav_new_account_new_accountplan','nav_new_account_new_accountserviceline','navContacts','navAddresses','navActivities', 'navActivityHistory', 'navSubAct','navRelationships','navOpps','navQuotes','navOrders','navInvoices','navService','navContracts','navListsInSFA','navCampaignsInSFA');
var i; var iStartIndex = 0;
// total no of navigation items defined in the oTempArray variable
var iEndIndex = 10;
var iEndObject = document.getElementById(oTempArray[iEndIndex]);
for(iStartIndex; iStartIndex <= iEndIndex; iStartIndex++ )
{
if(document.getElementById(oTempArray[iStartIndex]))
{
i = document.getElementById(oTempArray[iStartIndex]);
i.parentNode.parentNode.insertBefore(i.parentNode, iEndObject.parentNode );
}
}
}