Agile Xrm

A blog about all things Dynamics CRM and Agile

CRM 2011/2013 - Using Roll-up Queries as configurable searches in plugins/workflows/services

During a recent project I was working on we had the need to have configurable searches that would be used in code to perform an action against those records.

The standard way to do this was just to use a text field and get an advanced user to paste in the FetchXML needed for the search. This however is not very user friendly at all.

The first idea we thought of using was a saved view, which gives the user all the power of advanced find, but it still isn’t very neat and accessible from a CRM form and difficult to reference the correct view from a settings entity or in code.

One of my colleagues came up with using the Goal Rollup Queries. You can create a lookup to the Rollup Query on your settings entity or record that requires a search to be related to it, and then reference that in your plugin/workflow or windows service.

There is a field on the rollup query entity called fetchxml that stores the fetchxml of the rollup query which can be referenced like so:

 

            Guid RollupQueryID;

            string fetchXml;

            QueryExpression qryCriteria = newQueryExpression()

                        {

                            EntityName = GoalRollupQuery.EntityLogicalName,

                            ColumnSet = newColumnSet("fetchxml"),

                            Criteria =

                            {

                                Conditions =

                                {

                                  newConditionExpression("goalrollupqueryid", ConditionOperator.Equal, RollupQueryID),

                                }

                            }

                        };

 

            DataCollection<Entity> RollupQuery = service.RetrieveMultiple(qryCriteria).Entities;

            if (criteriaCol.Count > 0)

            {

                fetchXml = ((GoalRollupQuery)RollupQuery[0]).FetchXml;

                FetchXmlToQueryExpressionRequest reqFetch = newFetchXmlToQueryExpressionRequest() { FetchXml = fetchXml, };

                FetchXmlToQueryExpressionResponse resp = (FetchXmlToQueryExpressionResponse)service.Execute(reqFetch);

                QueryExpression QueryExp = resp.Query;

             }

 

 

You can now use the QueryExpression to query CRM and apply whatever logic to the records it returns (as well as add additional conditions if needed).

Loading