cancel
Showing results for 
Search instead for 
Did you mean: 

Allow Setting null value to the lookup field in Microsoft Dataverse connector

One of the feature gaps between Microsoft Dataverse (legacy) connector and Microsoft Dataverse connector is the ability to set the null value to the lookup field.

 

When we set the null value to the lookup field in Microsoft Dataverse connector, the flow fails with the following error.

 

 

 

An error occurred while validating input parameters: Microsoft.OData.ODataException: The 'odata.bind' instance or property annotation has a null value. In OData, the 'odata.bind' instance or property annotation must have a non-null string value.

 

 

 

 

It is important when we want to clear multiple lookup fields in one API call (without multiple Unrelate records actions), or the most common issue for me is passing the GUID from another step which is an optional lookup field (so that the value may/may not be null)

If the lookup field is polymorphic for both source and target records, it will be good if we can check the entity type in the expression and fill null/the target record OData ID accordingly into the right field.

 

One of the examples is Setting the Primary Contact in the screenshot below with the GUID value of "Primary Contact (Value)" which can be empty from the source data. Microsoft Dataverse connector cannot handle this kind of scenario and it will be great if we can use a null check expression for that case.

 

 

 

if(empty(triggerOutputs()?['body/_primarycontactid_value']), null, triggerOutputs()?['body/_primarycontactid_value'])

 

 

 

Flow Value Set.png

 

CC: @Audrie-MSFT 

Status: New
Comments
StephaneSeguin
Frequent Visitor

@microsoft 

Why are is this still not fixed? This post is 1 year old and we still have the same issue.

 

The more I use Flow the more I regret Classic Workflow that was working very well.

 

 

elmars
New Member

Fully agree. This workaround for Updating existing record to clear out Lookups, adds too much boiler logic and extra steps which also makes flows run slower in the end 😞

prodaptiv
Regular Visitor

Use an if empty expression with a concat like below.

if(empty(variables('ContactGUID')),null,concat('contacts(',variables('ContactGUID'),')'))

elmars
New Member

Hi @prodaptiv,

 

thanks. Now I can confirm this that setting "null" during Update method clears out the reference/lookup value! very great news indeed! Tested in version 9.2.21053.00135

 

And this works now both for Create and Update. Thank you MS! 😌

NielsL
Helper V

Are there any plans on fixing this? This is such as hassle working with lookups in the new'ish Dataverse connector.

LinnZawWin
Kudo Kingpin

I just noticed that we can now set the null value to the lookup column in the Microsoft Dataverse connector. Good job, team. 👏👏👏

Martin0203
Regular Visitor

Just tried to set a lookup column with an existing value to NULL and found it does not work for me.

 

{
  "error": {
    "code""0x0",
    "message""The supplied reference link -- xx_regions() -- is invalid. Expecting a reference link of the form /entityset(key)."
  }
}
 
xx_regions is the custom lookup table that the lookup refers to.
klemetsrud
Frequent Visitor

@Martin0203 

 

Hello.

 

I use the following syntax

if(empty(triggerOutputs()?['body/_parentcustomerid_value']),null,concat('accounts(',triggerOutputs()?['body/_parentcustomerid_value'],')'))

 

So, in that example i'm saying if the _parentcustomerid_value (which is the real id instead of using just parentcusotmerid when it's not the primary key of an entity) is empty, then output just NULL otheriwse we concat the plural name of the entity accounts( with the value and a close paren ) - so the output will be either FULLY NULL OR something like: accounts(yourGUIDHere) - which does work for me.

Martin0203
Regular Visitor

Hi @klemetsrud 

 

Your code works ... after some experiment.

 

Microsoft sells PowerAutomate as low code platform, they better fix this issue without the need for such a complicated solution. This was possible without if conditions in the old Dynamics workflows from at least CRM 2011.

klemetsrud
Frequent Visitor

@Martin0203 - I agree it is not ideal, but "low code" is not "no code".

 

For simple things it is indeed close to no code.

 

I personally think it's kind of dumb that you have to pass in the plural name UNLESS it's one of those lookups that can be for a "customer" that is either a contact or account, then I get it. If it's a 1 to 1 lookup though, it should be smart enough to just handle that.

 

The hardest part is usually finding out the plural name of your entity. Out of the box ones are easy = contacts, accounts, invoices, etc. Custom ones get wonky sometimes, but you can always figure out the syntax by going to: https://yourorgnamehere.crm.dynamics.com/api/data/v9.1 - (replace yourorgnamehere with yours) and it will dump out a bunch of junk. Find the entity you are looking for and see the plural spelling of it. This will give you what to precede your value with.

 

Take care!