Monday 30 June 2014

CRM 2011/2013 Filter ‘Look For’ Lookup View Entity Field Activity Party

Code:

function preFilterLookup() {    

Xrm.Page.getControl("to").addPreSearch(function () {

    addLookupFilter();

  });

}

function addLookupFilter() {

//to lock only for System User
 fetchXml = "<filter type='and'><condition attribute='isdisabled' operator='eq' uitype='systemuser' value='0' /></filter>";
 document.getElementById("to_i").setAttribute("defaulttype", "8");
    document.getElementById("to_i").setAttribute("lookuptypes", "8");
    document.getElementById("to_i").setAttribute("lookuptypeIcons", "/_imgs/ico_16_8.gif");
document.getElementById("to_i").disableViewPicker = 1;
 Xrm.Page.getControl("to").addCustomFilter(fetchXml);

}

 Result:
image

image

Hope it helps!

Set Default Price List in the Opportunity Dynamics CRM 2011/2013 [without a Piece of Code]

In My Previous Post, I explain about setting Default Price List in the Opportunity using Javascript and without any hard-code.

Now, I am trying to Set the Default Price List in the Opportunity Form without a line of code.
And thanks to Business Rules, that actually you can achieve this!

1. Create a Business Rule under Opportunity Form

2. Set a Condition:

image

* This condition is necessary to lock the Default Price and to not overwrite the existing Price List, for example, user did not use the selected Default Price List, but he/she used another Price List, so it will not overwrite for existing Opportunity.

3. Set an Action:

image

4. Here is your Business Rule should be:

image

5. Do not forget to Save and Activate:

image

6. And here is the result at the very first time create a New Opportunity:

image

NB: This method is can be used to several purposes, not limited to set the Default Price List.

Enjoying you life with CRM-ing!

CRM 2011/ 2013 Modify CreatedOn, CreatedBy, ModifiedOn, and ModifiedBy Using SDK C#

Often, we have requirements to import data from external data source to CRM and to keep the history, we should keep on eye for these following fields:

1. CreatedOn
2. CreatedBy,
3. ModifiedOn,
4. ModifiedBy

And we often think to update the database directly, yes, it is actually possible.

But, beside it is unsupported way, it is only applicable for those who implement CRM OnPremise, but in fact, we also have another CRM Online.

So, to update those fields using SDK, you can use these code lines:

TargetEntity["createdon"] = new DateTime(2011, 8, 8);
TargetEntity["overriddencreatedon"] = new DateTime(2011, 8, 8);
TargetEntity["createdby"] = new EntityReference("systemuser", new Guid("DDF26A49-4CBA-E311-9400-001CC4EECDD6"));
TargetEntity["modifiedby"] = new EntityReference("systemuser", new Guid("F1F26A49-4CBA-E311-9400-001CC4EECDD6"));
TargetEntity["modifiedon"] = new DateTime(2012, 8, 8);

Do not forget to change to your dynamic values for those assigned field values.

*You should use Late Bound since you cannot use Early Bound:

SNAGHTML1427c435

It will give error says “it is read only”;
And here is the result:

image

 image

Hope it helps!

Sunday 29 June 2014

CRM 2011/ 2013 SQL Query When Debugging Plugin

When you are debugging a plugin, then you want to do SQL Query, you will realize that you will find a deadlock and the SQL will be keeping executing and executing.

image

After you stop or finish your debugging, it will give you the result, but during the debugging, those queries would be keeping executing, executing, and executing.

To make it enable to you to query during plugin debugging, please add with (nolock) syntax in your Query after the from table syntax

For example:
select * from Opportunity with (nolock)

And here is the result:

image

And it is also help you to see the record that has been created, even though you are debugging in the PostCreate Plugin event.

image

When you see in the SQL,
it is actually you can see the record has been created after debugging completed:

SNAGHTML13556bc2

Hope it helps!

CRM 2011/ 2013 Add Security Role to Team Programmatically C#

To assign security to a Team in CRM 2011 or CRM 2013, you can use this code:

public void AddSecurityRoletoTeam(IOrganizationService service, Guid securityRoleId, Guid teamId)
{
     service.Associate(Team.EntityLogicalName,
     teamId,
     new Relationship("teamroles_association"),
     new EntityReferenceCollection() { new EntityReference(Role.EntityLogicalName, securityRoleId) });
 }

*Both Team and Security Role should be in the same Business Unit

Hope it helps!

CRM 2011/2013 Share and Un-Share Records Programmatically C#

Please refer to these codes, to Share Records, Share Record only for Read, and Unshare Records.
public void ShareRecord(IOrganizationService service, Entity TargetEntity, Entity TargetShare)
{
    //no delete access
    GrantAccessRequest grant = new GrantAccessRequest();
    grant.Target = new EntityReference(TargetEntity.LogicalName, TargetEntity.Id);

    PrincipalAccess principal = new PrincipalAccess();
    principal.Principal = new EntityReference(TargetShare.LogicalName, TargetShare.Id);
    principal.AccessMask = AccessRights.ReadAccess | AccessRights.AppendAccess | AccessRights.WriteAccess | AccessRights.AppendToAccess | AccessRights.ShareAccess | AccessRights.AssignAccess;
    grant.PrincipalAccess = principal;

    try
    {
        GrantAccessResponse grant_response = (GrantAccessResponse)service.Execute(grant);
    }
    catch (Exception ex)
    {
        throw ex;
    }
}
public void ShareRecordReadOnly(IOrganizationService service, Entity TargetEntity, Entity TargetShare)
{
    //no delete access
    GrantAccessRequest grant = new GrantAccessRequest();
    grant.Target = new EntityReference(TargetEntity.LogicalName, TargetEntity.Id);

    PrincipalAccess principal = new PrincipalAccess();
    principal.Principal = new EntityReference(TargetShare.LogicalName, TargetShare.Id);
    principal.AccessMask = AccessRights.ReadAccess;
    grant.PrincipalAccess = principal;

    try
    {
        GrantAccessResponse grant_response = (GrantAccessResponse)service.Execute(grant);
    }
    catch (Exception ex)
    {
        throw ex;
    }
 }
public void UnShareRecord(IOrganizationService service, Entity TargetEntity, Entity TargetShare)
{
    //no delete access
    ModifyAccessRequest modif = new ModifyAccessRequest(); ;
    modif.Target = new EntityReference(TargetEntity.LogicalName, TargetEntity.Id);

    PrincipalAccess principal = new PrincipalAccess();
    principal.Principal = new EntityReference(TargetShare.LogicalName, TargetShare.Id);
    principal.AccessMask = AccessRights.None;
    modif.PrincipalAccess = principal;

    try
    {
        ModifyAccessResponse modif_response = (ModifyAccessResponse)service.Execute(modif); ;
            }
    catch (Exception ex)
    {
        throw ex;
    }
}

CRM 2011/2013 Register Assembly for Offline Plugin (for Outlook) using .bat file

In this link:
http://msdn.microsoft.com/en-us/library/gg309582.aspx

We know that to make the plugin assembly we should register the plugin key in the AllowList reg key:

Add an AllowList key
  1. Run the registry editor by selecting Start, then select Run and type regedit.exe.
  2. In the tree view pane, navigate to the AllowList key. The complete path of the key is HKEY_CURRENT_USER\Software\Microsoft\MSCRMClient\AllowList.
  3. Select the AllowList key and right click to display the context menu.
  4. Select New then click Key to create a new sub-key.
  5. Paste the public key token value into the name of the new sub-key.
  6. Close the registry editor.
Now, imagine that you should register more than 5 plugin key to the AllowList registry key to more than 10 PCs.

To make it easier and faster, you can create .bat file and just let the user to run it.
This is the format of the key:

Regedit Plugin Offline

“REG ADD HKYE_CURRENT_USER\Software\Microsoft\MSCRMClient\AllowList\[publickey] /F”

Hope it helps!

Thursday 26 June 2014

Import Multiple Images from Physical Directory to CRM 2013 Entity Image field Using SSIS + KingswaySoft SSIS Connector

As we know that in CRM 2013, there is a new field type: Image type, called as entityimage.

In CRM 2011, I had a project to show image in the Contact Form, and yes, I usually use IFrame to show the Image and the image is stored physically in the File Server.

*In CRM 2011

My Calon UL Create New - 2

*In CRM 2013

Contoh dr

Now, the company wants to show the Image in entityimage field in CRM 2011, how to achieve this?

1. Manual Import (assuming you have million data and it is increasing since the company still maintain two system, then need more people to do it Smile)

2. Using custom code + SDK to read file from physical folder then update the contact entity image programmatically.

3. Using SSIS + KingswaySoft Data Connector.
(Luckily,I was checking to KingswaySoft Website, and it is clearly, it supports the entityimage field)
This time, I will not talking about point no 1 and 2, I want to talk about point 3, which is according to me, it is the best and the fastest way!

So, here is my scenario as example.

To make this example real and more interesting, and this is the moment of World Class Football Party, I have 3 Contacts of Germany greatest footballer in CRM 2013 as Customer.

1. Thomas Mueller
Contact1

2. Mesut Ozil
Contact2

3. Miroslav Klose
Contact3

I want to add Manuel Neuer as well, but okay, I decide to only use 3 samples.
Those 3 contacts are currently having no photo, let’s give picture to them!

Then, here we go.
1. First of all, preparing your image folder (back to client requirement, in CRM 2011, we stored the image physically under file server folders)

image

2. Step number 2, create a flat file, it can be .csv or .txt

With format:
[ImagePath],[contactid]
This contactid is the CRM Contact Id as destination
(In this case, I stored contact guid, you can use Text as well)

Database source find contact

So, here is your file looks like:

C:\Installer\Aileen\Image\MesutOzil.png,07D31879-41FD-E311-943B-001CC4EECDD6
C:\Installer\Aileen\Image\ThomasMueller.png,E36D306A-41FD-E311-943B-001CC4EECDD6
C:\Installer\Aileen\Image\MiroslavKlose.png,A26EB788-41FD-E311-943B-001CC4EECDD6

Save your file as imagelist.txt

image

3. Then, create the SSIS Project
You can refer to this link:http://msdn.microsoft.com/en-us/library/ms170419.aspx
There are millions tutorial published in the internet.

4. Then, you create a Data Flow Task

5. You need 3 components:
- Flat File Source
- Import Column
- Dynamics CRM Destination (this is SSIS Data Flow Component that you can get after installing Kingswaysoft)

image

6. Okay, I go through 1 by one for each control.

* Flat File Source
Create a new Flat File Connection Manager, browse your file.

FlatSource1

Go to the Columns

FlatSource2

Go to Advanced and rename your columns

Column0 to ImageParh
Column1 to ContactId

Then, here is the preview

FlatSource7

* Drag green connector from Flat File Source to Import Column

*Import Column

Go to Input Columns

ImportColumnNew1

Go to Input and Output Properties, add the Column Output, rename to ImageFile, and please remember the ID = 24

ImportColumnNew2

Go to the Input Column, Image Path, and fill the FileDataColumnID to 24

ImportColumnNew3

Next, again, drag the green connector to CRM Destination

*CRM Destination

Create new Connection Manager, then Configure the Microsoft Dynamics CRM Destination
Action: Update
Destination Entity: contact

CRM Des1

Go to Column, and then map the input and output columns.
Map ImageFile to entityimage
and ContactId to contactid

*NB: KingswaySoft can support Text Lookup, so if in case you don’t use guid as reference, you can use email or fullname (if unique) as well, but in this case, I’ve had used contactid, and lazy to revert back, and actually I store contact id in the image properties of my client folder.

Then, here is the magic, using this Connector to Map your Image file to CRM entityimage.

CRM Des Final

Execute the Task for testing purpose, later for batch job, you can deploy the SSIS Packages as well.

DataFlowTaskFinal Result

Ah yes, 3 rows have been executed successfully.
And here is the result

Before After
Contact1 Thomas Mueller Result

Contact2 MesutOzil Result

Contact3 Miroslav Klose Result

As you can see, now the images have been copied to entityimage of Contact, it did not irk the developer to do it and did not waste the time.

Easy, Faster, and Comfortable.

This combination (SSIS + KingswaySoft) helps me immensely.

And hopefully my blog post is helpful for everyone!

CRM 2011/2013 SQL Query to Retrieve Users assigned to Field Level Security Profile

Here is the SQL Query to retrieve list of users under Field Level Security Profile:

In this example is “System Administrator”

select * from SystemUser
where SystemUserId in
(
    select SystemUserId from SystemUserProfiles
    where FieldSecurityProfileId in 
    (
        select FieldSecurityProfileId from FieldSecurityProfile
        where Description = 'System Administrator'
    )
)

Run the Test:

From UI:

image

From SQL Database Result:

image

Hope it helps!

Wednesday 25 June 2014

CRM 2011/ CRM 2013 Set Default Chart–Chart Pane

I have heard that there are many requirements about setting default chart in Chart Pane.

Chart in CRM is actually stored in “SavedQueryVisualization” entity, same concept with View, is stored in SavedQuery entity.

Well, if we take a look in the database, actually, it has a field: “isdefault”, again, same concept with View, but View, we can manipulate easily through the UI, set the default view. But, Chart, we cannot do it through UI.
Okay, we go to the resolution.

1. If you are using CRM OnPremise, well, you can achieve it using Database Update syntax (unsupported, but yes, it worked).

Okay, I want the user to have Default Chart for Account.
Currently, we have 4 charts:

image

I would like to set “Accounts by Owner” as Account default chart when the user first time accessing CRM and see Account Chart in the Chart Pane.

As I mentioned above, there is a field, it indicates the default one.

image

And if we take a look, there are 4 charts that by default set as isDefault chart, and when we go to the Goal, for example, for the first time, we will always see the default Chart: Percentage Achieved

image

And it is also applying the same times for lead (Leads by Rating).

image

First of all, you have to know the savedqueryvisualization Id

select SavedQueryVisualizationId, Name, IsDefault from SavedQueryVisualization
where PrimaryEntityTypeCode = 1
and Name = 'Accounts by Owner'

Then, you can use this syntax to update the database, remember, this is modify the database

Update SavedQueryVisualization
set IsDefault = 1
where SavedQueryVisualizationId = 'A3A9EE47-5093-DE11-97D4-00155DA3B01E'

Change the SavedQueryVisualizationId based on your Chart ID.

Then, here is the result:

image

In the CRM:

User will see “Accounts by Owner” as the default chart of Account.

image

2. If you are not using CRM OnPremise, you are CRM Online User, or you are not comfortable with the Database Update, or you want to something more dynamic, ah yes, you can achieve it using plugin.

Same as the first one, I want to set the default chart for Account entity, that is now I want set “New Accounts By Month” as the default chart.

Here is the code.

//Change the variable to your Entity code and Name.
For the entity code, you can find in the:

https://www.google.com.my/webhp?sourceid=chrome-instant&ion=1&espv=2&ie=UTF-8#q=entity%20type%20code%20crm%202013

There are plenty of example, through Tool, Database, MSDN Reference, Custom code, etc.

public class SavedQueryVisualizationPlugin : IPlugin
    {
        public void Execute(IServiceProvider serviceProvider)
        {
            #region must to have

            IPluginExecutionContext context = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));

            IOrganizationServiceFactory serviceFactory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));

            // Create service with context of current user
            IOrganizationService service = serviceFactory.CreateOrganizationService(context.UserId);


            //create tracing service
            ITracingService tracingService = (ITracingService)serviceProvider.GetService(typeof(ITracingService));

            #endregion

            #region variables
            int intReturnTypeCode = 0;
            string strDefaultChart = string.Empty;
            EntityCollection ecVisualization = new EntityCollection();

            #endregion

            try
            {
                if (context.InputParameters.Contains("Query") && context.InputParameters["Query"] is QueryExpression)
                {
                    QueryExpression qe = (QueryExpression)context.InputParameters["Query"];

                    if (qe.EntityName == "savedqueryvisualization")
                    {
                        if (qe.Criteria != null)
                        {
                            if (qe.Criteria.Conditions != null)
                            {
                                for (int i = qe.Criteria.Conditions.Count - 1; i > -1; i--)
                                {
                                    if (qe.Criteria.Conditions[i].AttributeName == "primaryentitytypecode")
                                    {
                                        intReturnTypeCode = Convert.ToInt32(qe.Criteria.Conditions[i].Values[0]);
                                        if (intReturnTypeCode == 1) // Account, 2 = Contact, etc based on the entity type code
                                        {
                                            //Change this to your chart name
                                            strDefaultChart = "New Accounts By Month"; //

                                            if (context.OutputParameters != null)
                                            {
                                                if (context.OutputParameters["BusinessEntityCollection"] != null)
                                                {
                                                    ecVisualization = (EntityCollection)context.OutputParameters["BusinessEntityCollection"];

                                                    //change the default chart
                                                    ecVisualization = ChangeDefaultChart(strDefaultChart, ecVisualization);

                                                    //set the context OutputParameters
                                                    context.OutputParameters["BusinessEntityCollection"] = ecVisualization;
                                                    // Name of the view you want to set as Default
                                                }
                                            }
                                        }
                                        if (intReturnTypeCode == 9600) // Goal
                                        {
                                            //Change this to your chart name
                                            strDefaultChart = "Goal Progress (Money)"; //

                                            if (context.OutputParameters != null)
                                            {
                                                if (context.OutputParameters["BusinessEntityCollection"] != null)
                                                {
                                                    ecVisualization = (EntityCollection)context.OutputParameters["BusinessEntityCollection"];

                                                    //change the default chart
                                                    ecVisualization = ChangeDefaultChart(strDefaultChart, ecVisualization);

                                                    //set the context OutputParameters
                                                    context.OutputParameters["BusinessEntityCollection"] = ecVisualization;
                                                    // Name of the view you want to set as Default
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                        }

                    }
                }
            }
            catch
            {
                //to prevent unexpected error;
            }
        }

        private EntityCollection ChangeDefaultChart(string strMyDefaultChart, EntityCollection ecVisualization)
        {
            foreach (Entity entity in ecVisualization.Entities)
            {
                string strChartNameVar = (string)entity["name"];
                //play with isdefault field
                if (strChartNameVar.Equals(strMyDefaultChart))
                {
                    entity["isdefault"] = new bool();
                    entity["isdefault"] = true;
                }

                else
                {
                    entity["isdefault"] = new bool();
                    entity["isdefault"] = false;
                }

            }

            return ecVisualization;

        }
    }

Then, register your plugin:

Register Plugin

Message: RetrieveMultiple

Primary Entity: savedqueryvisualization
Event: Post-operation
Execution Mode: Synchronous

With that code, when the very first time, the user go to Account, will see the default chart same as what you did set in the plugin, no matter what.

Result

Info: The plugin will overwrite the default chart, will win against the one which is defaulted in Database.

With Plugin, you can do improvement to achieve the requirement, for example, you want dynamic setting, or you want to set not only for account, just adding if condition after the Account condition one.

if (intReturnTypeCode == 1) // Account, 2 = Contact, etc based on the entity type code
{
    //Change this to your chart name
    strDefaultChart = "New Accounts By Month"; //

    if (context.OutputParameters["BusinessEntityCollection"] != null)
    {                                           
        ecVisualization = (EntityCollection)context.OutputParameters["BusinessEntityCollection"];

        //change the default chart
        ecVisualization = ChangeDefaultChart(strDefaultChart, ecVisualization);

        //set the context OutputParameters
        context.OutputParameters["BusinessEntityCollection"] = ecVisualization;
        // Name of the view you want to set as Default
    }
}
if (intReturnTypeCode == 9600) // Goal
{
    //Change this to your chart name
    strDefaultChart = "Goal Progress (Money)"; //

    if (context.OutputParameters["BusinessEntityCollection"] != null)
    {
        ecVisualization = (EntityCollection)context.OutputParameters["BusinessEntityCollection"];

        //change the default chart
        ecVisualization = ChangeDefaultChart(strDefaultChart, ecVisualization);

        //set the context OutputParameters
        context.OutputParameters["BusinessEntityCollection"] = ecVisualization;
        // Name of the view you want to set as Default
    }
}

And here is the result:

image

*NB: The default chart will appear at the very first time user login and enter into the CRM System, when the user select another chart and then go somewhere, then go back, the default will not be set anymore, it will still remain to the last state of the chart selected by User, this is the concept of pick up where you left off.

Hope it helps!