Logic apps replace "null" with "" or vica versa

user_4361060 0
Viewed 266

I'm trying to compare the data in dataverse with a Excel file to see whether the dataverse rows needs to be updated or not.

I'm using compose on each item from dataverse and a combination of compose, json parse and select to create two output values that I can compare to see if they match or not. I have to do this because as far as I can tell Logic Apps has no sane way of comparing data otherwise.

The outputs are working as intended the only problem is that fields with no value from dataverse show up as "name": "null" and the Excel data with no value shows up as "name": "" which causes my compare to fail.

Is there any to easily replace all "null" or "" with either "" or "null"?

Dataverse "output" example

[
  {
    "name": "john"
    "last": "doe"
    "address": "null"
  }
]

Excel "output" example

[
  {
    "name": "john"
    "last": "doe"
    "address": ""
  }
]

The actual data has many more fields so I would prefer to somehow just look over the whole "output" and match anything that is either "" or "null" and replace it with something and not go over every possible field one by one.

azure-logic-apps
3 Answers

I found a "solution".

if(empty(items('myAction')?['myActionField']), '', items('myAction')?['myActionField'])

This checks if the field is empty or not. If its empty, the value '' is assigned thus creating a (empty) string type field. If its not empty, the original value is applied.

The downside is that this cannot possible be very efficient and you have to do this for every field so if you have a lot of fields it takes a lot of time. In my case I "only" have 30 or so so its still somewhat manageable.

A better way would be if you could just loop over a "value" array and remove/convert all null values in one go. Or create a mapping that tells logic apps what field type a field so be. But I don't know if that is possible.

user_43610600

Logic Apps support a lot of expressions. One set of expressions they support are String functions. And one of those functions is Replace(text, oldText, newText).

If the value you're trying to replace is an actual string holding "null" as its value, try this expression to replace null with an empty string:

replace(output, 'null', '')

Logic Apps string functions

Logic App string functions

user_19455250

As you are using a Compose action anyway, instead of getting a dynamic content for your address field directly, you can just use the if and equals functions in an expression, something like if(equals(__your_source_field__, 'null'), '', __your_source_field__).

enter image description here

Then the output of the Compose action won't have "null" values, and you can proceed with comparison as requried.

user_6243680
Related