Syntax for returning null / int in JSON

[MPLC]

Apologies if this has been answered, I’ve searched and been unable to find a solution.

Can anyone help me with the correct syntax for returning a specific value if the value data they receive is empty?

Here is my output:

my connector for Make an API Sharepoint call…

I basically want to know how to write an if statement and return Null or an Int if the value is empty.

Thanks

[samliew]

ifempty is correct.

You want to return a null or int, but you are wrapping everything in double quotes, which makes it a string containing "null" or "0" – which is clearly not null or 0

[MPLC]

If I do…

{
“ClosedFileBox”: {{ifempty(map(2.custom_field_values; “value”; “field_name”; “Closed File Box”); 0)}},
“ClosedFileReviewDate”: {{ifempty(map(2.custom_field_values; “value”; “field_name”; “Closed File Review Date”); null)}}
}

returns
{
“ClosedFileBox”:
“ClosedFileReviewDate”
}

when it should be
{
“ClosedFileBox”: 0
“ClosedFileReviewDate”: null
}

[samliew]

Hmm, since you need to insert the quotes if the value exists, then

you need to change this

{{ifempty(map(2.custom_field_values; "value"; "field_name"; "Closed File Box"); 0)}}

into

{{if(length(map(2.custom_field_values; "value"; "field_name"; "Closed File Box")) > 0; space + """" + map(2.custom_field_values; "value"; "field_name"; "Closed File Box") + """"; 0)}}

[MPLC]

@samliew

space + “”“” +

Hey.

I’m still struggling with this. I was having issues with JSON including spaces. So the way I know I can get this to work is by changing the body to be of type Text.

The way I see it working to get past the null/int is by only sending data if it exists.
So do an if check on the value, then insert the whole json string.

Struggling with that

[MPLC]

Even simple things like an if statement is failing.

This should return Empty but is returning “full”…

:S

[samliew]

1. Yours

empty + space = space

null + space = space

space can never equal to null

So this won’t work.

2. Mine

Check if length of value is greater than zero (exists) first

If yes, wrap the same value in double quotes.

If no, return 0.

[MPLC]

Thanks for the help and apologies for the back and forth but implementing your method still isn’t giving me the desired results. I’m wondering if it’s to do with the fact I have the Type set as ‘Text’. When I had the Type set as JSON I was getting a failed JSON error, and I think that was because I wasn’t updating every field in the payload, only specific ones like Closed File Box, for example.

When I enter the following code, for the purpose of testing the length…

These are the results returned (Review Date and Box are null values)…

So for some reason it’s not getting the length correctly, I’m assuming its not looking at the string length and the… buffer size?.

Appreciate the help!

P.S, this is the code I had entered following your method hoping it would work…

[MPLC]

I think thats becuase the map function is putting the value into an array which is why it always displays as 1?
I’m guessing I would need to do something like:
“Closed File Review Date Length”: {{length(map(2.custom_field_values; “value”; “field_name”; “Closed File Review Date”) + “[0].Value”)}}

?

EDIT: I was able to fix this by putting a get in there.
{{get(map(2.custom_field_values; “value”; “field_name”; “Closed File Destruction Date”); 1)}}

[MPLC]

Final Working Solution

Far more involved than was expected …

[Michaela]

Heya @MPLC

Thanks a lot for keeping us in the loop and for sharing your final setup with the rest of the community. This is super valuable and could be incredibly helpful to many others looking for similar information.