cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
robertopolo
Advocate I
Advocate I

3 Json in one

Hello I hope you are well. I require your help in a challenge that is costing me more than I thought. I am doing 3 HTTP queries that return a JSON as a response with the same structure for all 3 queries. The challenge I have is that I need the 3 queries to be a single one and then send them to Powerapps and I have not managed to get a single JSON that I can send to the application and store it in a collection. I don't know how to join all those queries in a single JSON. Could someone give me a hand?

 

That each query is performed within a Do until, that is, each query must be stored in a variable or something to get all the queries at the end of the "Do until" loop.

 

I apologize for my english.

1 ACCEPTED SOLUTION

Accepted Solutions
robertopolo
Advocate I
Advocate I

Hey, i finally found the solution. I will try to explain what I did:

  • The flow is triggered from Powerapps.
  • I have created 3 variables, 2 of type integer and 1 of string.

robertopolo_0-1620755628754.png

  • The accounting system separates the information into pages so the query must be repeated, as many pages with information exist. One of the integer variables that I created at the beginning has the number 0 as a value, which would be the first page to consult and each iteration increases an Ex number: 1,2,3...
  • Within each "Do until" cycle I make an HTTP query to the accounting system.

robertopolo_1-1620755661766.png

 

  • The second variable stores the length of the query response, if the length of the response is 0, it means that there is no information on that page and the "Do until" loop stops.
  • If the length is not 0, it is because the query generated information and stores the query response in the "String" type variable created at the beginning using the "Append to string variable" action.

robertopolo_2-1620755685791.png

robertopolo_3-1620755727811.png

 

  • The flow ends with the action "Response" and in the body I add the following expression: replace (replace (variables ('String'), '] [', ''), '} {', '}, {') where "String" is the string variable created at startup and where I stored the responses of the HTTP request sent. I remove the brackets and curly braces to not get comma separated arrays but a single object like the following:

robertopolo_4-1620755742714.png

  • The result of the flow helps me to create a collection within Powerapps without the need for additional operations within the application.

It may not be the most orthodox solution, but it works for me for the moment.

View solution in original post

7 REPLIES 7
annajhaveri
Super User
Super User

@robertopolo you can do following:

 

1. Use initialize variable action to define Array variable

2. Inside do until loop add "Append to array variable", select the array variable and select the response json in the value parameter

3. Use Join action after the do until (outside of it) and select the array variable and use , as the join with parameter value, this will give you comma separated json responses as output of Join action

4. Return the output of Join action to PowerApp using "Respond to a PowerApp or flow" action. 

Regards,
Anna Jhaveri

If I have answered your question, please mark the post as Solved to help other users to identify the correct answer
If you like my response, please give it a Thumbs Up.

The response of the HTTP request is a JSON and when adding it as a parameter for the action "Append to array variable" I get an error saying that the action does not accept the data type.

robertopolo_0-1620748503247.png

 

eliotcole
Impactful Individual
Impactful Individual

Questions

A few questions/info requests, then an assumptive answer 😉.

  1. Do you just want one submission to the Powerapp with all the data?
  2. Or do they need to be merged into a single JSON object with information in a specific order?
  3. Can you show us your flow?
    ( block private stuff, and edit as much info as possible into the original question )
  4. If this is complex, some sample data could help anyone making a solution.
    ( you can make it up, but ensure the structure and data types are the same )

 

Potential Solutions

Purely going on what you've said thus far, I've got two ways you could progress, with relative ease ( I think 😞

 

Blind Date(a)

If you just need to get all their data into the Powerapp in one submission, then

  1. Create an array variable, eg. "arrayVAR".
  2. Append each identical JSON response as an object to arrayVAR.

Now you have a JSON array of all the data to send to the Powerapp which can process it.

 

Objectification

If you need to construct a whole new JSON object, then the easiest way to do that is as logically as possible, so ( again, purely on what I can see here ) I would:

  1. Run a parallel branch for each API pull.
  2. Set variables ( with appropriate types ) for *everything* you need.
  3. Build a compose or string variable at the end ( joining the branches ) that inserts the variables into the new structure.
  4. Send the JSON to the Powerapp.

This won't be the end of your dealings with the flow, because I'm sure you'll see how it can be done more efficiently once you start seeing how data can be 'shortcutted' and worked with within the flow. However doing it this way will allow you to plot out the logic, easily, in your head before going down that road.

 

Hope this helps, and good luck! 👍

Thanks for your answer. Answering your questions:

I want to send all the information from the queries to powerapps and create a collection with this information.

All queries return a response with the following structure:

robertopolo_0-1620750952610.png

 

The flow has a "Do until" cycle since the system I am querying separates the information by pages and I must go through all the pages (0,1,2,3) until one of them has no information. Each query is a separate JSON in the response but I need them as a single array to create a collection with all the information in powerapps.

robertopolo_1-1620750985984.png

 

eliotcole
Impactful Individual
Impactful Individual

Another question, sorry, but where is the definition of "no information" in there?

 

Is it that your flow will continue to submit queries until the response is empty?

 

---

 

Either way, in this case I would:

  1. Make 4 Arrays - Create four separate arrays (arrayOneVAR, arrayTwoVAR, etc) for each page.
  2. Process Separately - Append to them respectively for each page in four parallel branches, until you get the notification that it has no more information.
  3. Merge The Arrays - Then at the end create a final array with union(variables('arrayOneVAR'), variables('arrayTwoVAR'), variables('arrayThreeVAR'), variables('arrayFourVAR'))
  4. To PowerApp! -  Then send that array to PowerApps. 👍

 

This would also illiminate any repeated array entries in the data.

Yes it is. The "Do until" cycle will be repeated until the query does not return results, which is currently after the third execution.

robertopolo
Advocate I
Advocate I

Hey, i finally found the solution. I will try to explain what I did:

  • The flow is triggered from Powerapps.
  • I have created 3 variables, 2 of type integer and 1 of string.

robertopolo_0-1620755628754.png

  • The accounting system separates the information into pages so the query must be repeated, as many pages with information exist. One of the integer variables that I created at the beginning has the number 0 as a value, which would be the first page to consult and each iteration increases an Ex number: 1,2,3...
  • Within each "Do until" cycle I make an HTTP query to the accounting system.

robertopolo_1-1620755661766.png

 

  • The second variable stores the length of the query response, if the length of the response is 0, it means that there is no information on that page and the "Do until" loop stops.
  • If the length is not 0, it is because the query generated information and stores the query response in the "String" type variable created at the beginning using the "Append to string variable" action.

robertopolo_2-1620755685791.png

robertopolo_3-1620755727811.png

 

  • The flow ends with the action "Response" and in the body I add the following expression: replace (replace (variables ('String'), '] [', ''), '} {', '}, {') where "String" is the string variable created at startup and where I stored the responses of the HTTP request sent. I remove the brackets and curly braces to not get comma separated arrays but a single object like the following:

robertopolo_4-1620755742714.png

  • The result of the flow helps me to create a collection within Powerapps without the need for additional operations within the application.

It may not be the most orthodox solution, but it works for me for the moment.

View solution in original post

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Users online (2,499)