Reformatting data in flow is one of those critical patterns. Taking multiple arrays and reshaping them can be a challenge.
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.
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.
