cancel
Showing results for 
Search instead for 
Did you mean: 

Allow Setting null value to the lookup field in Common Data Service (current environment) connector

One of the feature gaps between Common Data Service connector and Common Data Service (current environment) connector is the ability to set the null value to the lookup field.

 

When we set the null value to the lookup field in Common Data Service (current environment) 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. Common Data Service (current environment) 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
Regular Visitor

Yes, this is most unacceptable. I see no reason that it cannot simply be the actual GUID value with no need for the plural entity name. The database side does the fkey reference check anyway, so if you are passing in an invalid one, it most certainly would raise an error. Also, we know exactly what we are wanting to put there, so there is little need for this feature "Unless" it is added as a non GUID lookup, like let's say "InvoiceNumber" which you could gather from some external system that doesn't have the actual GUID value, and maybe the syntax would be something like /invoice/ValueFromExternalSystem/FieldNameToPerfromLookupOn - but even if that type of feature were added, it would still need to allow for the entire string to resolve to NULL, and not error if the field was not required.

Advocate I

Hi  @LinnZawWin and all,

 

Today Microsoft team implemented a work around for our scenario like this

if(empty(OUTPUT_HERE),'',concat('contacts(',OUTPUT_HERE,')'))

 

I have tested this and is working fine for optional lookups. Though like originally said complicating things is not acceptable but at least we have a work around now for our optional lookups.

 

Please Note:

Only empty string ('') is acceptable and NOT null for the field

I have tested this Power Automate of Oceanic region, report if not working in other regions

Advocate II

Still getting the error in West EU region: (trying to unassign an Account, so variable OwnerGuid is empty)

 

benn_1-1594903363767.png

 

benn_0-1594903288745.png

 

Kudo Collector

@pavansarma_2301 

 

I have tried empty string ('') in my Oceanic region but I am also getting the following same error as @benn 

Is it the latest changes that Microsoft has added? Shall I also raise a ticket with Microsoft Support to find out when will this feature be available for my environments?

 

"The supplied reference link -- -- is invalid. Expecting a reference link of the form /entityset(key)."

 

 

LinnZawWin_0-1594955022899.png

 

LinnZawWin_0-1594953929481.png

 

Advocate II

Just to confirm I'm still having the same message when trying to clear a lookup field via Power Automate using this approach:

if(empty(OUTPUT_HERE),'',concat('contacts(',OUTPUT_HERE,')'))

My previous attempt was not a good example since the Owner attribute is mandatory on the account entity.

But this last attempt was made on a custom lookup attribute.

@LinnZawWin do you still have the same behavior?

So far I've been unable to find a way to clear a lookup field independently which connector I use. (CDS or CDS Current Environment) 😞

Best,

Microsoft

Hi,

 

The workaround mentioned was for creating a new record.

 

This syntax 

if(empty(OUTPUT_HERE),'',concat('contacts(',OUTPUT_HERE,')'))

itself means leaving the lookup field as blank while creating a new record if the OUTPUT_HERE is blank, if not create the record with a value.

 

Update a record with removing a lookup value via Update a record action at the moment is a design limitation with the CDS current environment connector and the development team is looking at improving the experience.

 

In the meanwhile, you can use the non-current CDS connector (1) with action Update a record or the 'Unrelate records' (2) action will work. We can use an if condition to check if the lookup value to be disassociated is null or not as follows:

image.png

 

(1) Use the non-current connector in Solution-aware flow (flow insides solution)

 

The trigger uses the CDS Current Environment connector but the Update a Record use the Non-current connector, currently has different batch code, that still allow us using the "null" syntax 

 

David_Nguyen_0-1595500343407.png

 

 

David_Nguyen_0-1595499962722.png

 

 

Hope this helps!

 

David Nguyen

Advocate II

Thanks David,

 

I tried the relate / unrelate logic and it works, it just adds a hell lot of complexity to the flow for no obvious reason.

 

Can't help but thinking of it as a HUGE middle finger in the face of all citizen developers that MS is bragging about for years now...

 

Quite frustrating...... 🙂

Microsoft

Hi @benn ,

 

It is safe to use the native and non-native connector in a flow, right?

 

Kindly use mentioned updated post as workaround

 

The trigger uses the CDS Current Environment connector but the Update a Record use the Non-current connector 

Kudo Collector

Thanks, @David_Nguyen 

I can confirm that empty string '' for the empty lookup works for the Create a new record action.

Even though it is not the perfect solution, it solves half of my problems. (while the other half of them remain unsolved for Update a record action)

 

LinnZawWin_0-1595530591624.png