Filter null values in Dataverse using Power Automate

Pieter VeenstraMicrosoft 3651 year ago224 Views

Filtering records on empty values can be a real pain as there are many mistakes to make.

Null

First of all I would like to have a look at the null function. Many times, I’ve seen people ask the question why their null values haven’t been working. Below 3 examples. Only one will really work.

In Power Automate you can have an expression null.

Filter null values in Dataverse using Power Automate Microsoft Office 365, Microsoft Power Automate image 2

You can type the text null (this is just 4 characters, n,u,l,l)

Filter null values in Dataverse using Power Automate Microsoft Office 365, Microsoft Power Automate image 1

And then you can have empty strings (i.e. don’t fill in anything)

Filter null values in Dataverse using Power Automate Microsoft Office 365, Microsoft Power Automate image 3

Mistake – Filter rows

Now how would we use the filter rows option to get just the records where a specified field is empty? You might want to try this:

Well the null function isn’t going to work like that! The above query will be evaluated as accountnumber eq

The eq operator has to have something on the right.

Also if you replaced the above with accountnumber eq ” you would only get the empty string values. which isn’t exactly the same as nothing. Huh???

When a field is not set Dataverse will not return that field at all and nothing is not the same as an empty string.

So how do we make this work?

Filter by null values

Now we can make use of FetchXml to filter by null values.

So for example if I wanted to find the accounts without an account number set I could use the following query:

Filter null values in Dataverse using Power Automate Microsoft Office 365, Microsoft Power Automate image 4

Filter by not null values

And in a similar way I could also find all the records that do have an account number set:

Filter null values in Dataverse using Power Automate Microsoft Office 365, Microsoft Power Automate image 5

All that is needed to filter your records are the null and not-null operators.

<fetch mapping='logical'>  
   <entity name="account">   
      <attribute name="accountid"/>   
      <attribute name="name"/>   
      <attribute name="accountnumber"/> 
      <filter>
            <condition  attribute="accountnumber" operator="not-null"/>
        </filter>
   </entity>   
</fetch>

Continue Reading Pieter Veenstra’s Article on their blog

Filter null values in Dataverse using Power Automate

First of all I would like to have a look at the null function. Many times, I’ve seen people ask the question why their null values haven’t been working. Below

Blog Syndicated with Pieter Veenstra’s Permission

Upvote0PointsDownvote

0 Votes: 0 Upvotes, 0 Downvotes (0 Points)