Answered

Coalesce can't get value from MS Forms

(0) Share

Report

Posted on by DF-16031344-0

I have a MS Forms where the user choose year, and then get a dropdown menu where they choose name. In total there are five questions where the name can be chosen, but only one question can be answered per form (because of the previous chosen year leads to only one question). Four of the questions is therefore blank, and one have one name. The coalesce doesn't give any results ("value": null) even though I've checked there are data in one of the outputs. How can I make this work? I want the formula to check the five outputs, and give me the one that isn't blank.

I'm using this formula in a Set variable-box:

coalesce(outputs('Hent_svardetaljer')?['body/r6be04227351c4b8f9112dc937deb47f1'],outputs('Hent_svardetaljer')?['body/r074d0f9054e84a8e82704eec58dffc68'],outputs('Hent_svardetaljer')?['body/r07ef740bfdda41bfa66828ddc146566e'],outputs('Hent_svardetaljer')?['body/rb57d2aa0415c4334a70aab13caa9b1f4'],outputs('Hent_svardetaljer')?['body/r289529363b7e4874b8fcdc103fb92782'])

It gives this output when running the flow:

Thanks in advance for any help!

Categories:

Building flows

I have the same question (0)

All responses (3)

Answers (1)

Sort by

Fredrik_A 3,570 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hey,

could you please share a picture of a run you did where we can see that there is a value.

Most likely one or more of your dynamic values are wrongly mapped and might have changed ID if you replaced some of the questions.

If my response solved your issue, please mark it as ✅ Accepted Answer and give it a like.

Was this reply helpful? Yes No
Verified answer

Inogic 1,277 Moderator on at

Like (1)

Report
Copy link

Link copied!
Hi,

This happens because MS Forms doesn’t return true null values for unanswered questions. Instead, it usually sends an empty string ("").
The coalesce() function only skips null values, not empty strings. So if the first field is empty ("") it still gets picked by coalesce, and that’s why you’re getting a blank result even though one of the other fields has a value.
To fix this, you need to treat empty values as null before using coalesce. You can do that using empty().
You can update your expression like this:
coalesce(
if(empty(outputs('Hent_svardetaljer')?['body/r6be04227351c4b8f9112dc937deb47f1']), null, outputs('Hent_svardetaljer')?['body/r6be04227351c4b8f9112dc937deb47f1']),
if(empty(outputs('Hent_svardetaljer')?['body/r074d0f9054e84a8e82704eec58dffc68']), null, outputs('Hent_svardetaljer')?['body/r074d0f9054e84a8e82704eec58dffc68']),
if(empty(outputs('Hent_svardetaljer')?['body/r07ef740bfdda41bfa66828ddc146566e']), null, outputs('Hent_svardetaljer')?['body/r07ef740bfdda41bfa66828ddc146566e']),
if(empty(outputs('Hent_svardetaljer')?['body/rb57d2aa0415c4334a70aab13caa9b1f4']), null, outputs('Hent_svardetaljer')?['body/rb57d2aa0415c4334a70aab13caa9b1f4']),
if(empty(outputs('Hent_svardetaljer')?['body/r289529363b7e4874b8fcdc103fb92782']), null, outputs('Hent_svardetaljer')?['body/r289529363b7e4874b8fcdc103fb92782'])
)
What this does is simple:

If a field is empty, it turns it into null

Then coalesce skips it and moves to the next one

Finally it returns the one field that actually has a value

So the issue is not with coalesce, it’s just that the inputs are empty strings, not null.
Hope this helps.

Thanks!

Inogic

Was this reply helpful? Yes No
DF-16031344-0 5 on at

Like (0)

Report
Copy link

Link copied!

Thanks a lot, Inogic! Your solution worked.

Was this reply helpful? Yes No