Sequential Concurrent ForAll in Power Apps
In this blog post I will give detailed instructions for creating a Sequential Concurrent ForAll in Power Apps. TLDR?
I have used this ‘Sancho method’ to reduce the loading time of an App from 4 minutes to 15 seconds in a previous project I took over.
What a mouthful! Stay with me, as this is going to be a sorcery-level experience!
Now, you may be thinking:
“Why would I need that when I can just use ForAll“?
Why? 🤔
Well, let me tell you a story about an App. This App was taking more than 4 minutes to get past the loading screen due to it needing to LookUp and store all values for each item in a many-to-many relationship, which it was doing via a ForAll.
This, of course, is an unacceptable amount of time for anyone to wait for an App to get past its first loading screen, so when I was brought on to look at this App I knew I had to try and see if I could improve that user experience.
Being the inquisitive person I am, I tried to figure out whether there was a better way to do this, and why it was taking so damn long!
(“There is always a better way!“)
The App was cycling through the related items using ForAll, so for our example I’m going to use:
ForAll(Questions As ThisQuestion,
Collect(colPotentialQuestionAnswerPairsForAll,
{
Question: ThisQuestion,
Answers: LookUp(
Questions,
Questions[@meow_questionid] = ThisQuestion.meow_questionid
).Answers
}
)
);In this case we have two Tables, Questions and Answers, A Question can have multiple different Answers that are pulled from a Choice column in the Answers Table. In this same line of thought, each Answer can be attributed to multiple to different Questions. The Answers table also has other columns with information we will need, and the Questions Table has other Columns in it that we need the data from, and what we need to know in the case of each Question is which particular set of Answers relates to it.
Now I know there will be some of you thinking: “well, actually, you should have the ForAll inside the collect” – That was tried, and resulted in similar times because the issue is the LookUp within a many-to-many relationship and the fact that ForAll is singular sequential and not concurrent, so whether outside or inside it still needs to look up each item from a large many-to-many relationship.
Some others of you might also be thinking: “well why don’t you reference these items directly?” – The reason we couldn’t reference these directly is that we need these to populate dropdowns and create new rows while offline as they are Dataverse choice columns with multiple other columns of data for each question and answer that needs to be surfaced and used while offline, so they need to be stored in their entirety to be used while offline (for up to a month offline!).
The answer is that ForAll runs sequentially, it looks at only one record at a time, gets the result, then continues to the next until it has gone through all records in its scope.
We could potentially wrap the ForAll in a Concurrent, but then we need to know what size to make the divisions so that the Concurrent ForAlls don’t overlap, which introduces our next problem – ForAll is NOT A DELEGABLE FUNCTION – this means that if our list of related records we are trying to achieve is over 2000 items (the current upper limit allowed in Power Apps for delegation), and we need the entire list, then we will have to split it up.
The sorcery moment 🧙♀️🧙♂️
I spent quite some time playing around with this and eventually (as with all crazy ideas) I had a moment of madness and thought ‘What If?’
What if, I use a sequence, then iterate through all items in the sequence, but instead of doing one item per sequence, I use a Concurrent to action MULTIPLE items per sequence using the sequence numbers as a base unit reference?
…
The implementation of a Sequential Concurrent ForAll 🚧
Ok, so how do we go about creating this Sequential Concurrent ForAll?
TLDR (Too Long Didn’t Read):
I will give you the structural pseudocode for this here and you can adapt it for your own needs, then I will continue with a detailed example below that and follow that with the results of a very small test.
Pseudocode
ForAll(
// Currently using 1000 sequence items referencing 20 concurrent threads,
// resulting in 20000 checks - You could increase this sequence amount
// for any number of potential records as each action is an individual lookup
// 20k is already more than you should be pulling into any single App
Sequence(1000,0,1)
,
With(
{
ThisIteration: ThisRecord.Value
},
Concurrent(
// Check if there's a record with this row number
If(
LookUp(colNumberedQuestions,
RowNumber = (ThisIteration*20 + 0)).RowNumber = (ThisIteration*20 + 0)
,
//Then Do something like collect a record by using (ThisIteration*20 + 0))
)
,
If(
LookUp(colNumberedQuestions,
RowNumber = (ThisIteration*20 + 1)).RowNumber = (ThisIteration*20 + 1)
,
//Then Do something like collect a record by using (ThisIteration*20 + 1))
)
,
etc. etc. repeating this formula for the following concurrent rows until 19
If(
LookUp(colNumberedQuestions,
RowNumber = (ThisIteration*20 + 19)).RowNumber = (ThisIteration*20 + 19)
,
//Then Do something like collect a record by using (ThisIteration*20 + 19))
)
)
)
)Detailed example of a Sequential Concurrent ForAll
In this particular case, we have no row numbers for our questions to iterate through, so we are going to start by getting the list of Questions into a collection and numbering them.
You will need to adapt the collecting of the initial Questions (One side of the Many-to-many relationship) to fit your needs and may need to change how those questions are collected to fit within the delegable limits (i.e. this could be batches of 2000 items collected into a larger collection)
ClearCollect(colQuestionList, Questions);
ClearCollect(
colNumberedQuestionGUIDS,
Ungroup(
ForAll(
Sequence(CountRows(colQuestionList)),
{
QuestionGUID: (Last(FirstN(colQuestionList,Value))).meow_questionid,
RowNumber: Value
}
),
"QuestionGUID"
)
);Ok, so now we have a list we can iterate through by using the row number, and then perform multiple concurrent lookups with every loop. You may notice I used 20 concurrent actions, this is because on lower-end connections anything more than 20 simultaneous concurrent lookups started to impact performance (30 is the maximum that a concurrent can do, 20 is the sweet spot!)
This is going to be a whole example chunk of code, which repeats 20 times within the Concurrent call if you don’t have time to look at it then SKIP TO THE RESULTS
//Concurrently get 20 per cycle for all remaining
// of potentially 20*sequence items, starting at 20 (1*20) until 2000 (100*20)
//This gets all possible Answers to questions as Question-Answer pairs
ForAll(
Sequence(100,0,1)
,
With(
{
ThisIteration: ThisRecord.Value
},
Concurrent(
If(
// Check if this number exists in list
LookUp(colNumberedQuestionGUIDS,
RowNumber = (ThisIteration*20 + 0)
).RowNumber = (ThisIteration*20 + 0)
,
// Define Question once for this reference
With(
{
ThisQuestion0:
LookUp(
colQuestionList,
colQuestionList[@meow_questionid]
= LookUp(
colNumberedQuestionGUIDS,
RowNumber = (ThisIteration*20 + 0)
).QuestionGUID
)
},
// Collect into individual collections to not break concurrency
Collect(
colNumbersPossibleAnswers0,
{
Question: ThisQuestion0,
// Get relationship-referenced object
PossibleAnswers: ThisQuestion0.meow_Question_Answers_Rel
}
)
)
)
,
If(
// Check if this number exists in list
LookUp(colNumberedQuestionGUIDS,
RowNumber = (ThisIteration*20 + 1)
).RowNumber = (ThisIteration*20 + 1)
,
// Define Question once for this reference
With(
{
ThisQuestion1:
LookUp(
colQuestionList,
colQuestionList[@meow_questionid]
= LookUp(
colNumberedQuestionGUIDS,
RowNumber = (ThisIteration*20 + 1)
).QuestionGUID
)
},
Collect(
// Collect into individual collections to not break concurrency
colNumbersPossibleAnswers1,
{
Question: ThisQuestion1,
// Get relationship-referenced object
PossibleAnswers: ThisQuestion1.meow_Question_Answers_Rel
}
)
)
)
,
If(
// Check if this number exists in list
LookUp(colNumberedQuestionGUIDS,
RowNumber = (ThisIteration*20 + 2)
).RowNumber = (ThisIteration*20 + 2)
,
// Define Question once for this reference
With(
{
ThisQuestion2:
LookUp(
colQuestionList,
colQuestionList[@meow_questionid]
= LookUp(
colNumberedQuestionGUIDS,
RowNumber = (ThisIteration*20 + 2)
).QuestionGUID
)
},
Collect(
// Collect into individual collections to not break concurrency
colNumbersPossibleAnswers2,
{
Question: ThisQuestion2,
// Get relationship-referenced object
PossibleAnswers: ThisQuestion2.meow_Question_Answers_Rel
}
)
)
)
,
If(
// Check if this number exists in list
LookUp(colNumberedQuestionGUIDS,
RowNumber = (ThisIteration*20 + 3)
).RowNumber = (ThisIteration*20 + 3)
,
// Define Question once for this reference
With(
{
ThisQuestion3:
LookUp(
colQuestionList,
colQuestionList[@meow_questionid]
= LookUp(
colNumberedQuestionGUIDS,
RowNumber = (ThisIteration*20 + 3)
).QuestionGUID
)
},
Collect(
// Collect into individual collections to not break concurrency
colNumbersPossibleAnswers3,
{
Question: ThisQuestion3,
// Get relationship-referenced object
PossibleAnswers: ThisQuestion3.meow_Question_Answers_Rel
}
)
)
)
,
If(
// Check if this number exists in list
LookUp(colNumberedQuestionGUIDS,
RowNumber = (ThisIteration*20 + 4)
).RowNumber = (ThisIteration*20 + 4)
,
// Define Question once for this reference
With(
{
ThisQuestion4:
LookUp(
colQuestionList,
colQuestionList[@meow_questionid]
= LookUp(
colNumberedQuestionGUIDS,
RowNumber = (ThisIteration*20 + 4)
).QuestionGUID
)
},
// Collect into individual collections to not break concurrency
Collect(
colNumbersPossibleAnswers4,
{
Question: ThisQuestion4,
// Get relationship-referenced object
PossibleAnswers: ThisQuestion4.meow_Question_Answers_Rel
}
)
)
)
,
If(
// Check if this number exists in list
LookUp(colNumberedQuestionGUIDS,
RowNumber = (ThisIteration*20 + 5)
).RowNumber = (ThisIteration*20 + 5)
,
// Define Question once for this reference
With(
{
ThisQuestion5:
LookUp(
colQuestionList,
colQuestionList[@meow_questionid]
= LookUp(
colNumberedQuestionGUIDS,
RowNumber = (ThisIteration*20 + 5)
).QuestionGUID
)
},
// Collect into individual collections to not break concurrency
Collect(
colNumbersPossibleAnswers5,
{
Question: ThisQuestion5,
// Get relationship-referenced object
PossibleAnswers: ThisQuestion5.meow_Question_Answers_Rel
}
)
)
)
,
If(
// Check if this number exists in list
LookUp(colNumberedQuestionGUIDS,
RowNumber = (ThisIteration*20 + 6)
).RowNumber = (ThisIteration*20 + 6)
,
// Define Question once for this reference
With(
{
ThisQuestion6:
LookUp(
colQuestionList,
colQuestionList[@meow_questionid]
= LookUp(
colNumberedQuestionGUIDS,
RowNumber = (ThisIteration*20 + 6)
).QuestionGUID
)
},
// Collect into individual collections to not break concurrency
Collect(
colNumbersPossibleAnswers6,
{
Question: ThisQuestion6,
// Get relationship-referenced object
PossibleAnswers: ThisQuestion6.meow_Question_Answers_Rel
}
)
)
)
,
If(
// Check if this number exists in list
LookUp(colNumberedQuestionGUIDS,
RowNumber = (ThisIteration*20 + 7)
).RowNumber = (ThisIteration*20 + 7)
,
// Define Question once for this reference
With(
{
ThisQuestion7:
LookUp(
colQuestionList,
colQuestionList[@meow_questionid]
= LookUp(
colNumberedQuestionGUIDS,
RowNumber = (ThisIteration*20 + 7)
).QuestionGUID
)
},
Collect(
// Collect into individual collections to not break concurrency
colNumbersPossibleAnswers7,
{
Question: ThisQuestion7,
// Get relationship-referenced object
PossibleAnswers: ThisQuestion7.meow_Question_Answers_Rel
}
)
)
)
,
If(
// Check if this number exists in list
LookUp(colNumberedQuestionGUIDS,
RowNumber = (ThisIteration*20 + 8)
).RowNumber = (ThisIteration*20 + 8)
,
// Define Question once for this reference
With(
{
ThisQuestion8:
LookUp(
colQuestionList,
colQuestionList[@meow_questionid]
= LookUp(
colNumberedQuestionGUIDS,
RowNumber = (ThisIteration*20 + 8)
).QuestionGUID
)
},
Collect(
// Collect into individual collections to not break concurrency
colNumbersPossibleAnswers8,
{
Question: ThisQuestion8,
// Get relationship-referenced object
PossibleAnswers: ThisQuestion8.meow_Question_Answers_Rel
}
)
)
)
,
If(
// Check if this number exists in list
LookUp(colNumberedQuestionGUIDS,
RowNumber = (ThisIteration*20 + 9)
).RowNumber = (ThisIteration*20 + 9)
,
// Define Question once for this reference
With(
{
ThisQuestion9:
LookUp(
colQuestionList,
colQuestionList[@meow_questionid]
= LookUp(
colNumberedQuestionGUIDS,
RowNumber = (ThisIteration*20 + 9)
).QuestionGUID
)
},
Collect(
// Collect into individual collections to not break concurrency
colNumbersPossibleAnswers9,
{
Question: ThisQuestion9,
// Get relationship-referenced object
PossibleAnswers: ThisQuestion9.meow_Question_Answers_Rel
}
)
)
)
,
If(
// Check if this number exists in list
LookUp(colNumberedQuestionGUIDS,
RowNumber = (ThisIteration*20 + 10)
).RowNumber = (ThisIteration*20 + 10)
,
// Define Question once for this reference
With(
{
ThisQuestion10:
LookUp(
colQuestionList,
colQuestionList[@meow_questionid]
= LookUp(
colNumberedQuestionGUIDS,
RowNumber = (ThisIteration*20 + 10)
).QuestionGUID
)
},
Collect(
// Collect into individual collections to not break concurrency
colNumbersPossibleAnswers10,
{
Question: ThisQuestion10,
// Get relationship-referenced object
PossibleAnswers: ThisQuestion10.meow_Question_Answers_Rel
}
)
)
)
,
If(
// Check if this number exists in list
LookUp(colNumberedQuestionGUIDS,
RowNumber = (ThisIteration*20 + 11)
).RowNumber = (ThisIteration*20 + 11)
,
// Define Question once for this reference
With(
{
ThisQuestion11:
LookUp(
colQuestionList,
colQuestionList[@meow_questionid]
= LookUp(
colNumberedQuestionGUIDS,
RowNumber = (ThisIteration*20 + 11)
).QuestionGUID
)
},
Collect(
// Collect into individual collections to not break concurrency
colNumbersPossibleAnswers11,
{
Question: ThisQuestion11,
// Get relationship-referenced object
PossibleAnswers: ThisQuestion11.meow_Question_Answers_Rel
}
)
)
)
,
If(
// Check if this number exists in list
LookUp(colNumberedQuestionGUIDS,
RowNumber = (ThisIteration*20 + 12)
).RowNumber = (ThisIteration*20 + 12)
,
// Define Question once for this reference
With(
{
ThisQuestion12:
LookUp(
colQuestionList,
colQuestionList[@meow_questionid]
= LookUp(
colNumberedQuestionGUIDS,
RowNumber = (ThisIteration*20 + 12)
).QuestionGUID
)
},
Collect(
// Collect into individual collections to not break concurrency
colNumbersPossibleAnswers12,
{
Question: ThisQuestion12,
// Get relationship-referenced object
PossibleAnswers: ThisQuestion12.meow_Question_Answers_Rel
}
)
)
)
,
If(
// Check if this number exists in list
LookUp(colNumberedQuestionGUIDS,
RowNumber = (ThisIteration*20 + 13)
).RowNumber = (ThisIteration*20 + 13)
,
// Define Question once for this reference
With(
{
ThisQuestion13:
LookUp(
colQuestionList,
colQuestionList[@meow_questionid]
= LookUp(
colNumberedQuestionGUIDS,
RowNumber = (ThisIteration*20 + 13)
).QuestionGUID
)
},
Collect(
// Collect into individual collections to not break concurrency
colNumbersPossibleAnswers13,
{
Question: ThisQuestion13,
// Get relationship-referenced object
PossibleAnswers: ThisQuestion13.meow_Question_Answers_Rel
}
)
)
)
,
If(
// Check if this number exists in list
LookUp(colNumberedQuestionGUIDS,
RowNumber = (ThisIteration*20 + 14)
).RowNumber = (ThisIteration*20 + 14)
,
// Define Question once for this reference
With(
{
ThisQuestion14:
LookUp(
colQuestionList,
colQuestionList[@meow_questionid]
= LookUp(
colNumberedQuestionGUIDS,
RowNumber = (ThisIteration*20 + 14)
).QuestionGUID
)
},
Collect(
// Collect into individual collections to not break concurrency
colNumbersPossibleAnswers14,
{
Question: ThisQuestion14,
// Get relationship-referenced object
PossibleAnswers: ThisQuestion14.meow_Question_Answers_Rel
}
)
)
)
,
If(
// Check if this number exists in list
LookUp(colNumberedQuestionGUIDS,
RowNumber = (ThisIteration*20 + 15)
).RowNumber = (ThisIteration*20 + 15)
,
// Define Question once for this reference
With(
{
ThisQuestion15:
LookUp(
colQuestionList,
colQuestionList[@meow_questionid]
= LookUp(
colNumberedQuestionGUIDS,
RowNumber = (ThisIteration*20 + 15)
).QuestionGUID
)
},
Collect(
// Collect into individual collections to not break concurrency
colNumbersPossibleAnswers15,
{
Question: ThisQuestion15,
// Get relationship-referenced object
PossibleAnswers: ThisQuestion15.meow_Question_Answers_Rel
}
)
)
)
,
If(
// Check if this number exists in list
LookUp(colNumberedQuestionGUIDS,
RowNumber = (ThisIteration*20 + 16)
).RowNumber = (ThisIteration*20 + 16)
,
// Define Question once for this reference
With(
{
ThisQuestion16:
LookUp(
colQuestionList,
colQuestionList[@meow_questionid]
= LookUp(
colNumberedQuestionGUIDS,
RowNumber = (ThisIteration*20 + 16)
).QuestionGUID
)
},
Collect(
// Collect into individual collections to not break concurrency
colNumbersPossibleAnswers16,
{
Question: ThisQuestion16,
// Get relationship-referenced object
PossibleAnswers: ThisQuestion16.meow_Question_Answers_Rel
}
)
)
)
,
If(
// Check if this number exists in list
LookUp(colNumberedQuestionGUIDS,
RowNumber = (ThisIteration*20 + 17)
).RowNumber = (ThisIteration*20 + 17)
,
// Define Question once for this reference
With(
{
ThisQuestion17:
LookUp(
colQuestionList,
colQuestionList[@meow_questionid]
= LookUp(
colNumberedQuestionGUIDS,
RowNumber = (ThisIteration*20 + 17)
).QuestionGUID
)
},
Collect(
// Collect into individual collections to not break concurrency
colNumbersPossibleAnswers17,
{
Question: ThisQuestion17,
// Get relationship-referenced object
PossibleAnswers: ThisQuestion17.meow_Question_Answers_Rel
}
)
)
)
,
If(
// Check if this number exists in list
LookUp(colNumberedQuestionGUIDS,
RowNumber = (ThisIteration*20 + 18)
).RowNumber = (ThisIteration*20 + 18)
,
// Define Question once for this reference
With(
{
ThisQuestion18:
LookUp(
colQuestionList,
colQuestionList[@meow_questionid]
= LookUp(
colNumberedQuestionGUIDS,
RowNumber = (ThisIteration*20 + 18)
).QuestionGUID
)
},
Collect(
// Collect into individual collections to not break concurrency
colNumbersPossibleAnswers18,
{
Question: ThisQuestion18,
// Get relationship-referenced object
PossibleAnswers: ThisQuestion18.meow_Question_Answers_Rel
}
)
)
)
,
If(
// Check if this number exists in list
LookUp(colNumberedQuestionGUIDS,
RowNumber = (ThisIteration*20 + 19)
).RowNumber = (ThisIteration*20 + 19)
,
// Define Question once for this reference
With(
{
ThisQuestion19:
LookUp(
colQuestionList,
colQuestionList[@meow_questionid]
= LookUp(
colNumberedQuestionGUIDS,
RowNumber = (ThisIteration*20 + 19)
).QuestionGUID
)
},
Collect(
// Collect into individual collections to not break concurrency
colNumbersPossibleAnswers19,
{
Question: ThisQuestion19,
// Get relationship-referenced object
PossibleAnswers: ThisQuestion19.meow_Question_Answers_Rel
}
)
)
)
)
)
);Then since we now have all of the data and our calls to the network are over, we Collect all of those individual collections together and ensure we only have unique results (credit to Matthew Devaney for the improved duplicate removal formula, much cleaner than the duplicate removal I had written here before):
ClearCollect(
colPossibleAnswers,
colNumbersPossibleAnswers0,
colNumbersPossibleAnswers1,
colNumbersPossibleAnswers2,
colNumbersPossibleAnswers3,
colNumbersPossibleAnswers4,
colNumbersPossibleAnswers5,
colNumbersPossibleAnswers6,
colNumbersPossibleAnswers7,
colNumbersPossibleAnswers8,
colNumbersPossibleAnswers9,
colNumbersPossibleAnswers10,
colNumbersPossibleAnswers11,
colNumbersPossibleAnswers11,
colNumbersPossibleAnswers12,
colNumbersPossibleAnswers13,
colNumbersPossibleAnswers14,
colNumbersPossibleAnswers15,
colNumbersPossibleAnswers16,
colNumbersPossibleAnswers17,
colNumbersPossibleAnswers18,
colNumbersPossibleAnswers19
);
ClearCollect(colPossibleAnswers,
ForAll(Distinct(colPossibleAnswers, ThisRecord), Result)
);.. And that’s the end of it! A whole lot of code, yes, but the improvements are measurable and especially applicable if you are pulling large amounts of data and even more so when doing that in many-to-many relationships.
The Results
I will be using an incredibly small set of data to test, 200 rows.
The Question Table only has three columns: Name, QuestionText and Answer
The Answers Table only has three columns: Name, AnswerText and AnswerChoice (A choice field with only 5 choices)
(The Relationship column for these is meow_Question_Answers_Rel)
When my connection is throttled to a rural mobile-level (Slow 3G), the results are as follows:
Old ForAll:
Total Requests: 1226
Data Transferred: 679 kB
Time Taken: 100 seconds
New Sequential Concurrent ForAll:
Total Requests: 615
Data Transferred: 391kB
Time Taken: 39 seconds
THIS IS HUGE!
Half the data, time and number of requests made – this effect scales up even more with larger data volumes per row.
For such a tiny amount of data to have such a drastic improvement indicates how effective this is on larger data volumes.
I hope this pattern is useful to someone and that it helps anyone who is required (for whatever reason) to pull large volumes of data (especially in many-to-many relationships)!
Please take a look at my other blog posts here if you have the time and thank you so much for visiting!
I have used same technique to deal with more than 10k records from Dataverse, it takes sometime to load but it is possible to make it running in background while allow user to interact with other contents.
However, this technique is the last thing we would want to use or all records must be required for apps to run properly. If there is case that does not required all records, it is ultimately must not be loaded into apps. For this practice, I found that filter based on user (account\role\group\permission\role) is one of the best way to get just exact the number of records that user should see.
Yes for collecting ‘normal’ data you should not try get large numbers of records, regardless of the formula/approach you are using, as you should be minimizing the data you take into an App – but as I mentioned in the article this is a necessity if you need to take records for use offline or need to handle the storage of many-to-many relationships in a more performant way than a normal ForAll.
This method can be used for more than just collecting data though 🙂 you can use the Sequential Concurrent ForAll for performant execution of any other action that you would normally be trying to achieve with a ‘standard’ ForAll.
Cheers for this! I was looking for an approach to do something like this. 🙂 Either to make the Flow concurrent or call multiple flows concurrently. I’d almost written it off – I wasn’t that keen on having mountains of code to do something quite simple, but this is the trade-off. I was going to see if it was possible to ‘tee’ up some hidden buttons and then trigger those in some way, but think this is a better approach, plus is working in my instance.
I’m really glad you found it useful – it’s fairly code-heavy but the results have been worth it with larger-scale App performance 🙂