Setting a lookup field to blank

After spending close to 2 hours (starting midnight!) trying to figure out why my code wasn't setting a lookup field in CDS to blank, I finally figured out who the culprit was! I felt relieved but at the same time wanted to share this with the community before I went to bed!

To set a string to blank, all you need to do is set it to "" using Patch. However, for a lookup field, you need to use the Blank() function. More information on Blank() function can be found here.

To illustrate this, I will use the standard Users CDS entity. For my own user record, the manager field was populated with my manager's user record. I was trying to set that to blank (not that I wanted to remove my manager 🌝, I was just trying to use this as an example to figure out why this wasn't working for one of my customer's implementation). The expression I used to set my user's manager field to blank was:

Patch(Users, LookUp(Users, 'Full Name' = "Hardit Bhatia"), {Manager: Blank()})

The manager field did not get blanked out, as illustrated below:

I then enabled the "Formula-level error management" setting.

For reasons unknown to me, the same code worked and the manager field got blanked out, as illustrated below:

To conclude, if you want to use Blank() to set a lookup field to blank, make sure the "Formula-level error management" setting is enabled. Hopefully this will save you hours of banging your head against the wall!

Have fun! Get addicted!