CRM 2011 user security roles listing

This SQL script will give you an result of all active users within the CRM deployment and their security roles

select

u.fullname, u.internalemailaddress, r.name as 'Security Role' from FilteredSystemUserRoles AS sr
inner

join FilteredRole as r on sr.roleid = r.roleid
inner

join FilteredSystemUser as u on u.systemuserid = sr.systemuserid
where

u.isdisabled = 0
order

by u.fullname

Enjoy!

Using Entity Framework to create or update database(Migration)

Once you define your object model in C# or VB, you can push the object to your database. Most of the case entity framework will handle the update automatically. And you dont even need to write a SQL code.
This development method also commonly know as "Code First" in Entity Framework.

However I searched numbers of blog posts and found not a lot of them mention the command to generate and update database. And here are the procedures to create or update your databases:

1. Develop your object in visual studio
2. Make sure your connection is correct and the account have permission to crate and update
3. Open package manager console (view > other windows >  package manager console)
4. Enable automatic migration if you haven't by "PM> Enable-Migrations -EnableAutomaticMigrations"
5. Update database command "PM> Update-Database"

Thanks Travis in providing these procedure.

Update status in CRM 2011

Here is the code to update a recode via CRM context object:

CRMContext.Execute(new SetStateRequest
            {
                EntityMoniker = new EntityReference
                {
                    Id = new Guid(""),
                    LogicalName = "" //Entity Name
                },
                State = new OptionSetValue(1), //get the value
                Status = new OptionSetValue(1)
            }
            );

Select top N from group

This is a simple SQL select query to illustrate how to select top (or bottom) N from each group.

Here is the data structure

Table 1: product

Prodcut_ID	Product_Name
1	Apple
2	Orange
3	Banana
4	Mango

Table 2: Review

Review_ID	Product_FK	Score
1	1	9
2	4	3
3	3	8
4	2	9
5	2	7
6	3	6
7	2	8
8	1	6
9	2	6
10	3	7
11	4	8
12	4	5

SQL query to select top review score from each product

SELECT P.Product_Name, R.Score AS 'Top Score' FROM Product AS P

LEFT JOIN Review AS R ON R.Product_FK = P.Prodcut_ID

AND R.Review_ID =

      (SELECT TOP 1 R2.Review_ID FROM Review AS R2

      WHERE R2.Product_FK = P.Prodcut_ID

      ORDER BY R2.Score DESC)

Here is the select result:

Product_Name	Top Score
Apple	9
Orange	9
Banana	8
Mango	8

You can also use the same query to get average or do other calculations

The context is not currently tracking the entity

I encountered this error when I deploy a plug-in to CRM online environment. I tested the same plug-in in on premises environment and it works without any error.

The plugin read and write to entity A and entity B (they have no relationships). The plug-in is triggered when entity A records are created and it retrieve all records from entity B and process them.

The error was thrown when it tries to save changes to entity B, with this error message: The context is not currently tracking the <<entity B>> entity

It suggested the services context does not track <<entity B>> in the scope. I researched this topic and found out I need to use OrganizationServiceContext.Attach Method. However when I use this method it returns with this error: The context is already tracking the <<entity B>> entity. :(

And did more research and found that it actually need to detach the object ( OrganizationServiceContext.Detach Method) before it can be attached.

This makes no sense to me but this is the work around.

CRM 2011 Plug-in error: Object reference not set to an instance of an object.

Problem/Error:

When I create an instance of object from the sdk generated code by using the default constructor like:

email.regardingobjectid = new CrmEntityReference();

I will find it return a null object:

This is not the result I wanted because the object does not have correct structure and when I try to assign a value to the object (in this case is regardingobjectid) it will throw a NullReferenceException error like this:

Solution

The solution is relatively simple by using a different constructor and it will give a correct object:

email.regardingobjectid = new CrmEntityReference("contact", contact.contactid);

CRM Email router time out

This summary is not available. Please click here to view the post.