MS CRM Customization: February 2015

Tuesday, February 24, 2015

MS CRM form load Performance Anyalyzer

This is good Microsoft CRM Hidden tool to check how much time is taken by form to load.

All Credit goes to Gareth Tucker

This tool opens when you click CTRL +SHIFT +Q

Here are details about how to use Form Load Performance analyzer tool

http://garethtuckercrm.com/2014/07/01/microsoft-crm-form-load-performance-analyzer/

Add Account/Contact GUID in email template

In CRM Email template, you cannot add record id, but sometime we need to send GUID in email or need to send record URL which will contain record ID/ GUID.

To Add GUID in email template,

1. Create one text attribute in Account or contact entity.. say Account GUID.

2. Add this attribute on form, so that we can able to use it in JavaScript.

3. Add following JavaScript code on form load.

function getRecordId() {

var accountGuid = Xrm.Page.getAttribute('new_accountguid').getValue()

if (Xrm.Page.ui.getFormType() == 2 && accountGuid == null) {

var accountId = Xrm.Page.data.entity.getId();

Xrm.Page.getAttribute('new_accountguid').setValue(accountId);

Xrm.Page.data.entity.save();

}

Here I am getting account id and updating in custom attribute. We can do this only if existing record is opened.

If want to populate this custom Account GUID attribute on save of account record, then need to create custom plugin and register on Post-Save method. Make sure.. this plugin is not going into Loop by adding some condition like if new_accountguid is null then only execute & update account.

4. Now open or create email template for account and add this custom attribute in email template

5. Now use this email template for Account..

Another option will be by updating Template body XML. but for this need to update template XML using SQL query.

CRM stores templates in Template table.

to get particular template,

select Body from Template where Title='Account Reconnect'

Email template body is represented in XML and xml is look like

if email template is like

Notice that account name is selected like

< xsl:when test="account/name" >

< xsl:value-of select="account/name" / >

< /xsl:when >

If changed this to

< xsl:when test="account/accountid" >

<xsl:value-of select="account/accountid" / >

< /xsl:when >

and updated back template body using SQL statement

Now when you use this email template then you will see GUID in email template.

Friday, February 20, 2015

Minimum product quantity required when creating Quote/order/invoice

Some time in order needs to products with certain Quantity, if product added below certain Quantity, then need to stop user from creating quote.

I am doing this functionality using JavaScript.

In this example I am using crmFetchkit.js. this kit is available on http://crmtoolkit.codeplex.com

Here are steps

1. Create JavaScript web resource and add following code in JavaScript.. I created Js web resource with name Quote.js
In this script I am using FetchXML to retrieve products from Quote. To generate FetchXML, easiest option is using Advanced find.

2. Add latest jquery library in form library

3. Add CrmFetchkit.js web resource in Form library

4. Add Quote.Js in form library

5. Call CheckPrductQuantity with product name as parameter function onLoad and OnSave event of Form.

6. Save and publish your form.

7. Now if product added with quantity less than 5, it will show message as below.

Wednesday, February 18, 2015

Convert Option set to multi select Checkbox list

1. Create two custom attributes to store selected value and selected text from check box list.

2. Add both attributes on form, this is required, it is not required to show those attributes on form, but those need on Form, so that JavaScript code will find those attributes.

3. Add latest JQuery library in form library

4. Create HTML web resource and add following code in HTML web resource

// function will be called when web resource is loaded on Form.

$(document).ready(function () {

ConvertDropDownToCheckBoxList();

});

//Coverts option list to checkbox list.

function ConvertDropDownToCheckBoxList() {

var dropdownOptions = parent.Xrm.Page.getAttribute("new_makeyear").getOptions();

var selectedValue = parent.Xrm.Page.getAttribute("new_selectedyears").getValue();

$(dropdownOptions).each(function (i, e) {

var rText = $(this)[0].text;

var rvalue = $(this)[0].value;

var isChecked = false;

if (rText != '') {

if (selectedValue != null && selectedValue.indexOf(rvalue) != -1)

isChecked = true;

/* Remove spaces before input, label word and end tags of input & label*/

var checkbox = "< input type='checkbox' name='r'/ >< label> " + rText + "</ label>"

$(checkbox)

.attr("value", rvalue)

.attr("checked", isChecked)

.attr("id", "id" + rvalue)

.click(function () {

//To Set Picklist Select Values

var selectedOption = parent.Xrm.Page.getAttribute("new_selectedyears").getValue();

if (this.checked) {

if (selectedOption == null)

selectedOption = rvalue+"";

else

selectedOption = selectedOption + "," + rvalue

}

else {

var tempSelected = rvalue + ",";

if (selectedOption != null) {

if (selectedOption.indexOf(tempSelected) != -1)

selectedOption = selectedOption.replace(tempSelected, "");

else

selectedOption = selectedOption.replace(rvalue, "");

}

parent.Xrm.Page.getAttribute("new_selectedyears").setValue(selectedOption);

//To Set Picklist Select Text

var selectedYear = parent.Xrm.Page.getAttribute("new_selectedyeartext").getValue();

if (this.checked) {

if (selectedYear == null)

selectedYear = rText+"";

else

selectedYear = selectedYear + "," + rText

}

else {

var tempSelectedtext = rText + ",";

if (selectedYear != null) {

if (selectedYear.indexOf(tempSelectedtext) != -1)

selectedYear = selectedYear.replace(tempSelectedtext, "");

else

selectedYear = selectedYear.replace(rText, "");

}

parent.Xrm.Page.getAttribute("new_selectedyeartext").setValue(selectedYear);

})

.appendTo(checkboxList);

}

});

}

</script>

</div>

</body></html>

Change new_makeyear with option set schema name.

Change new_selectedyears with custom attribute which is storing selected values.

Change new_selectedyeartext with custom attribute which is storing selected Text.

1. Add HTML web resource on Form.

2. Save and publish your customization.

Final output will be like

Tuesday, February 17, 2015

Dynamically Filter form sub grid to show related records

Scenario:

I have different Cars in system, and cars has different categories, when one car record is opened, I want to show all other cars of same category in sub grid.

Main thing is that there is not any relationship between cars.

Basically need to filter sub grid dynamically based on category of current record and show related records.

Resolution:

To achieve this there is not fully supported way, so I did using unsupported way..

1. Add Sub grid on form for car entity.

When adding sub grid, make sure initially show all records.

2. Add JavaScript web resource in Form library

3. Add following code in JavaScript Web resource

4. Save form and publish.

5. Now form subgrid is showing only related records based on category

Monday, February 9, 2015

MS CRM Audit Database Table Details

CRM audit table contains column

AttributeMask - Column number of attribute from either Attribute table or from MetadataSchema.Attribute

CRM stores all attributes changed in single transaction in a string format concatenate by ,

Attribute mask column contains values like

,47,45,42,96,93,107,201,28,25,53,32,14,90,178,121,61,55,108,11,35,8,177,120,69,122,117,209,46,80,78,59,10051,30,102,50,71,4,202,73,3,5,128,54,51,206,210,236,19,126,124,38,184,34,31,231,118,33,43,98,52,10037,70,123,129,132,133,103,39,114,22,48,56,10026,235,99,97,181,183,95,234,18,21,200,10045,49,106,109,110,16,24,67,74,175,44,27,139,125,208,134,10053,180,130,2,41,36,92,76,94,179,113,127,17,10039,101,233,10022,15,176,174,10043,29,203,119,37,72,40,20,173,211,91,111,105,131,10047,6,116,104,23,12,57,10041,66,100,115,26,

To get list of attribute name use query like

SELECT ar.name,
ar.ColumnNumber
FROM MetadataSchema.Attribute ar INNER JOIN
MetadataSchema.Entity en ON ar.EntityId = en.EntityId
WHERE en.ObjectTypeCode=2 AND ar.ColumnNumber=47

Or

SELECT ar.name,
ar.ColumnNumber
FROM Attribute ar INNER JOIN
Entity en ON ar.EntityId = en.EntityId
WHERE en.ObjectTypeCode=2 AND ar.ColumnNumber=47

Action:
Action column stores what kind of action performed on record. Action would be Create, Update, Delete, Activate, Fulfill etc. In audit table CRM stored action value like 2 or 3 or 4 etc.

To get all action value from Database use following query

SELECT Value as Action,AttributeValue as ActionValue FROM StringMap WHERE AttributeName='action'

and here is list of actions in CRM 2013.

0 - Unknown,	25 - Disqualify,	44 - Win,	63 - Enabled for organization,
1 - Create,	26 - Submit,	45 - Lose,	64 - User Access via Web,
2 - Update,	27 - Reject,	46 - Internal Processing,	65 - User Access via Web Services,
3 - Delete,	28 - Approve,	47 - Reschedule,	100 - Delete Entity,
4 - Activate,	29 - Invoice,	48 - Modify Share,	101 - Delete Attribute,
5 - Deactivate,	30 - Hold,	49 - Unshare,	102 - Audit Change at Entity Level,
11 - Cascade,	31 - Add Member,	50 - Book,	103 - Audit Change at Attribute Level,
12 - Merge,	32 - Remove Member,	51 - Generate Quote From Opportunity,	104 - Audit Change at Org Level,
13 - Assign,	33 - Associate Entities,	52 - Add To Queue,	105 - Entity Audit Started,
14 - Share,	34 - Disassociate Entities,	53 - Assign Role To Team,	106 - Attribute Audit Started,
15 - Retrieve,	35 - Add Members,	54 - Remove Role From Team,	107 - Audit Enabled,
16 - Close,	36 - Remove Members,	55 - Assign Role To User,	108 - Entity Audit Stopped,
17 - Cancel,	37 - Add Item,	56 - Remove Role From User,	109 - Attribute Audit Stopped,
18 - Complete,	38 - Remove Item,	57 - Add Privileges to Role,	110 - Audit Disabled,
20 - Resolve,	39 - Add Substitute,	58 - Remove Privileges From Role,	111 - Audit Log Deletion,
21 - Reopen,	40 - Remove Substitute,	59 - Replace Privileges In Role,	112 - User Access Audit Started,
22 - Fulfill,	41 - Set State,	60 - Import Mappings,	113 - User Access Audit Stopped,
23 - Paid,	42 - Renew,	61 - Clone,
24 - Qualify,	43 - Revise,	62 - Send Direct Email,

ObjectId:
Object Id the record id, for which this audit record is created.

UserId:
The user who performed action on record.

ChangeData:
Change Data column stores old data for the attribute. If you changed First name of contact from James to Jim, then in Change data James will be stored.
Values in Change Data are stored in same order as attribute mask stored.
If First name attribute mask is 2 and last name is 3, then in AttributeMask it would be ,2,3 and Change data would be James~Bond

Change Data column data is separated by ~ by per attribute.

Operation:
Operation column stores information of what kind of operation is performed on record.
CRM has only four operations
1 - Create , 2 - Update, 3 - Delete, 4 - Access.

To get directly operation values from database use

SELECT Value,AttributeValue FROM StringMap WHERE AttributeName='operation'

ObjectTypeCode:
Object Type code is entity object type code.

To find object type code for entity use

SELECT ObjectTypeCode, LogicalName FROM Entity WHERE Name='contact'

If want to get entity details from object type code then use

SELECT ObjectTypeCode, LogicalName FROM Entity WHERE ObjectTypeCode=1