Clearing CDS lookup fields

[Billy_C]

Hi there,

So I have read an article about the unrelate and relate records at https://flow.microsoft.com/en-us/blog/work-better-with-the-common-data-service-in-power-automate/.

But I have been wondering about a few things:

How about "Many to One" relationships for example with the Contact entity for the field Primary Contact?

How can you update a specific Contact and make the Primary Contact field empty because you can't do: "contacts()" since this will generate an error because it requires an ID?

Is there a way to clear a lookup field in a flow since it is possible to do all this manually?

To give an example for my case:

There are 3 entities: Company, Article and Staffel.

Article has 2 relations with Company both are "Many to One" and is to display the "Manufacturer" and "Distributor" of the Article.

This is the same for Staffel because I am saving the codes of the Manufacturer and Distributor at both Article and Staffel.

Since It is possible that both have different codes. So for my flow when the article updates I would like to ensure that if the "Manufacturer" or "Distributor" relation is updated or removed that all related staffels will be updated respectively instead of having to update these relations manually.

I hope that there is a solution to this so It can be automated.

Thanks in Advance!

[Mira_Ghaly]

@Billy_C 

Try using the null expression on your Lookup field as below

[Billy_C]

Hi @Mira_Ghaly ,

I have tried using the null expression like you mentioned but the flow failed on that part and it has generated following error:

Spoiler

{

  "error": {

    "code": "0x0",

    "message": "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.\r\n   at Microsoft.OData.JsonLight.ODataJsonLightDeserializer.ReadAndValidateAnnotationStringValue(String annotationName)\r\n   at Microsoft.OData.JsonLight.ODataJsonLightDeserializer.ReadAndValidateAnnotationStringValueAsUri(String annotationName)\r\n   at Microsoft.OData.JsonLight.ODataJsonLightResourceDeserializer.ReadEntryPropertyAnnotationValue(String propertyAnnotationName)\r\n   at Microsoft.OData.JsonLight.ODataJsonLightDeserializer.ReadODataOrCustomInstanceAnnotationValue(String annotatedPropertyName, String annotationName, PropertyAndAnnotationCollector propertyAndAnnotationCollector, Func`2 readPropertyAnnotationValue)\r\n   at Microsoft.OData.JsonLight.ODataJsonLightDeserializer.ParseProperty(PropertyAndAnnotationCollector propertyAndAnnotationCollector, Func`2 readPropertyAnnotationValue, String& parsedPropertyName)\r\n   at Microsoft.OData.JsonLight.ODataJsonLightDeserializer.ProcessProperty(PropertyAndAnnotationCollector propertyAndAnnotationCollector, Func`2 readPropertyAnnotationValue, Action`2 handleProperty)\r\n   at Microsoft.OData.JsonLight.ODataJsonLightResourceDeserializer.ReadResourceContent(IODataJsonLightReaderResourceState resourceState)\r\n   at Microsoft.OData.JsonLight.ODataJsonLightReader.ReadNextNestedInfo()\r\n   at Microsoft.OData.JsonLight.ODataJsonLightReader.ReadAtNestedResourceInfoEndImplementation()\r\n   at Microsoft.OData.ODataReaderCore.ReadImplementation()\r\n   at Microsoft.OData.ODataReaderCore.InterceptException[T](Func`1 action)\r\n   at System.Web.OData.Formatter.Deserialization.ODataReaderExtensions.ReadResourceOrResourceSet(ODataReader reader)\r\n   at System.Web.OData.Formatter.Deserialization.ODataResourceDeserializer.Read(ODataMessageReader messageReader, Type type, ODataDeserializerContext readContext)\r\n   at System.Web.OData.Formatter.ODataMediaTypeFormatter.ReadFromStream(Type type, Stream readStream, HttpContent content, IFormatterLogger formatterLogger)"

  }

}

Any ideas on how to solve this?

Thanks in Advance!

[Mira_Ghaly]

@Billy_C 

Can you share a screen shot of your step? expanded? I assume you are using the CDS current Environment connector.

[Billy_C]

Hi @Mira_Ghaly ,

Yes ofcourse no problem, I am indeed using the CDS current environment connector.

I hope this helps!

Thanks in Advance

[Mira_Ghaly]

@Billy_C

For Troubleshooting can u remove the null from Manufacture and rerun the flow?

Also can you add a compose data operation before the update step and add to it the value you ar passing to the Id. 

Same for the other lookup value u are passing, please add to a compose data operation and observe the value.

[Billy_C]

Hi @Mira_Ghaly,

My apologies for the late reply, I have tried to execute what you said.

The 2 compose methods look like this:

https://i.imgur.com/M2DCiHs.png 

When executing the flow I get the following result:

https://i.imgur.com/U0RsBjr.png

So it seems like the compose of the Manufacturer has no input or output.

I hope this is the information you wanted.

Thanks in Advance!

[ManishJain]

Hi @Billy_C ,

Have you tried doing HTTP request (DELETE) with Azure AD connector. This works well.

https://linnzawwin.blogspot.com/2019/11/power-automate-how-to-set-lookup-field.html

https://pravinpawarweb.wordpress.com/2019/03/27/update-set-lookup-reference-value-as-null-using-dele...

Thanks

[Mira_Ghaly · Accepted solution]

@Billy_C 

Can you try to use the below action, but i am not sure if in your case you have if of the record you want to remove the relation for

[Billy_C]

Hi @ManishJain ,

Sorry for the late answer but didn't have the time to check this issue out any further.

I haven't tested out the HTTP request as you mentioned but I will definitely test it out if I have the for it and I'll let you know if it works or not.

Thanks in Advance!

[Billy_C]

Hi @Mira_Ghaly ,

I can't use the unrelate to remove a company from the article (many Article to one company).

Although the other way is possible but won't this remove this company for all articles? While it should only do for the one article specified?

Thanks in Advance!

[Mira_Ghaly · Accepted solution]

@Billy_C 

No because you are specifying the Id of the Article and the Id of the company.

[Billy_C]

Hi @Mira_Ghaly,

My apologies I didn't know you could just pass the id of the entity inside of the URL field.

I thought that a URL to the page was needed.

Perhaps the name for that field should be URL/id to show that id of an entity is also possible

The name is kind of confusing as it is right now.

Anyways, thank you for clearing this out.