Update Dataverse Date Column to Null/Blank

[rkochanowski]

My apologies in advance for adding a discussion item that has been posted on multiple previous occasions. I think I've looked at every possible solution out there that has worked for others, to no avail. In a nutshell, whether I try to apply the update using if(equals()), if(empty()), or coalesce(), to parse the incoming date value, if that value is null/blank, the update fails.

Scenario:

Using Power Automate,

• I'm importing data from a Smartsheet sheet using the HHTP Request action.
• Next, I have a Select action grabbing the individual rows that were returned. 
• Then I ensure the row is available in Dataverse using a Condition action
• If the Dataverse row id is found, the Dataverse Update a Row action fires

This is where the error occurs:  

OpenApiOperationParameterTypeConversionFailed. The 'inputs.parameters' of workflow operation 'Update_Ticket' of type 'OpenApiConnection' is not valid. Error details: Input parameter 'item/dp_datecertpagesubmitted' is required to be of type 'String/date-time'. The runtime value '""' to be converted doesn't have the expected format 'String/date-time'.

This error is returned for any date field that's being updated in Dataverse where the value is null/blank.

For the one item showing in the error, I've tried each of the following formula in the Update action with the same error resulting:

if(equals(item()?['datecertsubmitted'],''),null,item()?['datecertsubmitted'])

if(empty(item()?['datecertsubmitted']),null,item()?['datecertsubmitted'])

coalesce(item()?['datecertsubmitted'],''),item()?['datecertsubmitted'],null)

The incoming value is null/blank:

The run results in the error:

 

Here's the kicker...when a new row is added to the same Smartsheet, I have the same basic process running to create a new record in the same Dataverse table which passes these date values in to the table directly, and if the value is null/blank, the record is created with no value in the field!  By the way, I've tried passing the values in directly as well and that didn't work either.

What in the world could be causing this to not work for the Update action?

[David_MA]

Can you show the date field and what you are populating it with in your Update Ticket action? It is cut off in your screen shot.

[rkochanowski]

@David_MA Sure!  

 

[David_MA]

I don't see any issues. Maybe someone else will notice something I don't see. Does it run correctly when your date field is populated?

[rkochanowski]

@David_MA Thanks for looking!  Yes, when the date is present everything runs fine.  The issue is only when the value is blank/null.
Have a great New Year!

[David_MA]

If it works when the date is populated, add a Compose action before your List Tickets Dataverse action and put the datecertsubmitted field into it so you can see what is being passed to the flow. Then temporarily update the field with 2024-01-02 just to see if that also generates an error. The date needs to be in ISO8601 format, and I am thinking that the date being passed to the action is not in this format. If that is the case, you'll need to modify your current expression with a FormatDateTime expression to format it in ISO8601 format.

[rkochanowski]

@David_MA 

So, if I understand your suggestion, add a compose and see how the empty/blank/null is populating.  These dates are formatted in ISO8601 format.  Again, when there's a date present in the field, the flow runs fine. 

It's only when there's no date present that there's an issue.  Using Equals(), Empty() nor Coalesce() functions work to insert a NULL into that field of the Dataverse table.

[David_MA]

If there is no date, why do you need to update the Dataverse field with null? Isn't the field already null?

[rkochanowski]

Because Power Automate is throwing the error!

This error is what has me befuddled. How do I get around it?  And to your point, "If there is no date, why do you need to update the Dataverse field with null? Isn't the field already null?"...I agree with you 100%!!!

As a matter of fact, I have an Add a row action for when a new row is added and the empty date values go into Dataverse as null without any issue.  It's only on the Update a row action that the error occurs.

[David_MA]

We may be talking about two different things, so I created a flow of what I think you are trying to do:

In the above flow, the date in the trigger is optional. Then, like you, I have a condition to see if the date field is empty. If yes (meaning the date field is null), I update the Dataverse table and do nothing with the CurrentDate field since it is already null. If no (meaning a date has been selected), it updates the Dataverse table with the value that was selected from the trigger. Is this what you are trying to do?

These are the expressions in the flow. In the condition to check if the date in the trigger is null:

empty(triggerBody()?['date'])

And this is the expression to populate the CurrentDate field on the No side of the condition so that the trigger date is formatted as an ISO8601 date:

formatDateTime(triggerBody()?['date'],'yyyy-MM-dd')

The expression in the Request field is not relevant. The field is required since it is the primary key in the table I created.

[rkochanowski · Accepted solution]

Somehow, after two weeks of trying to figure this out, I went back to the original:

item()?['cells']?[0]?['value']

and it worked!  When no value is available 'null' is value and a date field can accept a null value...but it cannot accept an empty string, which was the case that this post was regarding.

Thanks for the fix Microsoft!

[Angelo_ntt]

Hi @rkochanowski ,

Could you kindly provide details on how you solved this problem using the formula: item()?['cells']?[0]?['value']?

Did you initialize a variable outside of an "apply to each" loop and then use a "set variable" action where you wrote this formula?
Thank you in advance!

[rkochanowski]

@Angelo_ntt , as I stated in the comment marked as the solution, I didn't do anything to correct the issue.  My guess is that Microsoft did an update, broke something, found the error, then fixed it.  

That said, my process was to

• Perform an HTTP Request accessing the SmartSheet API.  This API is a bit of a challenge, but the values returned can be worked with. This is the GET used: 

  https://api.smartsheet.com/2.0/sheets/@{variables('varSheet')}?rowsModifiedSince=@{outputs('Compose_ModifiedAt')}&include=discussions

• Using the Select action, mapped each value returned from SmartSheet to a key that could be better worked with. the formula that's the focus of this discussion maps like this:

"select": {
            "datereferred": "@item()?['cells']?[0]?['value']"
}

Visually, this looks like:

This should have worked all along, but suddenly started throwing the error referenced.  Then, just as suddenly, began working again.

Hopefully this answers your question,

Ron