Learning ODATA Queries

Hi,
The below link is one of the best blogs I have come across for learning ODATA queries

http://crmscape.blogspot.com/2011/03/crm-2011-odata-json-and-crm-forms.html

Please check it out.

Publishers and Changing a Custom Entity Prefix in MS CRM 2011

I think this is a great new feature in CRM 2011. Please refer to the following article that describes how to do this:
http://support.microsoft.com/kb/2500602

Changing the Font Color of a Read-Only Field in MS CRM 2011

I recently got a request to change the font color of certain fields in CRM 2011. I used the below code to achieve this:

function setColor() {
    if (document.getElementById("new_name") != null) {
        //Add the below line if the field is disabled
        document.getElementById("new_name").disabled = false;
        //The below line changes the font color
        document.getElementById("new_name").style.color = "red";
    }
}

Note: Accessing the DOM as shown above is totally unsupported. I had to use the above method since I was not able to find any supported way of achieving this functionality

Enabling JScript/JavaScript in Bulk Edit CRM 2011

I recently came across a situation where I had to use Jscript while Bulk Editing the records in CRM 2011. As we all know, this is not possible out of the box but thanks to Michael Höhne (http://www.stunnware.com/crm2/topic.aspx?id=BulkEdit) and Microsoft for providing with the KB article (http://support.microsoft.com/kb/949941)

Here is what we need to do:

1. Export the entity as a solution for which JScript needs to be enabled during Bulk Edit.
2. Open the customization.xml
3. Add BehaviorInBulkEditForm="Enabled" to the event handlers as shown below

 
<
event name="onchange" application="false" active="false" attribute="new_day" BehaviorInBulkEditForm="Enabled"
>

<
event name="onsave" application="false" active="false" BehaviorInBulkEditForm="Enabled"
>

4. Save and Import the solution. Your JScript code should now be triggered!

 --Srihari

MS CRM 2011 - Cool New Data Import Wizard

Yesterday, while trying to import some data into my dev system, I stumbled upon a cool new feature built inside the MS CRM 2011 Import Wizard - The ability to create an "Entity" and all it's attributes right during the import.
This means that I no longer have to create an entitty and then import the data into it. I can do this in one shot. Thats an AMAZING feature considering I am doing a whole bunch of mock-ups  in order to present to the client :-)

Let us walk through the steps below:

1. I have an excel sheet with some data in the following format. Let me call this "TIMETABLE"

July	Accounts	Management A/c
5	Corporate Financial reporting	Project Planning & capital budgeting
6	Corporate Restructuring	Project Planning & capital budgeting
7	Corporate Restructuring	Project Planning & capital budgeting
8	Corporate Restructuring	Project Planning & capital budgeting
9	Corporate Restructuring	Project Planning & capital budgeting
10	Consolidated Fian Stats	Project Planning & capital budgeting
11	Consolidated Fian Stats	Project Planning & capital budgeting
12	Consolidated Fian Stats	Project Planning & capital budgeting
13	Consolidated Fian Stats	Project Planning & capital budgeting
14	Consolidated Fian Stats	Leasing decisions
15	Consolidated Fian Stats	Leasing decisions
16	Consolidated Fian Stats	Leasing decisions
17	Consolidated Fian Stats	Dividend decisions
18	Consolidated Fian Stats	Dividend decisions
19	Consolidated Fian Stats	Indian capital market
20	Consolidated Fian Stats	Indian capital market
21	Consolidated Fian Stats	Indian capital market
22	Consolidated Fian Stats	Indian capital market
23	Consolidated Fian Stats	Indian capital market
24	Consolidated Fian Stats	Security Analysis
25	Consolidated Fian Stats	Security Analysis
26	Consolidated Fian Stats	Security Analysis
27	Consolidated Fian Stats	Portfolio theory
28	Consolidated Fian Stats	Portfolio theory
29	A/c & reporting of financial instruments	Portfolio theory
30	Revision	Revision
31	Revision	Revision

Now, let's use the Data Import Wizard to create and Import the Time-Table entity into CRM 2011.

2. Open CRM 2011. Click on Tools -->Import Data
3. Click on "Browse" and choose the above excel sheet which was saved in ".csv" format.

4. Click on Next.
5. Verify that the file is uploaded and click on Next again.
6. Choose the Default Automatic Mapping and click on Next

7. Now this is where the real fun begins. Choose "Create New" in the Map Record Types Screen.

8. Specify a name for this entity. In this case I call is Time Table. Click on Next

9. As you can see below, the wizard automatically recognizes the columns from the excel sheet and is asking us to specify the field names in CRM.

10. Since there are no fields yet created, I selected "Create New Field" from the dropdown list.

11. Specify the Field Name and also choose the data type for the field and then click on OK.

12. Perform the same for all the other fields that you want and then finally, click Next.

13. Verify the "Review  Mapping Summary" and click on Next.

14. Specify if you want to allow duplicates and also choose the default owner for the Imported Records. Finally click on Submit.

15. CRM will start creating the entity and the attributes for you as shown below

16. If everything is successful, it should display the screen as shown below.

17. The import job should complete successfully and after that we just need to go to the customization and customize the form and the views as per our requirement.

18. Publish the entity and voila! - we have a new Time Table entity with all the data in it.

I am going to love this feature as creating demos for customers is going to be a lot more easier from now on.

Thanks!

Passing the CRM_URL from Parent Record to Drill-Down records

Part 1:

I think most of us know that the CRM_URL parameter in the report allows us to open the CRM record directly from the reports. For those of you who do not know this, you can probably refer to this link: Using CRM_URL Report Parameter

Part 2:

The above feature will work if you are trying to open the CRM record from any main report. In my scenario, I had a main report called 'Contact Category'. Clicking on any category will drill down to another report called 'Contact Details'.  Now, when the user clicks on a particular contact's name, I wanted to open the respective CRM contact record. 

When I tried the steps in the above link, it did not work for me. On further investigation I found that, when we drill-down from a main report into a child report, the child report is opened within the context of the main report i.e. the URL displayed in the address bar will be of the main report and not of the child report. 

As a result, the report does not recognize the parameters that are declared in the child report. 

To resolve this issue, we need to pass the CRM_URL parameter from the main report to any of the child or drill-down reports. To do this, 

a. Create a new "Hidden"  report parameter called CRM_URL in the parent report.

b. Select "Allow null value" and "Allow Blank Value".

c. Now, navigate to the report properties where you specify which drill-down report to navigate to (In SSRS 2008, its Report Properties-->Action-->Go to Report-->Child report)

d. Add a new parameter that needs to be passed to the child report.

e. Select CRM_URL from under the "Name" drop down list

f. Type "=Parameters!CRM_URL.Value" for the value and click on OK.

g. Perform the steps specified in the above link for the child report.

Save and upload your reports. Now, every time you drill down to the child report, the CRM_URL parameter also gets passed from the parent report as a result, allowing you to open the respective CRM record.

Thats it!

Resizing the IFRAME in Header in CRM 2011

I was trying to add an IFRAME in the header in CRM form with a height of just 1 row. For some reason. CRM would not respect the 1 row height in the header. It would display about 6 rows always. This was very strange but after posting in the CRM Dev Forum (http://social.microsoft.com/Forums/en-US/crmdevelopment/thread/da61999c-d5e3-45a9-841c-3e09a84782d4/), it was confirmed that this seems to be a bug.
As a workaround, you an try the following code:

document.getElementById('IFRAME_Name').style.height = 35;

OR

document.getElementById("IFRAME_Test_d").parentElement.parentElement.height = 35;

Update - This issue seems to have been resolved with the Update Rollup 1 for MS CRM 2011

Making Notes non-editable after X hours

Recently, there was a requirement from my client that they wanted to ensure that the notes for all the entities be made non-editable after 8 hours from the time the note was created for a specific set of users with say "Salesperson" role.
After some thinking, I came up with the below idea.

1. I gave the "Salesperson" role only User level Write access on the Note entity.
2. Then I created the below workflow on the Note entity :

3. For demo purposes, I wait for only 1 minute after a note is created but you make it wait for as long as you need.
4. As you can see above, I check if the created by is someone other than Administrator. I then wait for a minute and then update the owner of the Note to Administrator.
5. Since a Salesperson has only User level Write access, when he tries to edit this note, he will get an error saying that he does not have sufficient privileges.

Cheers,
Srihari

Getting started with writing JScript in MS CRM 2011: Step by Step

We are just in the process on migrating to 2011 and I was just trying to write some JScripts for testing purposes. However, I found that the way of writing JScript in 2011 has changed a bit and after some trying, the below are the steps that I used to write my JScripts:

1. I created a simple JScript function as shown below in Visual Studio:

function welcome() {

    alert("Welcome to CRM 2011");
}

BTW, it looks like all the JScript in 2011 can only be accessed using functions.
2. Save this file as TestWebResource.js
3. Open CRM and go to your custom entity's customization and click on Form Properties
4. Under Form Libraries -->Click on "Add"
5. In the Look Up Record, click on the "New" Button
6. In the "Web Resource: New" window, fill the mandatory fields and then click on "Browse" -->select the JScript file that we  had saved earlier and open. Save this file(I also published it).
7. Select OK in the "Look Up Record" window
8. You should be back at the main "Form Properties" window as shown below:

9. Now, click the "Add" button under Event Handlers for the onLoad event.
10. The library will default to our jscript file. Type "welcome" as the function name and click on OK
11. Save and Publish.
12. Click on Preview-->Create Form
13. You should see the alert being displayed:

Thanks!

Error while Importing a solution in CRM 2011 - The solution file is invalid. The compressed file must contain the following files at its root: solution.xml, customizations.xml, and [Content_Types].xml

I was trying to add a button to the ribbon but I was not able to test it as I would keep getting the below error while trying to import the solution zip files:

After spending a huge amount of time checking and rechecking my code, I was finally sure that the issue had to be somewhere else.
Finally, it turned out that when I used to zip the folder using the built in windows compression(zip), it would create a folder inside the zip file and place the solution files inside this folder.

So, as the error above states, CRM is looking for those files in the "Root" but was actually finding a folder in it's place and hence throwing the error. When I selected the solution files and zippped them directly, the import was successful. I know this might be a small thing but it definitely cost me an awful lot of time. :)

Adding a button to the CRM 2011 ribbon

I found a nice article about adding a button to the CRM 2011 ribbon here:

http://gtcrm.wordpress.com/2011/01/18/quick-reference-for-adding-a-button-to-the-crm-2011-ribbon/

Invalid column name 'DeletionStateCode' CRM 2011 upgrade

I was getting the error "Invalid column name 'DeletionStateCode'" when I was trying to upgrade from 4.0 to 2011. After a lot of trials and errors I got to know that there were many stored procedures and triggers in the database which were referencing to this column. 


Since the 'DeletionStateCode' has been dropped in 2011, maybe it was erroring out while trying to upgrade them.


Solution: I deleted all the triggers and stored procs from the database as it was just a test environment and I was able to install 2011 sucessfully.

Bulk Delete/Drop Stored Procedures from Database

I had to recently clean up a database and get rid of all the custom stored procedures in it. Unfortunately, there were more than a 100 SP's in the database and it was not possible to delete them one by one. Fortunately, I found a great line of code here (http://forums.asp.net/t/1340617.aspx) that will give me a list of all the stored procedures with the 'Drop' command. I just had to copy the results from the below query, paste and execute them in the query editor window.

Select 'Drop Procedure ' + name from sys.procedures Where [type] = 'P' and is_ms_shipped = 0 and [name] not like 'sp[_]%diagram%' and name like 'usp_%'

Getting the CRM 4.0 License Key from the Database

Hi,

I found the below query that can help us to retrieve the CRM 4.0 license key from the database. Run it against the MSCRM_Config DB:

SELECT LicenseKey FROM ConfigSettings

Update

The above code does not work for CRM 2013. Please find the updated query to find the license key specific to an Organization in CRM 2013

SELECT B.NVarCharColumn 
FROM [dbo].[ConfigSettings] A , [dbo].[ConfigSettingsProperties] B
Where A.Id=b.Id
And B.ColumnName='LicenseKeyV6RTM'

Getting clean HTML from a Word document using Gmail

Recently, I had to convert a word document into an HTML file. First, like everyone else, I tried to directly save the word file as HTML web page from word.
This saved the file as HTML alright, but when I went to view the HTML source, there were just too many unnecessary tags and the HTML looked almost illegible. So I had to find other means to get this done.

After some search on the Internet, I got to know the following method which helped me get clean HTML out of my word document.
1. Send the word document as an attachment to your Gmail account.
2. Log into Gmail.

3. Open the email with this attachment and click on the "View" next to the attachment.

4. This will open up a new window/tab where Gmail tries to read your word document.
5. On this page click on View->Plain HTML
6. Now you should see the same data without all the formatting as just plain text.
7. Right Click on this Page->View Source to get the HTML portion of this page.
8. The HTML you have right now will be very clean and you can easily work with it from here on.

That's it!

Using CRM Attribute Types

The following are the default values to be set for the different attribute types as defined in the SDK.

// Set up the CRM Service.
CrmAuthenticationToken token = new CrmAuthenticationToken();
token.AuthenticationType = 0; 
token.OrganizationName = "AdventureWorksCycle";
 
CrmService service = new CrmService();
service.Url = "http://crm:5555/mscrmservices/2007/crmservice.asmx";
service.CrmAuthenticationTokenValue = token;
service.Credentials = System.Net.CredentialCache.DefaultCredentials;

CrmBoolean boolean = new CrmBoolean();
boolean.Value = true;

CrmDateTime dateTime = new CrmDateTime();
dateTime.Value = "2006/5/27T17:00:00";

CrmDecimal dec = new CrmDecimal();
dec.Value = (decimal)10.1;

CrmFloat f = new CrmFloat();
f.Value = (float)10.1;

CrmMoney money = new CrmMoney();
money.Value = (decimal)10.00;

CrmNumber number = new CrmNumber();
number.Value = 10;

Lookup lookup = new Lookup();
lookup.Value = user.UserId;
lookup.type = EntityName.systemuser.ToString();

Owner owner = new Owner();
owner.type = EntityName.systemuser.ToString();
owner.Value = user.UserId;

Picklist list = new Picklist();
list.Value = 1;

Status status = new Status();
status.Value = 1;

EntityNameReference reference = new EntityNameReference();
reference.Value = EntityName.systemuser.ToString();

Key key = new Key();
key.Value = user.UserId;

PostCreate/UpdatePlugin

Here, when a custom entity is created or updated, I am updating a varchar field in the related contact entity

public void Execute(IPluginExecutionContext context)
        {
            DynamicEntity entity=null;

            //Check if the input parameters contains a target of type Dynamic Entity
            if (context.InputParameters.Properties.Contains("Target") &&
                context.InputParameters.Properties["Target"] is DynamicEntity)
            {
                //obtain the entity from the input parameters
                entity = (DynamicEntity)context.InputParameters.Properties["Target"];

                //TextWriter log1 = TextWriter.Synchronized(File.AppendText(@"D:\Srihari Backup\Log.txt"));
                if (entity.Name == "new_testEntity")
                {
                    
                    try
                    {
                        //log1.WriteLine("inside try");                       

                        
                        DynamicEntity testEntity= (DynamicEntity)context.InputParameters[ParameterName.Target];
                        string clinicName = String.Empty;
                        string contactId= String.Empty;

                        if (testEntity.Properties.Contains("new_clinicname"))
                        {
                            clinicName = (string)testEntity["new_clinicname"];
                            //log1.WriteLine(clinicName);
                        }
                        if (context.MessageName == MessageName.Create)
                        {
                            if (testEntity.Properties.Contains("new_contactid"))
                            {
                                //log1.WriteLine("contains patient id");                                

              //Since the contact is a lookup, cast the object as a lookup.
                                //log1.WriteLine("create message :-");
                                Lookup contactLookup = (Lookup)testEntity["new_contactid"];
                                contactLookup.type = EntityName.contact.ToString();
                                contactId= contactLookup.Value.ToString(); //get the guid of the lookup   
                            }                   
                           
                            
                        }
                        else if (context.MessageName == MessageName.Update)
                        {
                            //log1.WriteLine("update message :-");
//Since this is an update, only the values updated on the form will be available in the context. Hence I am getting the value from an Image that I have registered.
                            DynamicEntity postTestEntity = (DynamicEntity)context.PostEntityImages["contactId"];//The name or 'entityalias' of the image
                            //log1.WriteLine(postCCProvider);
                            Lookup contactLookup = (Lookup)postTestEntity.Properties["new_contactid"];
                            //log1.WriteLine(patientLookup);
                            contactId = contactLookup.Value.ToString();
                            //log1.WriteLine(patientId);

                            //log1.Close();
                        }
                        
                        DynamicEntity contact = new DynamicEntity();
                        contact.Name = EntityName.contact.ToString();

                        Key contactKey = new Key();
                        contactKey.Value = new Guid(contactId);

                        KeyProperty contactGuid = new KeyProperty();
                        contactGuid.Name = "contactid";                 
                        contactGuid.Value = contactKey;
                        contact.Properties.Add(contactGuid);

                        //set the field that needs to be updated in the contact entity.
                        StringProperty ccProviderNameProperty = new StringProperty("new_communitycareprovider", clinicName);
                        contact.Properties.Add(ccProviderNameProperty);

                        ICrmService service = context.CreateCrmService(true);
                        service.Update(contact);


                        
                    }
                    catch (SoapException soapEx)
                    {
                        //log1.Close();
                        throw new InvalidPluginExecutionException("Soap error occured in the plugin", soapEx);                        
                        
                    }
                    catch (Exception ex)
                    {
                        //log1.WriteLine(ex.Message);
                        //log1.Close();
                        throw new InvalidPluginExecutionException("An error occured in the plugin", ex);
                    }
                }
            }
            else
            {
                return; 
            }

           
        }

Creating a document library in Sharepoint through code.

Recently there was a requirement to create a document library in Sharepoint from an ASP.NET page. I used the following code to achive this:

1. Add a reference to the sharepoint list service web service.

http:///_vti_bin/Lists.asmx

2. I then used the List webservice to create the document library using the code below

Web_Reference_Folder.Lists listService = new Web_Reference_Folder.Lists();
listService.Credentials = new System.Net.NetworkCredential("username", "password");
XmlNode ndList = listService.AddList("document library name", "Type any description for your document library", 101);

"101" in the above code is used to create a document library.
Thats it! 
 –Srihari.

Searching for a file/doc in a document library in Sharepoint

//Call my document library’s listservice webservice 
Sharepoint_WebService.Lists listService = new Sharepoint_WebService.Lists();
                            listService.Credentials = new System.Net.NetworkCredential("username", "password");
                            //get the entire list items from the specified document library
                            XmlNode ndList = listService.GetListItems(accountName, null, null, null, null, null, null);
                            XmlNamespaceManager nsMgr = new XmlNamespaceManager(ndList.OwnerDocument.NameTable);
                            nsMgr.AddNamespace("rs"”, "urn:schemas-microsoft-com:rowset");
                            nsMgr.AddNamespace("z", "#RowsetSchema");
                            //if the document library is empty
                            if (ndList != null)
                            {
                                XmlNode dataNode = ndList.SelectSingleNode("rs:data", nsMgr);
                                int itemCount = Convert.ToInt32(dataNode.Attributes.GetNamedItem("ItemCount").Value);
                                if (itemCount == 0)
                                {
                                    MessageBox.Show("No file to search");                               
                               }
                                else
                                {
                                    //get the entire items in the document library
                                    foreach (XmlNode row in dataNode.SelectNodes("z:row", nsMgr))
                                    {
                                        //if one of the item equals "Group Verification.docx"
                                        if ((row.Attributes.GetNamedItem("ows_LinkFilename").Value).ToString() == "Group Verification.docx")
                                        {
                                            MessageBox.Show("File Found");
                                        }
                                            //no verification document has been uploaded
                                        else
                                        {
                                            MessageBox.Show("File not found");                                       
                                        }
                                    }
                                }
                            }

Points about Pre-update plugin

1. A pre-update plugin can be used to update the field values of the same record/entity.
2. Only the values that we are changing in the entity form will be available in the entity context.
3. If we want to access the values of fields that we are not going to update, then we need to register the image for that entity and include the attributes whose value we need.
4. Finally, we need to directly update the entity context for the changes to get reflected.

Note: We cannot do a service.update(entity) as this will cause the code to go in a circular loop and finally the plugin will throw an error and the changes will not get saved!

Code Sample:

//Updating a boolean value to true or false using Pre-update
// Obtain the target business entity from the input parameters.
entity = (DynamicEntity)context.InputParameters.Properties["Target"];
//Instantiating the boolean property and passing the boolen field name and value to the property
CrmBooleanProperty boolProp = new CrmBooleanProperty("new_verified", new CrmBoolean(false));
//Adding the Property to the entity properties
entity.Properties.Add(boolProp);

-Srihari.

Determining if an Account is a part of a DitributeCampaignActivity

Scenario: Given an account GUID, I should be able to determine if the account is a part of a  "Distribute  Campaign Activity"

The functional flow of the data would be CampaignCampaign ActivitiesCampaign Activity ItemTarget Marketing ListsMarketing List MembersAccounts

 I was trying to look into the database for my answers and before reaching the solution, the following are my findings:
1.       List and ListMember are the tables that contain the data about a marketing list
2.       Similarly, Campaign and CampaignActivity tables contain information about the Campaign records.
3.       Intersect Tables: When there is a many-to-many relationship between two entities, an intersect table is created. This is true for both system relationships built in to the product as well as custom many-to-many relationships. This is how we used to create M:M relationships in CRM 3.0 but it’s implemented OOB in CRM 4.0(See “Using Intersect Tables” in SDK for more info)
4.       The Intersect Table between List and CampaignActivity isCampaignActivityItem.
5.       There is also a ‘DistributeCampaignActivity’ message using which we can distribute a campaign programmatically. However, we cannot register a plugin against this message as it’s not supported.
6.       So, although I was getting all the info individually, I was still not able to determine if an account was a part of a DistributeCampaignActivity.
7.       Then I found that all the plugin registration messages are stored in theSdkMessageBase and the SdkMessageFilterbase tables and there was a‘DistributeCampaignActivity’ message in the table but it was not exposed.
8.       So, I used the below query  in order to expose the‘DistributeCampaignActivity’ message in the plugin registration tool.(Unsupported)

update sdkmessagefilterbase
set isCustomProcessingStepAllowed=1
where sdkmessageid=(select sdkmessageid from sdkmessagebase where name='DistributeCampaignActivity')

9.       I then registered a post-update plugin with message name‘DistributeCampaignActivity’ and Primary Entity: CampaignActivity10.   This time, when I clicked on the ‘DistributeCampaignActivity’ button for a Campaign, I was able to debug the plugin and find out that it was creating a record of type BulkOperationID11.   Naturally, I checked and found that there were 2 tables:  BulkOperation andBulkOperationLog and an entry was made into these tables ONLY when the record was of type “Distribute” or “QuickCampaign“.
12.   Finally, I was able to arrive at the below query which tells me if an account is a part of a ‘DistributeCampaignActivity’ or a QuickCampaign created in the last 7 days based on the account GUID: 

select COUNT(1) 
from FilteredBulkOperation FBO, FilteredBulkOperationLog FLOG 
where FBO.activityid=FLOG.bulkoperationid 
and FBO.actualend >= GETDATE()-7 and actualend<= GETDATE()
and FLOG.regardingobjectid ='20C7A5F8-AD02-DE11-83DE-0003FFE51F61'  /*–The Account GUID–*/

Thats it!