cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate II
Advocate II

CDS Update a record differentiate between null and undefined/empty

Hi all I'm trying to update a Dynamics CRM record using CDS "Update a record". When entering data into the fields, I have a source JSON from an external source which, for multiple fields may have either:

1) have a value for the field (i.e. update Dynamics with the new value)

2) have the field with a value of null (i.e. clear the existing data from the field in Dynamics) or

3) not contain the field (i.e. don't update the field in Dynamics)

 

How can I put a formula into a field in the "update a record" that will accomodate any of the 3 options above. It appears to me that, as soon as you enter anything into the field, it will eliminate the 3rd option above. I would guess a formula needs to provide a differentation between null and empty.

 

Any ideas?

5 REPLIES 5
Highlighted
Solution Sage
Solution Sage

Hi @Stevenfayers ,

 

Can you please provide your input JSON ?

 

Thanks

Highlighted

Hi Manish As an example, I could have one of any of the following each time the flow runs:

example1

{
...
  "numberofemployees": 2000,
  "ownershipvalue": 888000000,
  "otherphone": "+44 1234 567890"
...
}

or example2

{
...
  "numberofemployees": null,
  "ownershipvalue": 888000000  
...
}

 or example3

{
...
  "ownershipvalue": 888000000,
  "otherphone": null
...
}

 The question I have is what formula should I use when filling out the "Number Of Employees" field

 

Stevenfayers_0-1593094291687.png

that covers example 1 (with a value), example 2 (with null) and example 3 (not provided, empty, leave the existing value in Dynamics alone)?

 

 

Highlighted

Hi @Stevenfayers ,

 

When you are doing Parse JSON , in schema add null to your numberofemployees field like this 

 "type": [ "integer", "null" ] instead of "integer" to handle null values. 
 
Just add compose action (for debugging) and use "if(equals(body('Parse_JSON')?['numberofemployees'],null),'Null Found',body('Parse_JSON')?['numberofemployees'])" in expression. 
 
I have tested this to work for both null and non null and even if the field is not coming up. 
 
Thanks
Highlighted

Hi @ManishJain So I did a little test:

Stevenfayers_0-1593101694039.png

the full expression is :

If(equals(body('Parse_JSON')?['numberofemployees'],null),null,body('Parse_JSON')?['numberofemployees'])
 
If the number of employees is missing from the JSON, this evaluates to null, and then clears the existing data from Microsoft Dynamics. I want it such that if the element is missing from the JSON, then don't update the current field value at all.
 
payload sent to dynamics:
 

Stevenfayers_0-1593102046087.png
Because it is missing from the original JSON, I want to be able to have an expression that CDS update a record also excludes it from the update JSON.

Highlighted

Hi @Stevenfayers ,

In that case you might need to do break your update in two parts 1, for your value and 2 for not having anything in that . I do not think via expression it is achievable.

 

Thanks

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Top Solution Authors
Top Kudoed Authors
Users online (12,659)