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

[LinnZawWin]

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'])

CC: @Audrie-MSFT 

[rw_ga]

@klemetsrud,

I'm not a fan of the way the new DATAVERSE connector handles lookups either and for multiple reasons.  The legacy way of handling polymorphic lookups was much easier for us citizen developers.  All we needed was the record/row GUID entered in a single field/column.  All linking appears to have happened on the backend, somehow.  Now the new connector breaks out each relationship of the polymorphic lookup into a dedicated field/column.  So I first have to determine what entity/table type is getting updated and then create an update action for each dedicated field.  For example, the customer field on an invoice is a polymorphic lookup and has 2 separate dedicated fields/columns now, see below screenshot. 

If you try to update an invoice, you have to know which entity/table type (Account or Contact) the customer is first and then create a dedicated "update a row" action for it.  For example, you select multiple invoices and tell it to change the customer to Accounts(xyz).  You have to create 2 different update actions after a switch control because the invoices you selected my have an account OR contact currently linked to it.  So if the customer WAS originally a contact, you have to clear the value in "Customer (Contacts)" using NULL and add "Accounts(xyz)" to "Customer (Accounts)".  But if the "Customer" is already an account, you just update "Customer (Accounts)" with the new "Accounts(xyz)" value.  Maybe you could use the 'unrelate' action instead of using NULL, but that still another action/call...

This has created a lot of unwanted actions/calls/branches in some of my flows.  Am I wrong and just need to figure out a better way to handle these or do others feel my pain???

I wish there was a way to use an expression that DIDN'T TAKE ANY ACTION, as if an expression wasn't even entered.  That way we could use one 'update a row' action and this new expression to evaluate whether or not to enter data, clear data, or do nothing.  It would require adding an expression to each dedicated polymorphic lookup table field/column but I'm okay with that, I'm not okay with having to do all the branching and duplicate actions.  All the other fields on these update actions are the same, so if you ever have to modify one field value, you have to touch each update a row action and duplicate the change.

The expressions I've tried all require some type of value be entered whether its null, '', etc., but that doesn't work if you don't want to clear the value.  I just want to take no action.  no action is an action in itself!  I don't know if this example is accurate, but I'll try to use the 'if' expression as an example of what I'd like:  if(empty('output of an action above'),DO NOTHING/ACT AS IF THIS EXPRESSION WASN'T EVEN ENTERED,'Take action')

Another example of why we could use a take no action expression is because when you use an expression to pass the same column value as the existing value in a row, and that table has a cloud flow monitoring changes on that column, it will see that same value as a change regardless of the fact the value doesn't actually change.  End result the flow is triggered.

Don't get me wrong, I like SOME of the new changes made in the NEW connector, but this has been a really sore spot for me.