Multiple arrays in a single object in Microsoft Flow

I get many questions about Microsoft Flow related to the many posts that I have written in recent years. Earlier this week I got a question related to my posts Do you process your invoices with Microsoft Flow and DocParser? One of my followers found that when the multiple pages are scanned the format of the  json returned by the service becomes difficult to handle with multiple arrays in a single json object.

The multi page output from DocParser

The first thing I did was to ask for some screenshots and then I asked for an example of the json that is returned by DocParser

The json that was given looked like this:

{
"id": "",
"document_id": "",
"remote_id": "",
"file_name": "20091109_0002.pdf",
"media_link": "https://api.docparser.com/v1/document/media/",
"media_link_original": "https://api.docparser.com/v1/document/media/",
"media_link_data": "https://api.docparser.com/v1/document/media/",
"page_count": 5,
"uploaded_at": "2019-05-09T16:11:34+00:00",
"processed_at": "2019-05-21T14:50:00+00:00",
"letter_date": [
{
"letter_date": "30-Apr-2019"
},
{
"letter_date": "30-Apr-2019"
},
{
"letter_date": "30-Apr-2019"
},
{
"letter_date": "30-Apr-2019"
},
{
"letter_date": "30-Apr-2019"
}
],
"paid_date": [
{
"pay_date": "02-May-2019"
},
{
"pay_date": "02-May-2019"
},
{
"pay_date": "02-May-2019"
},
{
"pay_date": "02-May-2019"
},
{
"pay_date": "02-May-2019"
}
],
"inv_using_token": [
{
"inv_number": "INV-09-B3H8B3"
},
{
"inv_number": "INV-08-D1C7W2"
},
{
"inv_number": "INV-09-R3L9M3"
},
{
"inv_number": "INV-09-D1Z6X1"
},
{
"inv_number": "INV-04-P0N1H7"
}
],
"gpr_using_token": [
{
"gpr": "GPR0000"
},
{
"gpr": "GPR0000"
},
{
"gpr": "GPR0000"
},
{
"gpr": "GPR0000"
},
{
"gpr": "GPR0000"
}
],
"amount_formated": [
{
"amount_paid": "1"
},
{
"amount_paid": "1"
},
{
"amount_paid": "1"
},
{
"amount_paid": "6"
},
{
"amount_paid": "6"
}
],
"student_name": [
{
"student_name": "XXXXXXX"
},
{
"student_name": "XXXXXXX"
},
{
"student_name": "XXXXXXX"
},
{
"student_name": "XXXXXXX"
},
{
"student_name": "XXXXXXX"
}
]
}

 

Ok, this means that we have multiple arrays within the single object.

My simulation of multiple arrays

To simulate the situation I copied the json that I was given in a Compose action. then I parsed the json with the Parse JSON  action and finally I created a Compose and included some of my data.

Multiple Apply to Each

Ok, this is not what we want! Just because I added 3 of my fields I shouldn’t get multiple apply to each steps.

How do we solve this?

Reshape the json data

We will have to adjust the data here. Changing the way the service works will be difficult Therefore I’;m going to convert the data to the following format:

[
{
"letter_date": {
"pay_date": "02-May-2019"
},
"pay_date": {
"letter_date": "30-Apr-2019"
}
},
{
"letter_date": {
"pay_date": "02-May-2019"
},
"pay_date": {
"letter_date": "30-Apr-2019"
}
},
{
"letter_date": {
"pay_date": "02-May-2019"
},
"pay_date": {
"letter_date": "30-Apr-2019"
}
}
]

Creating some helpful variables

I’m going to start by creating 4 variables

MyArray and MyArray2 contain all the letter dates and the paid dates. I’m only going to look at two of the fields for simplicity sake. But you could follow the same pattern with all the fields.

As I’ve got a page count I’m going to loop through my arrays until I’ve processed all of my items in the arrays.

Looping through the arrays

Within the loop I’m building up the json for each of my pages:

Once  the loop has completed my Pages variable now has all the data as I need it.

 

 

 

 

Advertisements

Please leave a comment or feedback

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.