Recently I was troubleshooting an issue with the Logic App connector for dataverse. The scenario is we have a table which has 2 date fields in it (actual delivery date and actual shipment date). When our logic app runs we get the input dates to the logic app in the format yyyy-MM-dd from the other system. We then convert them to the yyyy-MM-ddThh:mm:ss.000Z format so we can update the record in the dataverse table.

This works great but the problem comes about when the source system supplies a blank date for one of the fields.

If you use the dataverse connector and just supply the date values from the parse json shape you will get an error like below.

DateTime is less than minumum value supported by CrmDateTime. Actual value: 01/01/0001 00:00:00, Minimum value supported: 01/01/1753 00:00:00

If we look at the more info on the connector we will see the message the logic app supplied to the connector will look like this body:

"body": {
        "ipl_actualdeliverydate": "",
        "ipl_actualshipdate": ""       
    }

The problem is the connector is attempting to supply empty string and we need it to supply null.

The obvious instinct is to use an expression in the connector such as:

if(equals(length(body('Parse_JSON_-_Formatted_Dates')?['formatted_placement_date']), int('0')), null, string(body('Parse_JSON_-_Formatted_Dates')?['formatted_placement_date']))

I tried this but I was still getting the same issue!

I tried thinking about a few different ways to solve this problem, and going down a rabbit hole when the solution is not that difficult, but it is a bit stupid.

If we look at the peek code for the connector we will see it looks like below when I add the expression:

"ipl_actualdeliverydate": "@{if(equals(length(body('Parse_JSON_-_Formatted_Dates')?['formatted_placement_date']), int('0')), null, string(body('Parse_JSON_-_Formatted_Dates')?['formatted_placement_date']))}",

If I modify it to remove the { and } at the start and end of the expression like below then it will work fine.

"ipl_actualdeliverydate": "@if(equals(length(body('Parse_JSON_-_Formatted_Dates')?['formatted_placement_date']), int('0')), null, string(body('Parse_JSON_-_Formatted_Dates')?['formatted_placement_date']))",

When I run the test case with empty dates then the json for the connector request body looks like this.

"body": {
        "ipl_actualdeliverydate": null,
        "ipl_actualshipdate": null,        
    }

And if I run the test case where the dates have values then the connector body looks like this.

"body": {
	"ipl_actualdeliverydate": "2021-02-14T12:00:00.000Z",
        "ipl_actualshipdate": "2021-02-14T12:00:00.000Z"     
    }

As you can see in the picture below you cant tell the difference as they both look like valid designer setup but under the hood the json is different and it results in a null being sent to dataverse instead of an empty string even though the expression is exactly the same.

 

Buy Me A Coffee