How to change value of array if it satisfies a given condition and help with Select Action

[David59740]

So I have an array of values that are collected from a JSON using a 'Select' action. The issue is, objects from where I get the values will sometimes not exist. From what I have gathered, this will make the entire 'Select' array null even if 9/10 values have data. I came to this conclusion from this error message: "the result of the evaluation of 'foreach' expression '@body('Select')' is of type 'Null'." 

My goal is to collect the values into an array and find a way to say if any value is null in the array change the value to "not available." 

I'm trying to insert the array data into a sql table so regardless the data cannot be null in the Insert Row action.

I have been stuck on this for about 2 days now I could really use some help.

[eliotcole]

Hi, @David59740, this hopefully sounds like something which crops up a bit here (you'd do well to try a few different searches, you might find the answer, maybe even in the Recommendations to the right there! 🙂) ... but whomever assists you, here, may need a bit more information to help out.

Could you please edit your question above with screenshots of your flow, obfuscating any sensitive information?

That way someone can see where the information is coming from, how it is coming in, and how you're moulding it, if at all. 👍

[ekarim2020]

Hi @David59740 ,

May be the following example will help. The birthdate property for Tom Cruise is missing:

 Note the expression for the Birthdate in the Select action:

 if(empty(item()?['Birthdate']),'uknown', item()?['Birthdate'])

The birthdate property for Tom Cruise is missing and is replaced with the text 'unknown':

Source for the sample data: JSON Example With Data Types Including JSON Array (codeblogmoney.com)

Hope this helps.

Ellis
____________________________________
If I have answered your question, please mark the post as Solved.
If you like my response, please give it a Thumbs Up.

 

 

[David59740]

"Subtotal": {
"type": "number",
"valueNumber": 47,
"text": "$47.00",
"boundingBox": [
214,
325,
241,
324,
241,
333,
214,
334
],
"page": 1,
"confidence": 0.979
},
"Tax": {
"type": "number",
"valueNumber": 3.67,
"text": "$3.67",
"boundingBox": [
219,
336,
240,
335,
241,
344,
219,
345
],
"page": 1,
"confidence": 0.985
}

I tried your solution I still get the same error message. Take this JSON block from my data for example. I want the value number of both. The way I do this in my flow for Subtotal is item()['fields']['Subtotal']['valueNumber']   and I would get 47.  now let's pretend Subtotal is gone, my JSON would look like this:

"Tax": {
"type": "number",
"valueNumber": 3.67,
"text": "$3.67",
"boundingBox": [
219,
336,
240,
335,
241,
344,
219,
345
],
"page": 1,
"confidence": 0.985
}

Now if I try to call the value for Subtotal, I have no success even with the if statement. Here is the last error I received while trying to get a field 'MerchantName':

The template language expression 'if(empty(item()?['fields']['MerchantName']['valueString']),'Not Available',item()?['fields']['MerchantName']['valueString'])' cannot be evaluated because property 'MerchantName' doesn't exist, available properties are 'Items, MerchantAddress, MerchantPhoneNumber, ReceiptType, Subtotal, Tax, Tip, Total, TransactionDate, TransactionTime'.

[ekarim2020]

Hi @David59740 ,

Please try the expression in this format:

if(empty(item()?['fields']?['MerchantName']?['valueString']),'Not Available',item()?['fields']?['MerchantName']?['valueString'])

Please see Re: Systematics for referencing properties - Power Platform Community (microsoft.com)

for an explanation on using this syntax to reference JSON properties:

item()['fields']['MerchantName']['valueString'] 
or
item()?['fields']?['MerchantName']?['valueString']

Ellis
____________________________________
If I have answered your question, please mark the post as Solved.
If you like my response, please give it a Thumbs Up.

[David59740 · Accepted solution]

Thanks for the response! I was able to figure it out. If anyone ever runs into a similar issue I did it by initializing a string variable and set it equal to output of my HTTP activity (the JSON). I then did an if contains statement on each value. This statement says if the variable contains "Merchant Name" then set the value to the item in the JSON object, otherwise set it to "Not Available." For some reason if contains would not work on the array in the JSON without first putting it in a string variable.

if(contains(variables('JSONString'),'MerchantName'),item()['fields']['MerchantName']['valueString'],'Not Available')