Merge two arrays into all possible combinations

[chrisgchambers]

The Setup
I have a Sharepoint list named "Translation Dictionary" that allows users to review and edit translated strings used for localization in an internal application.  It has lookups for two other lists (Locales, Placeholders) that are populated by scraping the javascript locale definitions from the app's git repository. The user then provides or updates the Translation Text for each Locale and Placeholder.

The Problem
As application development continues, new placeholders are added to the locale definitions. I want the flow to create a list of Placeholder and Locale combinations that do not already exist in the dictionary so I can iterate through them, add automated literal translations to the dictionary as a starting point, and notify users to review and update the literal translations to be context-specific and fluent. 

I have a flow that works for this, but it's SLOW (ok, it takes two minutes with 50 threads, but it really seems like there should be a better way). The only part of the flow taking an appreciable amount of time is generating the initial list of combinations to filter. I've done this with nested Apply to Each loops that append an object with the pair of IDs to an array variable for each combination. 

It really seems like there ought to be a better way to come up with the initial array of items, but I haven't found it yet. I'm hoping fresh eyes will come up with something more elegant. The Translation Dictionary list also has a surrogate key in the Title column that's composed from the concatenated placeholder and locale names if that's helpful. Again, the problem isn't filtering against the existing pairs, it's creating the list to filter in the first place.

[mahoneypat · Accepted solution]

Here is one way to cross join two arrays.  A Select can often be used to take one ATE out of a Flow and speed things up.  Note, however, in this one, I set the Concurrency of the ATE to 1, as the current output is unioned with the vResult array variable in each iteration.

An array variable called vResult is created, along with a string variable called vTemp.  The ATE is over the items in Array1.  In each iteration:

- the vTemp variable is set to the current value of Array1

- a Select is performed on Array2 with two columns (one to get the vTemp variable and the other to get the current Array2 item)

- a Compose is used to union the Select array with the vResult array

- the vResult array is Set to the Compose output

The vResult array can then be used downstream.

Pat

[chrisgchambers]

Thank you Pat! This is just the sort of solution I was hoping to find. This one goes right into the toolbox!

It seems to me it would make sense to use the ATE on the array you expect to have fewer elements. In my current case, there are about 256 placeholders but only a handful of locales and I expect the placeholder count to continue increasing much faster than the locales. Would I be correct to assume I should apply the ATE to the array of locales and the Select to the placeholders?

Thanks again for the assist. I've mocked this approach up in a flow to make sure I understand how it works. I'm going to try it in the real deal now and see how it performs.

[chrisgchambers]

High five Pat! What an improvement! From 2 minutes with max concurrency to 3 seconds single-threaded.

The only thing I tweaked is I dispensed with the vTemp variable and just used items('for_each_locale') in my select.