Child Flow cannot have concurrency, so parent flow is timing out

Susan Walters

I'm trying to build a flow that generates our monthly report, which is essentially a very large gifts list, divided into 11 different subheads. In my parent flow, I separate out each group, then send that group to a Child flow which is looking up each gift and converting the Campaign/Fund/Appeal IDs to names. Each of those IDs has to run through its own Apply to Each loop. Inside each ID loop, it's writing the code name to a variable, so the variable can be added to the gift string that's appended to the array of gifts from that group. When concurrency was on, every gift was being output with the names for the codes from the first gift in its group. When we turned concurrency off, this was solved.

I set this up as a child flow because there are 11 sections in the parent flow, and I was afraid I'd never be able to recreate the steps 11 times.

After each group comes out of the child flow, it's written to a table for it's own section, so I can arrange the sections and append them each in turn to my final document.

The problem now is that the largest group of gifts to run through the child flow is over 700 records. Based on the timing of smaller groups, the child flow is taking about 7 seconds per record. So, about 80 minutes for this group. But this flow is timing out after 10 minutes.

Is there any way to solve this problem?

Alex Wong

@Susan Walters
your flow seems to be more complex than it needs to be. What you want to do is rather simple if you use Query API

Few questions:

• does your org split gifts?
• what do you mean by 11 different subheads?

Susan Walters

@Alex Wong

We don't use split gifts, but I can't guarantee that one will never appear. Our monthly report lists all the gifts in 11 sections - 4 sections are specifically by the fund ID, and the other 7 are from the all remaining gifts sorted by subtype. Each section has a subtotal and a gift count, with additional blank lines between each section. I set up the flow based on a previous flow that I built that was writing each gift to both and excel file and to an HTML table, since I knew I could work with both of those. So each section finds the gifts that belong to it, calculates that groups subtotal, and sends the gift IDs to the Child flow, which converts the C/F/A IDs to names, and formats the amount and the date, then writes it to a string and sends it back to the parent flow. Farther down in the parent flow, I assemble all the sections with the subtotal lines and blanks between sections and write them to the final spreadsheet.

I'm still very much a beginner at this, and I tend to spell out each of the steps because I don't understand the shortcuts yet.

EDIT: I haven't tried the Query API yet. The original report is actually a db view query export, but it does require manipulation to divide and subtotal.

Alex Wong

@Susan Walters
I strongly recommends you give Query API a try: there are template for both running query that is already saved in dbview OR running ad-hoc query that has programable filtering/output.

then from there, doing additional calculations can be done with power automate.

So step one, take a Query API template (since you said you have a saved query export from the original process), and run that query.

Susan Walters

@Alex Wong
I don't understand where to go from here. I've only got 4 Query API actions available. If I select Start a Query Job, and run it, I only have a few outputs available to use: JobID, Status, Message, and Body, and none of those seem to output anything from the query.

Thanks for your help.

Alex Wong

@Susan Walters
The way Query API works is:

• Execute Query
  • either using query id to execute a saved query in dbview
  • or execute adhoc query using a JSON definition that represent query info (filter, output, sort, query option, etc)
  • this step will give you a job id
• using job id, you will execute another endpoint to check status of the run
  • this step can result in success or fail, you have to consider the possibility
  • if successful, you will get a sas_uri which is the URL link that will download the result
• use the sas_uri on success run to download the result
  • result can be in CSV or JSON, or JSONL (don't worry about JSONL, you will likely better off in your case to use CSV or JSON)

in one of the query api template, you will see how all the steps is done