Power Automate - Looping through Query results help

Rijusiddha Drewett

Hi everyone.

I am fairly new to Power Automate so am asking for some advice on a flow I am trying to create.

I am using query to get details of cancelled Direct Debit (UK) gifts and associated constituent details so I can loop through the results and send out Donor Emails with relevant fields from the Const/Gift records. Reason I am using query is because some of the fields are not query fields in the current API's or some of the output.

I have got as far as getting the query results and outputs but I am a bit stuck as the next stages to pass into a Apply to Each loop so the Send Email action can get the relevant fields per iteration and put in the email. Here is my flow so far with being able to see the results in the output of Compose.

I am following the Utilizing Financial Edge NXT Query API with Power Automate video and seems to suggest using ParseJson but the video cuts out at that point and any attempt I have tried to use ParseJson just gives errors in the flow. Although that could be a red herring! Any advice or direction much appreciated!

Carol Grant

@Rijusiddha Drewett
Hi- I can't see what is in your Compose but mine actually converts it to a string first in a compose. Also you need to make sure the System Record ID is in your query.

string(body('HTTP_-_Get_query_results'))

Then you are correct, you want to Parse Json. The content area is where you put the Outputs of the Compose. Then you want to Generate from a sample schema. Here is one I used that just gets the basics I need which is the System Record ID.

{

"type": "array",

"items": {

"type": "object",

"properties": {

"System Record ID": {

"type": "string"

},

"QRECID": {

"type": "string"

}

},

"required": [

"System Record ID",

"QRECID"

]

}

}

After the Parse Json you should be able to do Get a Constituent and pull in the Parse Json field System Record ID and then it will do an Apply to Each.

Alex Wong

@Rijusiddha Drewett
This depends on if you run the query with "output_format": "JSON" or not.

If you don't specify, the default is CSV. So the option you want to use will depend on size.

if you expect a LOT of data will come from the running of the query, use CSV, CSV uses less “bytes”. JSON uses more. If you don't expect hundreds of thousands records, then use JSON, as it is easier to work with in the remaining part of your flow.

Assuming you use JSON, then you can run your flow, and go into run history.

In the HTTP action to download, you should see something like this:

or your output can be like below if there are too much data, but if you “Click to download” it will show you data same as above.

You can see that the “content” of your download is in the $content property of the HTTP body JSON. it is “base 64 encoded”. so you will need to “decode” it before it can be used. There are 2 ways to do the decoding, one of it is mentioned by Carol already.

You can also directly use the $content dynamic content without “Compose”/String() first, but will use to use the base64ToString expression:

base64ToString(body('HTTP_Get_Query_Result')?['$content'])

{

"type": "array",

"items": {

"type": "object",

"properties": {

"Output Column 1": {},

"Output Column 2": {},

"Output Column 3": {}

}

}

}

In the Schema, you don't have to have each column data type explicitly defined, so meaning, using the above Schema, and just add more “output Column” name is fine.

Rijusiddha Drewett

@Alex Wong Brilliant! Thanks for that missing piece. Works perfectly and flow now sending test emails with the information needed. JSON was fine as the query only looks at Direct Debit gifts cancelled the previous day and the flow will be scheduled for once every morning so very few results expected.

Rijusiddha Drewett

@Carol Grant Thanks Carol all working now and thanks for the System ID tip. I dont need to make any further RE calls at present as all the fields are in the query output but still good to pull through as the flow might develop and at least its there. ?