Tuesday, 27 March 2012

Retrieve Label/Values of Option Set/Two Options Attributes using SQL Script

When you want to retrieve the list of  Label and Values of Option Set and Two Options attributes for documentation, development or whatever purpose, you can query by using the following SQL Script.
You will have to remove/modify the two lines of conditions ("new_testentity" and "new_testoptionsetattribute") accordingly to your requirement.

 SELECT DISTINCT  
         Entity.LogicalName AS EntitySchemaName  
 ,        Attribute.LogicalName AS AttributeSchemaName  
 ,        OptionSet.Name AS OptionSetName  
 ,        AttributePicklistValue.Value AS OptionValue  
 ,        LocalizedLabel.Label AS OptionText  
 FROM    MetadataSchema.LocalizedLabel  
     INNER JOIN MetadataSchema.AttributePicklistValue ON LocalizedLabel.ObjectId = AttributePicklistValue.AttributePicklistValueId  
     INNER JOIN MetadataSchema.OptionSet ON AttributePicklistValue.OptionSetId = OptionSet.OptionSetId  
     INNER JOIN MetadataSchema.Attribute ON OptionSet.OptionSetId = Attribute.OptionSetId  
     INNER JOIN MetadataSchema.Entity ON Attribute.EntityId = Entity.EntityId  
 WHERE LocalizedLabel.Label <> ''  
 AND Entity.LogicalName = 'new_testentity'  
 AND Attribute.LogicalName = 'new_testoptionsetattribute'  
 ORDER BY Entity.LogicalName, Attribute.LogicalName, AttributePicklistValue.Value  

This is the alternative query over StringMap table.

 SELECT * FROM StringMap SM  
 INNER JOIN Entity E ON E.ObjectTypeCode = SM.ObjectTypeCode  
 WHERE E.Name = 'new_testentity'  
 AND SM.AttributeName = 'new_testoptionsetattribute'  

Populate Regarding Attribute in New Email Form


When we created a new email for a record by Clicking “New Activity” button in the “Activities” associated view, it will prompt us to select the activity type.

And when the user select E-mail and click OK, the new email form will is shown with the “From” attribute populated with the current user and the “Regarding” attribute populated with the record from which we clicked “New Activity” and everything is fine.


According to the users’ requirement, there’s a case that we have to show the Subgrid of email related to the record on the form. And that’s where the problem started. When the user click “Add New E-mail” button from the Subgrid and the new Email form is loaded, the record is not populated in the regarding attribute. So, the user demands for the same functionality as the new Activity since it would be a hassle to select the Regarding attribute manually.


So, my solution was writing a JavaScript code at the onLoad of the Email form which will auto populate the “Regarding” attribute if email form is loaded from a record. In order to fill up the Regarding lookup value, we’ll need the RecordId, LogicalName of the Entity and PrimaryName (for display). The logic of the code is to check the _CreateFromId and _CreateFromType from the QueryStringParameters and fetch the required parameters using RetrieveAllEntitiesAsync from "SDK.MetaData.js" of Microsoft. First, you need to create a new JavaScript webresource, copy the code of "SDK.MetaData.js" and add it into the Form Libraries of the Email form. Then, add the following codes in the JavaScript Library of Email form and call the OnLoad function.

 function OnLoad()  
 {  
     var FORM_TYPE_CREATE = 1;  
       
       
     if (Xrm.Page.ui.getFormType() == FORM_TYPE_CREATE && Xrm.Page.context.getQueryStringParameters()["_CreateFromId"] != null && Xrm.Page.context.getQueryStringParameters()["_CreateFromType"] != null)  
     {  
         SDK.MetaData.RetrieveAllEntitiesAsync(SDK.MetaData.EntityFilters.Entity, false, successRetrieveAllEntities, errorRetrieveAllEntities);  
     }  
 }  
   
 function successRetrieveAllEntities(entityMetadataCollection)  
 {  
     var entityName = "";  
     var PrimaryNameAttribute = "";  
     for (var i = 0; i < entityMetadataCollection.length; i++)  
     {  
         if (entityMetadataCollection[i].ObjectTypeCode == Xrm.Page.context.getQueryStringParameters()["_CreateFromType"])  
         {  
             entityName = entityMetadataCollection[i].LogicalName;  
             PrimaryNameAttribute = entityMetadataCollection[i].PrimaryNameAttribute;  
             break;  
         }  
     }  
       
     if (entityName != "" && PrimaryNameAttribute != "")  
     {  
         var displayValue = RetrieveMultiple(entityName, PrimaryNameAttribute, entityName + "id|Equal|" + Xrm.Page.context.getQueryStringParameters()["_CreateFromId"]);  
         Xrm.Page.getAttribute("regardingobjectid").setValue( [{id: Xrm.Page.context.getQueryStringParameters()["_CreateFromId"], name: displayValue, entityType: entityName}]);  
     }  
 }  
   
 function errorRetrieveAllEntities(error)  
 {  
     alert(error.message);  
 }  
   
 function RetrieveMultiple(entity, attribute, condition)  
 {  
     // Usage => RetrieveMultiple("new_entityname", "new_attribute1|new_attribute2|new_attribute3", "new_attribute1|Equal|value1||new_attribute2|Like|value2||new_attribute3|NotNull";  
     // Refer to the following link for more operators >> http://msdn.microsoft.com/en-us/library/bb959309.aspx  
       
     // Prepare variables to retrieve the records.  
     var attributes = attribute.split('|');  
     var authenticationHeader = GenerateAuthenticationHeader();  
       
     // Prepare the SOAP message.  
     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'>"+   
     authenticationHeader+   
     "<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>" + entity + "</q1:EntityName>"+   
     "<q1:ColumnSet xsi:type='q1:ColumnSet'>"+   
     "<q1:Attributes>";  
       
     for (i = 0; i < attributes.length; i++)  
         xml += "<q1:Attribute>" + attributes[i] + "</q1:Attribute>"  
       
     xml +=  
     "</q1:Attributes>"+   
     "</q1:ColumnSet>"+   
     "<q1:Distinct>false</q1:Distinct>"+   
     "<q1:Criteria>"+   
     "<q1:FilterOperator>And</q1:FilterOperator>"+   
     "<q1:Conditions>";  
       
       
     var conditionDetails;  
     var conditions = condition.split('||');  
     for (i = 0; i < conditions.length; i++)  
     {  
         conditionDetails = conditions[i].split('|');  
         xml +=   
         "<q1:Condition>"+  
         "<q1:AttributeName>" + conditionDetails[0] + "</q1:AttributeName>"+   
         "<q1:Operator>" + conditionDetails[1] + "</q1:Operator>";  
           
         if (conditionDetails.length > 2)  
         {  
             xml +=   
             "<q1:Values>"+   
             "<q1:Value xsi:type='xsd:string'>" + conditionDetails[2] + "</q1:Value>"+   
             "</q1:Values>";  
         }  
           
         xml +=   
         "</q1:Condition>";  
     }  
       
     xml +=   
     "</q1:Conditions>"+   
     "</q1:Criteria>"+   
     "</query>"+   
     "</RetrieveMultiple>"+   
     "</soap:Body>"+   
     "</soap:Envelope>";  
     // Prepare the xmlHttpObject and send the request.  
     var xHReq = new ActiveXObject("Msxml2.XMLHTTP");  
     xHReq.Open("POST", "/mscrmservices/2007/CrmService.asmx", false);  
     xHReq.setRequestHeader("SOAPAction","http://schemas.microsoft.com/crm/2007/WebServices/RetrieveMultiple");  
     xHReq.setRequestHeader("Content-Type", "text/xml; charset=utf-8");  
     xHReq.setRequestHeader("Content-Length", xml.length);  
     xHReq.send(xml);  
     // Capture the result.  
     var resultXml = xHReq.responseXML;  
   
     // Check for errors.  
     var errorCount = resultXml.selectNodes('//error').length;  
     if (errorCount != 0)  
     {  
         var msg = resultXml.selectSingleNode('//description').nodeTypedValue;  
         //alert(msg);  
         return msg;  
     }  
     // Parse and display the results.  
     else  
     {  
         var results = resultXml.getElementsByTagName('BusinessEntity');  
         var msg = "";  
         if (results.length == 0)  
         {  
             //msg = "No record found with the given criteria.";  
             //alert(msg);  
             //return;  
             return null;  
         }  
         else  
         {  
             var result = "";  
             for (i = 0;i < results.length;i++)  
             {  
                 if (i != 0)  
                     result += '|';  
                 for (j = 0; j < attributes.length; j++)  
                 {  
                     if (i != 0 || j != 0)  
                         result += '|';  
                     if (results[i].selectSingleNode('./q1:'+ attributes[j]) != null)  
                         result += results[i].selectSingleNode('./q1:'+ attributes[j]).nodeTypedValue;  
                 }  
                   
             }  
             return result;  
         }  
     }  
 }  

Leave me a comment if you have any problem with the above code.

Friday, 2 March 2012

ForceSubmit vs setSubmitMode in CRM 2011 Javascript

Previously, we used
 crmForm.all.[fieldname].ForceSubmit = true;  
in CRM 4.0 to force the CRM system to submit the form data on disabled fields.

But in CRM 2011, the method is changed to
 Xrm.Page.getAttribute([fieldname]).setSubmitMode("always");  
but the old CRM 4.0 object model is still supported for backward compatibility so that setting true flag to ForceSubmit property seems to be working. But actually, there is a difference between using ForceSubmit and setSubmitMode to save the data in the disabled fields in CRM 2011.

In CRM 2011, there's a Xrm.Page.data.entity.getIsDirty() method, which returns a Boolean value that indicates if any fields in the form have been modified. When we set true flag to ForceSubmit property of the disabled field, the IsDirty flag of the entity won't be changed when the value of that disabled field is updated from the JavaScript code. But when we use setSubmitMode, the IsDirty flag of the entity will be changed when the value of that disabled field is updated.

So, there won't be any difference if setting the value of disabled field is based on the onChange value of another attribute because the entity level IsDirty flag will be updated by the onChange of the latter. But in some scenarios, the value of the disabled field is set by the Ribbon Button event and if we use ForceSubmit entity level IsDirty flag won't be updated which will cause the Xrm.Page.data.entity.save() not working since the method only check the entity level IsDirty flag to perform the action.
In my case, the button event change the value of the disabled field but couldn't force the CRM form to save and ended up finding the difference of these two.

In Conclusion: We should always use the new object model for new developments and we need to replace all the codes which used ForceSubmit with setSubmitMode if the JavaScript codes are from the upgraded CRM 4.0 system.

The parameter values for setSubmitMode function has to be one of the following 3

  • always
  • never
  • dirty

where dirty is default value for editable fields which means that the value will be submitted to the server only when that data value is changed,  never means the value will never be submitted and always means the value will always be submitted regardless of the value change. (the values for the last 2 are quite self-explanatory)
You can read more details on MSDN article.