Dataverse views as Criteria's definitions

Hello,

During project, we often have the need to implement some complex criteria’s handling for approval processes for example.
Redevelop this when we are talking about simple conditions if fine.
As soon as we want to be able to manage criteria’s like :

Field1 = Value1 && Field2 = Value2 || Field3 != Value3

This is starting to be funnier to develop and especially when you don’t want to hardcode anything but keep it flexible.
And if you don’t want to rely on the developer as soon as you want to update those conditions, well …

Why Dataverse Views ?

A friend of mine told me once :
“Clément, what if we use the views definition to handle those criterias with a standard functionality of the Dataverse ?”, Fabien Camous.

Sometimes when we have an idea in mind, we stick to it while there are really simpler solution in front of you. Developing the flexible criteria’s matrix with json as input VS using the Dataverse Views was a quick choice.
I started to dig into that direction and this was really the great idea.

You are already working with Dataverse so it’s open to your usage without extra development and especially the capability of a functional consultant or a key user is “enough” to update the criterias.

Goal ?

Our goal on that project and in the examples which follow was to make sure that a record was matching some criterias or not and based on that result set some fields values.

How to ?

The first step will be obviously to create the view :

You can see with the following that we have several criteria’s with AND / OR conditions which can be quite complex to implement programmatically from scratch but it took 2 min to do it be the view definition of Dataverse.

As you can see, we have a field called Opportunity ID in that sample, this allow use to make sure that we catch the unique Opportunity record, it’s a dedicated field with autonumbering but the same thing could be done with the GUID field of the entity.
This Opportunity ID value is set to 9999999 by default to easily replace it with the special record value you are targeting.

Then the next step will be to grab the view GUID in order to use it in your plugin / CWA or wherever you need it.

Here are the snippet code to perform the magic :

1- Retrieve the view definition

For one view at a time :
(savedquery for system views and userquery for personal views)

var fetchXmlQuery = _service.Retrieve("savedquery", new Guid("VIEWQGUID"), new ColumnSet("fetchxml"));  

For multiple views at a time :

var views = ["VIEWGUID1", "VIEWGUID2", "..."]; // could come from somewhere else

var fetchXmlQueries = _service.RetrieveMultiple(new QueryExpression()  
{
    EntityName = "savedquery",
    ColumnSet = new ColumnSet("fetchxml"),
    Criteria =
    {
        Conditions =
        {
            new ConditionExpression("savedqueryid", ConditionOperator.In, views)
        }
    }
});

2- Use the fetchXML definition to dynamically update it and execute it

foreach (var view in fetchXmlQueries.Entities)  
{
    var fetchXML = view.GetAttributeValue<string>("fetchxml");

    fetchXML = fetchXML.Replace("999999999", oppId);
    var result = this._service.RetrieveMultiple(new FetchExpression(fetchXML)).Entities;

    if (result.Count > 0)
    {
        // Record passing the criterias
        // Perform action
    }               
    else
    {
        // Record NOT passing the criterias
        // Perform action
    }
}

In that part, we modify the previous value we setup “9999999” into the value of the actual record we want to compare with.
Then executing the updated fetchXML allows us to make sure that we do have a complex valid query.

If we do have a result, it means that the record match the criterias.
If we do not have a result, the criterias are not matching the record value.

Based on the following result you can then perform a specific action and continue your record process.

Where to use it ?

The above example is about plugin directly.
You could imagine having the same approach with an Action which could be called via a Flow from a Canvas App.
Using this in an Azure Function to get the return result then.
There are a lot of possible scenario with it which is not “stick” to the classic plugin.

Conclusion

As you can see, we defined a “complex” with its limits of the Dataverse views capacity (which are still quite good) and implemented the logic within a plugin in few minutes when with a complete new logic it could take hours or days !

One recommandation from my side, it is to at most try to execute this in async mode to make sure that you are not slowing the UI during the process if you have many records / views to check. This is a recommandation since we make sure to perform only one request to grab all views definition but you can’t get rid of the RetrieveMultiple request for each fetchXML.

Let’s say that in that particular project, it was clearly a quick win and hope that quick win can be useful to some of you :

Enjoy,
Cheers

Original Post https://stuffandtacos.azurewebsites.net/2020/12/10/dataverse-views-as-criterias-definitions/