string array from json

[Anonymous]

Hello everyone, 

I am trying to import a nested json file and later on compare the receipt-number with another number from an rather large excel file. In the end I want to send a Mail if any receipt number is missing in my excel file.

What I've done so far is importing my json file and parsing it. I tried to compare the values by using apply to each, but the comparison does not seem to work. I suppose that's because I would need an array with strings, but I don't really know to to get there. Any advices would be greatly appreciated!

My file looks like this:
{
"infos": {
"numPages": 1
},
"entities": {
"service ": {
"date": "26.12.2020",
"currency": "EUR",
},
"receipt": [
{
"number": "1218182",
"datum": "20.12.2020",
"betrag": "977,34",
"fälligkeit": "20.12.2020"
},
{
"number": "38272",
"datum": "02.12.2020",
"betrag": "5621,58",
"fälligkeit": "02.12.2020"
},
{
"number": "39328",
"datum": "26.12.2020",
"betrag": "1817",
"fälligkeit": "26.12.2020"
},
{
"number": "2181",
"datum": "02.12.2020",
"betrag": "1092",
"fälligkeit": "16.12.2020"
},
{
"number": "161728",
"datum": "02.12.2020",
"betrag": "1839",
"fälligkeit": "16.12.2020"
},
{
"number": "82726",
"datum": "03.12.2020",
"betrag": "1023",
"fälligkeit": "17.12.2020"
},
{
"number": "635272",
"date": "03.12.2020",
"betrag": "123",
"fälligkeit": "17.12.2020"
}
]
}
}

[yvett]

Hi Fabian,

I've had a similar issue a few days ago, and the only way i found around it was with a few loops and conditions. 

First, I put the whole json code into a "compose", and save its output into a string variable. I also initialize 2 arrays, the first one we get from the "text" string variable, but I split it at the character '{'; the second one i leave empty:

Step 1

 For step two, i loop through all of the items in the "array", and only select the items that contain "number" in them. I set these items to my "text variable and then split them by the ',' character to be able to loop through them again. 

Step 2

Once I have got the array, i loop through its items once more, and only select the ones that contain "number" (once more!) the value now should look like ""number": "1218182"" i set the text to this value, and split it by the ":" character

Step 3

 Finally, i loop through my array (that should only contain 2 items) and only select the item that does not contain "number" and append it to the array of "final_numbers"

Step 4

I know it looks awkward and can take a while to go through it, but make sure to enable concurrency on your loops and it'll go significantly quicker. I hope it works!

As a confirmation, after these nested loops, I checked the output by simply setting the first array to the array of outcomes. once you run it, this should only include the numbers you wanted. 

check

From here on, you should be able to work out the next step to compare the numbers. 🙂 

Kind regards, 

Yvett 

[Anonymous]

Hi Yvett, 

Thanks for sharing your solution! I tried it immediately. I ran into one issue I don't really get. Within the first loop the current items seems to change. In the end I get an array that consists either of 12 entries of the same number, or several repetions of 2. 

Checking the output I found that within the first loop, the split function does something weird. Insted of using the Input text, that refers to the numbers in correct order, it take some other entry from my json file. Did you run in the same issue?  

Again, thanks a lot!

Best, 

Fabian 

[Paulie78]

I think you need a slightly different approach. It would be helpful if you could screenshot your entire flow. 

In short terms you need to:

1. Read the Excel file once only at the start of the flow.
2. Extract the array from your JSON, which is simply to do and I can show you how if you post your current flow.
3. Loop over that array.
   1. Within the loop you will filter the Excel data based on the current iteration of the loop array.
   2. Check the length of the filter, if it is zero then the entry is missing.
   3. If it was greater than zero, add the current loop item to a new array.
4. At the end of the loop you will have an array containing only the missing entries.

Does that make sense?

[Anonymous]

Hi @Paulie78 , 

I attach the flow and provide some explanation: 

1) I import the the json file from my sharepoint 

2) I initialize a variable to pass it to the parse Json function  

3) I parse the json formatted data

4) In the next step I import the excel file. the column reference is supposed to be used for my later comparison 

5) I use the function select, to select exactly this column 

6) I create two arrays that I use to test whether my comparison works

7) I loop through each 'number_ element which is nested within the 'rechnung' array. My condition is: If the output of the select function (which is the reference column from my excel) contains the current element.. 
😎 If the condition is true, I append the current elemt to the test array match. If it's false, I append it to the test array no match. 

9) In the last step I simply compose those test arrays

Unfortunately I get an error message, because the apply to each function requires a different input format. That's why I was wondering how to extract the number information in a format that I could use as Input in my apply to each function.
Your suggestion is in general correct, but I don't need to check whether the arrays match in total, but whether each single number-entry in my json file is listed in the excel file. 

I would be really greatful for some advice! Thanks in advance!

Best, 

Fabian

[yvett]

Hi Fabian, 

No, unfortunately I didn't get any errors, I copy pasted the code you had in the original post and did exactly what's on the pictures, i got the right values, however, i can see that yours is splitting it at a different place than mine, could it be the input is somewhat different? Are you splitting it the same way? If you have concurrency turned on, that can mess with things if it's not correct, try maybe without it first? 

I hope you got it working over the weekend! 🙂 

Kind regards, 
Yvett 

[Paulie78]

It's really difficult to explain, but you do not need the multiple apply/each loops. You simply need to go into your apply/each loop with your "number_ element". Then instead of looping through each Excel row to see if there is a match, you simply filter the Excel data using the "filter array" function.

If there is a match, the length of that filter array will be greater than zero, if there is no match it will be zero. By using this method your flow will be much faster, and much more simple.