Tuesday 31 May 2016

Modify CRM View Query or Filter Criteria on Demand Dynamically using Plugin Retrieve

Hi guys,

Just want to share how we can modify the View Filter Criteria/Condition dynamically.

Introduction

Recently I have requirement that I need to create a View in CRM that there is no way to construct it from Advanced Find and make it dynamic.

For example,
I need to have query that:
1. The Date parameter is must be changed accodingly based on the today date and
2. Another parameter must get the value from a Configuration Entity
Ok, for number 1, we can force the users to always go to the View and change the date accordingly, but how about number 2?

So, my use case:

Show me the list of Customers that due date is in the next 21 days?
Meanwhile, 21 here is must be dynamically obtained from another Configuration entity.

So, this is nearly impossible, because even we use Advanced Find there is no way to use variable as parameter in the filter criteria that we need to query from another entity that is NOT related at all!
As we know that Advanced Find Query, only has limited operator and also it cannot Query from another entity, only limited to the related entities.

Use Case

So, to go to the code, I need to tell the scenario first, and to make it simpler, I just use this use Case:
I want to get all Active Customers that birthday is this Month.

This month is May for example.
So, I have a custom field = Birthday Month

Which I have auto-populated this field before with the Date Birth component (in another plugin).
So, now I need to always update the View to always Query to the:

Status = Active and Birthday Month = 5

And for the Next Month (June) should be:
Status = Active and Birthday Month = 6

Which last Month for April
Status = Active and Birthday Month = 4

Those number 5, 6, and 4 are supposed to be generated dynamically.

And I can’t use the This-Month operator because I dont have May 2016 Data, since I save the DOB, not updating every customer BOD every year plus 1 year.

You can also add another requirement, such as must be a Member Customer with Annual Income more than X, which X is you taken from Configuration entity.

What you need is just a new fetch XML or Query Expression that you can just convert use this request!


QueryExpressionToFetchXmlRequest req = new QueryExpressionToFetchXmlRequest();
req.Query = qenew;
QueryExpressionToFetchXmlResponse resp = (QueryExpressionToFetchXmlResponse)service.Execute(req);

But, for this use case, my point is jut to point out to you how to modify the Query just as per you wish (sorry as per Users’ whish Smile)

The Current View

image

So, for the post here, I just need to replace the Query from the Existing View (in your case, you might need to create a new View)

image

Then I got the savedqueryid from this view

The Code

public void Execute(IServiceProvider serviceProvider)

{
            #region must 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


            if (context.OutputParameters.Contains("BusinessEntity"))
            {
                var retrievedResult = (Entity)context.OutputParameters["BusinessEntity"];
                Entity entity = retrievedResult;
                string fetch = string.Empty;


                //the below GUID, you can do advance query to make this as non-hardcoded one, you can find by name as well, for example
                //just for my use case here, I post here as final GUID of the 'Active Customer' view GUID
                //in ACTUAL case, do not use harcoded one because it will change in different environment unless for Out of The Box Entities View
                if ((Guid)entity["savedqueryid"] == new Guid("00000000-0000-0000-00AA-000010001004")) 
                {
                    QueryExpression qenew = new QueryExpression("contact");


                    ConditionExpression activeCon = new ConditionExpression()
                    {
                        AttributeName = "statecode",
                        Operator = ConditionOperator.Equal,
                        Values = { 1 }
                    };


                    int currentMonth = DateTime.Now.Month; //this is the variable that dynamically you need to insert in


                    ConditionExpression birthdayCon = new ConditionExpression()
                    {
                        AttributeName = "ags_birthdaymonth",
                        Operator = ConditionOperator.Equal,
                        Values = { currentMonth }
                    };


                    qenew.Criteria.Conditions.Add(activeCon);
                    qenew.Criteria.Conditions.Add(birthdayCon);


                    //for easy fetch XML I use the Query Expression then convert to FetchXML
                    //but you can always use the directly generated fetchXML and also can get from Advanced Find as well
                    QueryExpressionToFetchXmlRequest req = new QueryExpressionToFetchXmlRequest();
                    req.Query = qenew;
                    QueryExpressionToFetchXmlResponse resp = (QueryExpressionToFetchXmlResponse)service.Execute(req);


                    //work with newly formed fetch string
                    string myfetch = resp.FetchXml;


                    //change the existing
                    entity["name"] = "Active Customer Birthday this Month"; 
                    entity["fetchxml"] = myfetch;
                }
            }
}



Code Explanation

*For the GUID of savedqueryid section, I got the ID and I implement in my code

image

*In your case, you need to do query, do not hardcode, please see my comment in the code as well.
For the Query Expresison, this is the place for you to change the logic as per your current requirement


image

Then for the Fetch XML

image

As the title mentioned, Then you need to register as POST event for RETRIEVE message, entity = savedquery.
Refresh the Advanced Find or View to see the result.

Result

As we can see that now, the Active Customer View (The View that we just now changed the fetchXML dynamically), now has this Query

image


You can also try to tweak your code, by just add additional conditional over the existing view by retrieving the fetch xml

string baseFetchXML = enSavedQuery["fetchxml"].ToString();

Then use this request to convert to Query Expression easily

FetchXmlToQueryExpressionResponse

Example of what I Did:



Remember, you can also add another Parameters.

So use this method for Querying:
1. Query that different from the static query from the View
2. Query that requires dynamic Date/Datetime variables
3. Query that needs condition obtained from another non-related entity
4. Query with variable from another Entity, let’s say Configuration Entity
5. Another Impossible Query using standard static Advanced Find
6. Also can do query that involving Current User, Security Role, or Team Query
7. Query requires Operator like “Does-Not-Equal Today” then you can utilize the “ON-OR-BEFORE” or “ON-ON-AFTER”
8. Query for “NOT IN”, then this one you can utilize the “DOES NOT EQUAL” function

But remember, you still need to use  FetchXML in the End.

Hope this helps!
Thanks

Friday 13 May 2016

Filter N to N (Many to Many) Subgrid Lookup for Existing Records in CRM Without Modifying the Ribbon Action

Introduction and Post References

Basically, in this post, I just want to show how to filter the N to N lookup in the subgrid in CRM Form, without even modifying the Global Subgrid Ribbon in CRM.

image

In my previous post: http://missdynamicscrm.blogspot.sg/2015/07/filter-nn-subgrid-in-crm-2013-using.html, I have explained about a way how to filter the N to N Subgrid Lookup, but that is needed to modify the ribbon action globally and you might need to set another condition to actually skip is you need to filter only on specific entity, but your grid will be appearing in many entities, such as:

You want to filter the Contact lookup in, if you modify the subgrid ribbon, in every contact subgrid, the filter will be applied and you might need extra code of criteria to actually limit the  execution.
Good thing about that if your entity only used for single entity and you don’t need to specify is that only for subgrid or associated view, then modifying the ribbon is a better solution. however, if you want to filter only subgrid in specific entity or form, you can actually using another way.

Like you can refer to my posts:


So, in this post, basically I try to combine the ways to become one solution, to filter the N to N lookup that only applicable once the user load the form using single javascript.  This is unsupported customization, but, remember, modifying the N to N view also needs unsupported customization, so yeah, to reach the requirement we have to go further for unsupported customization eventhough strongly I also not recommend.

The Code

So, I just try to combine the code becoming:

*For CRM 2013

function modifyRibbon(subgridName) {
    try {
        //to store the original function ones
        var originalFunctionAddNewStandard = Mscrm.GridRibbonActions.addNewFromSubGridStandard;
        var originalFunctionAddExistingStandard = Mscrm.GridRibbonActions.addExistingFromSubGridStandard;
        var originalFunctionAssociated = Mscrm.GridRibbonActions.addExistingFromSubGridAssociated;
        //add new standard subgrid
        Mscrm.GridRibbonActions.addNewFromSubGridStandard = function (gridTypeCode, parentEntityTypeCode, parentEntityId, primaryControl, gridControl) {
            if (gridControl != null) {
                if (gridControl.get_id() != subgridName) {
                    originalFunctionAddNewStandard(gridTypeCode, parentEntityTypeCode, parentEntityId, primaryControl, gridControl);
                }
                else {
                    originalFunctionAddNewStandard(gridTypeCode, gridControl);
                    filterTrainerProfile(gridTypeCode, gridControl);
                }
            }
        }

        //add existing standard subgrid
        Mscrm.GridRibbonActions.addExistingFromSubGridStandard = function (gridTypeCode, gridControl) {
            if (gridControl != null) {
                if (gridControl.get_id() != subgridName) {
                    originalFunctionAddExistingStandard(gridTypeCode, gridControl);
                }
                else {
                    originalFunctionAddExistingStandard(gridTypeCode, gridControl);
                    filterTrainerProfile(gridTypeCode, gridControl);
                }
            }
        }
        //add associate subgrid (N:N)
        Mscrm.GridRibbonActions.addExistingFromSubGridAssociated = function (gridTypeCode, gridControl) {
            if (gridControl != null) {
                if (gridControl.get_id() != subgridName) {
                    originalFunctionAssociated(gridTypeCode, gridControl);
                }
                else {
                    originalFunctionAssociated(gridTypeCode, gridControl);
                    filterTrainerProfile(gridTypeCode, gridControl);
                }
            }
        }
    }
    catch (ex) {
        alert(ex);
    }
}

*For CRM 2015

function modifyRibbon(subgridName) {
    try {
        //to store the original function ones
         var originalFunctionAddNewStandard = Mscrm.GridCommandActions.addNewFromSubGridStandard;
         var originalFunctionAddExistingStandard = Mscrm.GridCommandActions.addExistingFromSubGridStandard;
         var originalFunctionAssociated = Mscrm.GridCommandActions.addExistingFromSubGridAssociated;
        //add new standard subgrid
        Mscrm.GridRibbonActions.addNewFromSubGridStandard = function (gridTypeCode, parentEntityTypeCode, parentEntityId, primaryControl, gridControl) {
            if (gridControl != null) {
                if (gridControl.get_id() != subgridName) {
                    originalFunctionAddNewStandard(gridTypeCode, parentEntityTypeCode, parentEntityId, primaryControl, gridControl);
                }
                else {
                    originalFunctionAddNewStandard(gridTypeCode, gridControl);
                    filterTrainerProfile(gridTypeCode, gridControl);
                }
            }
        }

        //add existing standard subgrid
        Mscrm.GridRibbonActions.addExistingFromSubGridStandard = function (gridTypeCode, gridControl) {
            if (gridControl != null) {
                if (gridControl.get_id() != subgridName) {
                    originalFunctionAddExistingStandard(gridTypeCode, gridControl);
                }
                else {
                    originalFunctionAddExistingStandard(gridTypeCode, gridControl);
                    filterTrainerProfile(gridTypeCode, gridControl);
                }
            }
        }
        //add associate subgrid (N:N)
        Mscrm.GridRibbonActions.addExistingFromSubGridAssociated = function (gridTypeCode, gridControl) {
            if (gridControl != null) {
                if (gridControl.get_id() != subgridName) {
                    originalFunctionAssociated(gridTypeCode, gridControl);
                }
                else {
                    originalFunctionAssociated(gridTypeCode, gridControl);
                    filterTrainerProfile(gridTypeCode, gridControl);
                }
            }
        }
    }
    catch (ex) {
        alert(ex);
    }
}

* So the filterTrainerProfile(gridTypeCode, gridControl); is your custom function to filter the view, some as you filter and create new custom view that I also have example in my previous post:
http://missdynamicscrm.blogspot.sg/2015/07/filter-nn-subgrid-in-crm-2013-using.html.

Remember, to get the GUID of the list record to show, you can always using CRM Javascript or in CRM 2013 and above you can use smarter way that is to combine with Custom Action, since Custom Action can be called through Javascript.

http://missdynamicscrm.blogspot.sg/search?q=custom+action

Hope this helps! Jiayou!

Thursday 5 May 2016

'Workflow' entity doesn't contain attribute with Name = 'Workflowid' error during Importing Solution

Hi, just a quick one, if you receive this error:

image

When importing solution, please check this following setting:
LookupNameMatchesDuringImport
In your current environment.
By default, it is false.

Change this back to False to resolve the error, but remember, it will be potentially create a duplicate workflow after that, so you better clear them.

The reason of being duplicate is because they are workflow and workflow name is possible not unique, but the ID is.

*To change this setting easily, especially if you are using CRM Online, please use this way:
https://orgdborgsettings.codeplex.com/

image

As you can see, my previous setting was True, which causes the issue
Hope this helps!
Thank you

Monday 2 May 2016

Make a user in CRM ‘Joining More than 1 Business Unit’, enabling Users to See outside their Parent-BU Child Cluster Group records

Hi friends, as we know that Dynamics CRM can support the security access very well, it has the ownership feature, that is based on combination of Business Unit hierarchy and Security Role. But there is always limitation, such as, one User cannot be assigned to more than one Business Unit.

And the security role privileges are so strict, right if you have designed Parent-Child user access level, then the user inside cannot see records owned by other BU outside their parent-child cluster, so, the way is either you assign the Organization level or using Sharing, but the idea using sharing is can make the structure little bit unstructured, not easy to track shared/unshared records in single view unless we create a report or do advanced SQL Query.

I write this blog just because I have a case, that I am pretty sure this could be one of your experience in your CRM-ing journey.

So, here is my scenario that lead me into this idea.

The Scenario

Understand the Business Unit Hierarchy

Imagine, I have this hierarchy

image

Which applicable rule is very common requirement in Sales that each BU cannot do intervention each other, but they can see the records within their BU for collaboration. So, using this CRM Security Role, I can easily fulfill this by either provide the Business Unit Level or Parent Business Unit level privilege.

So, in CRM I have these Business Units

CRM Busines Unit Records

User join BU-1.1 cpy

Now, Understand the Users

In CRM, I would have this list of Users

User join BU-1 cpy

Come to The Another More Complex Rule

Now, I have another requirement:

The Product HQ Team is a stand-alone Business Unit, not a Sales-related, it is purely doing RnD and Product Management, but it supports all Regional Sales, so, once it has Record, example Potential Customer known by one of the Product Manager, or a Product related record that supports Sales, the Regional Sales should be able to View it.

So, I get an example, the User from West, Shawn Owen is a salesperson and Michael Lee is from Product HQ.

Once, Michael created a new Account, Shawn, should be able to view it, because the Organization needs him to follow up, it is not Michael job.


image

What’s Happening

Now, we already implemented a Security Roles that each Users are assigned to the Regional Business Unit with access = Parent-Child Level at max to prevent seeing each other record.

We also have a Regional Manager sitting in the Region area, which is correct. And Product HQ is a separated Business Unit without intervention.

All records:

User join BU-2

We know that when Shawn Owen is online and log in (see the top right logged in username), he will only see his own records and his WEST Business Unit teammate records.

User join BU-4

And when Michael Lee login

User join BU-5

Yes, they cannot see each other, which is correct for first rule, but we have another requirement to let the Regional users to see Product HQ newly created Account.

So, expected, once Shawn Owen is online, he will see other 2 records owned by “Product HQ”.

The Workaround

What we can do without tweaking is by let Product HQ user as owner, sharing the records to the Regional BU Teams or individual Users.

So, I share the record to Shawn and West

User join BU-6

Now, Shawn Owen is online and log in, he will be able to see the record that just now I share

User join BU-7

But, it is troublesome and every record must be shared, or you can do programmatically which is easy, this is one of the workaround as well.

Now I only share 1 record, I need to share another 1 record to make all those two records owned by Product HQ to be viewed by Shawn Owen.

This is not what I want to share in this article, so just get a new idea utilizing the concept of User, Team, and Business Unit.

The Final Solution

We know that we cannot grant Organization access to the Sales users nor can assign Shawn Owen in two different Business Units, so here is the solution.

Shawn Owen wants to see the record owned by Product HQ, as we know that all Business Unit in CRM always has a Default Team, and we have a concept that every user will join that default Team, so if we want to make a user to have “virtually” joining two different Business Units, then we need to make him joining the teams, but we cannot make a Users to manually joining the Default Business Unit Team in CRM.

So, come out an idea that we need to create our Custom Team manually.

User join BU-8

I create the Team and assign the Business Unit to the Product HQ, a Business Unit that I want the user to see the BU owned records.

Then, as part of my experiement, one of the users from Sales, I need to make him joining the team.

User join BU-9

Then, it is not enough, I need to assign the Security Role to the team.

User join BU-10..1jpg

With Security Role detail:

User join BU-12

Note:
And this is very important, you need to assign a Security Role to team with Business Unit access as well, eventhough YOU HAVE ASSIGNED THE PRIVILEGE TO THE USER SECURITY ROLE, it is not enough, if you want to play with Team concept of ownership!!

And now in Shawn perspective

User join BU-13

He now is joining two Team

And, now see the result once he login….

The Result

User join BU-11

As we can see once SHAWN OWEN from WEST login, he can see records owned by his Team (WEST) and also by Product HQ (eventhought Shawn is not part of Product HQ Business Unit).

He is virtually joining the Product HQ Business Unit, so long the records are owned by Product HQ Default Team or Product HQ Custom Team and the Team has Business Unit access, User access is not enough!

Hope this helps.
Thanks