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

25 comments:

  1. Hi,

    Based on your post i can see that you are Fetching the "Month Number" and storing in Database
    And this "Month Number" field is being used in filtering purpose.

    But i am afraid that, this will have a negative impact when we deal with users across different regions as date will get automatically converted based on user timezone settings,

    So, if I being in Malaysia Entered my DOB as "01-May-2016", the user in USA can view it as "31-APR-2016" but the "Month Number" field will show as "5" for both users. So, when the user being in USA, try to retrieve the list of data whose DOB falls in Apr, as per above code, it will miss the records with data "01-May-2015" but the data will show that user DOB is in APR. it will lead to wrong data retrieval.

    Please consider this and modify the code accordingly.

    I feel storing a "Month Number" will lead to unnecessary implications.
    As you are already using C# code, we can make in a way where in the condition should go like below,

    DOB is "greater than or Equal" to 1st day of Month and "lessthan or equal" to last day of Month.

    Hope this helps!!!

    ReplyDelete
  2. Truely a very good article on how to handle the future technology. After reading your post,thanks for taking the time to discuss this, I feel happy about and I love learning more about this topic. keep sharing your information regularly for my future reference. This content creates a new hope and inspiration with in me. Thanks for sharing article like this. The way you have stated everything above is quite awesome. Keep blogging like this. Thanks.
    Hadoop training in chennai

    ReplyDelete
  3. We are expert and professional in ms crm series. Here are option for the all these articles Microsoft Dynamics CRM : Ms crm 2011 , 2013 and 2015 step by step tutorial of ms crm error in mscrm plugin workflow ms crm javascript Code .

    ReplyDelete
  4. Hi, can I have inputs from you guys. My scenario is to open a view via java script (on click of button), how can I trigger the plugin through java script? Do I have to make a custom entity then create an update with that entity? Is that the logic here or there's much more better approach?

    I want to dynamically filter the view's criteria through plugin. My apologies to this shabby question, crm newbie here.

    ReplyDelete
  5. Thank you for sharing any good knowledge and thanks for fantastic efforts
    afssac
    fire fighting academy
    Firefighter I *
    Arsisc

    ReplyDelete
  6. Truely a very good article on how to handle the future technology. elsa games

    ReplyDelete
  7. Thanks for your article. You have rectified my doubts i had before reading this blog.
    IOS Training in Chennai

    ReplyDelete
  8. Hi
    plugin running but
    throwing an error error "Could not find link-entity node for alias"

    Can you please suggest me any ideas to resolve this.

    ReplyDelete
  9. I really appreciate information shared above. It’s of great help. If someone want to learn Online (Virtual) instructor lead live training in Big Data Hadoop and Spark Developer, kindly contact us http://www.maxmunus.com/contact
    MaxMunus Offer World Class Virtual Instructor led training on MS Dynamic CRM. We have industry expert trainer. We provide Training Material and Software Support. MaxMunus has successfully conducted 100000+ trainings in India, USA, UK, Australlia, Switzerland, Qatar, Saudi Arabia, Bangladesh, Bahrain and UAE etc.
    For Demo Contact us.
    Sangita Mohanty
    MaxMunus
    E-mail: sangita@maxmunus.com
    Skype id: training_maxmunus
    Ph:(0) 9738075708 / 080 - 41103383
    http://www.maxmunus.com/


    ReplyDelete
  10. I really appreciate information shared above. It’s of great help. If someone want to learn Online (Virtual) instructor lead live training in Salesforce, kindly contact us http://www.maxmunus.com/contact
    MaxMunus Offer World Class Virtual Instructor led training on MS Dynamics CRM. We have industry expert trainer. We provide Training Material and Software Support. MaxMunus has successfully conducted 100000+ trainings in India, USA, UK, Australlia, Switzerland, Qatar, Saudi Arabia, Bangladesh, Bahrain and UAE etc.
    For Demo Contact us.
    Nitesh Kumar
    MaxMunus
    E-mail: nitesh@maxmunus.com
    Skype id: nitesh_maxmunus
    Ph:(+91) 8553912023
    http://www.maxmunus.com/



    ReplyDelete
  11. Hi,

    Thanks for the valuable information.
    Kindly provide your suggestion to get the savedqueryid using entity name and view name
    in Dynamics 365 crm online instance

    ReplyDelete
  12. This comment has been removed by the author.

    ReplyDelete
  13. This is my first time i visit here. I found so many interesting stuff in your blog especially its discussion. From the tons of comments on your articles, I guess I am not the only one having all the enjoyment here keep up the good work crm care salesforce

    ReplyDelete
  14. Such a nice blog i really like what you write in these blog i also have some relevent information about online crm software if you want more information.

    ReplyDelete
  15. Thank you for sharing any good knowledge and thanks for fantastic efforts.
    Microsoft Dynamics AX Online Training

    ReplyDelete
  16. This blog is truly useful to convey overhauled instructive undertakings over web which is truly examination. I discovered one fruitful case of this truth through this blog. I will utilize such data now.
    Power conditioner

    ReplyDelete
  17. If you are looking for more information about flat rate locksmith Las Vegas check that right away. Sandfilteranlage

    ReplyDelete
  18. Awesome blog. I enjoyed reading your articles. This is truly a great read for me. I have bookmarked it and I am looking forward to reading new articles. Keep up the good work! I will create 200 do follow backlinks blog comments on actual page

    ReplyDelete
  19. Thank you very much for your fascinating write-up. It's truly exceptional. https://zonewp.com/

    ReplyDelete
  20. Thank you for sharing such a nice post. I love it so much.Check Office Furniture Miamia

    ReplyDelete
  21. Thank you for this insightful post! I found it really helpful. If you want to learn more about scaling applications, check out HyScaler for some valuable resources.

    ReplyDelete

My Name is..