You’re offline. This is a read only version of the page.
I am pulling billing data off of a form that displays as two columns. The reader pulls these two columns as a table of detail lines -- GenericCharges1A and GenericCharges1B.
I need these values (there are 6 of these tables/arrays) consolidated to a single array for processing.
union() doesn't work because it drops duplicate rows.
Is there a unionall() that doesn't drop any rows?
I've tried concatenate() but it does literally just that -- it bolts the contents of the arrays together to form a long string and I can't use it aw an array. I've tried wrapping that in createArray(), but it creates a two-item array with each item itself being an array.
Has anyone found a way to do this? Would pasting these values to an Excel table (virtual, not one that is saved anywhere) be an option?
I could always do a For Each / AppendToArray() loop on each of the GenericCharges* arrays but that is a performance killer.
I'm looking for options that are NOT Union() or For Each.
I just came across an excellent and "in one step" way to concatenate arrays (append without dropping duplicates):
https://recursion.no/blogs/power-automate-extract-combine-join-and-filter-array-values/
"convert the array to string object, and then concatenate them and replace the brackets in between. Then you need to convert it back to an array object afterwards."
json(replace(concat(
string(outputs('arrArray1'))
,string(outputs('arrArray2'))
, << and so forth for each array >>
), '"]["', '","') )
Note carefully the replace() values are
singlequote, doublequote, end bracket, startbracket, doublequote, singlequote
and
singlequote, doublequote, comma, doublequote, singlequote.
It's kinda hard to see when they're rammed up together
Also I put commas at the start of the line for each array I'm appending. The array expressions do need to be separated by commas!
This is much easier (and reproducible ) than the other solutions I've seen so I am going to mark this as Solution so more people can find it.
Thanks for sharing your feed back!
Ellis
So glad that you have a solution, Melissa!
I will say that you still have the problem of when more arrays need processing, but I guess you can save that for another time. 🙂
That's why I really wanted to see an actual representation of what you have that's showing you the arrays.
With that, I could make the solution provided so that it doesn't have separate slice functions, and instead relies on one or two select actions. Ideally you want to be data agnostic.
I totally agree -- AI is too expensive to purchase it to troubleshoot for someone else!
But the heart of my question is one of array manipulation, not AI Forms.
Yes, what I sent is the result of taking that crazy-complex Output() with GUID-named columns and creating the simple two-column table I modeled in the first place using a SELECT on the Output() from AI Reader.
The heart of my question is not an AIForms one, but array manipulation -- how do I take two or more sets of array/tabular data stored in separate variables and append them (not join, append) together.
The ideal option would be Union() but it drops duplicate rows (I will have duplicate charges) and there is no UnionAll()
For me the solution as suggested by @ekarim2020 is:
1)converting each table/array to a string
2)stripping off the leading [ and trailing ] on each of those strings
3)reassembling those strings using concat()
4)convert that long string back to an array using json()
This can be done in one nested function as I believe you had suggested.
So I've extracted just the values I need for each Page/Column table using Select -- no getting around that because the column reference IDs are uniquely named.
Then in a nested function call in Select/From create the consolidated result set I need as a starting point for processing the charges.
OK, knowing that this is in each of the arrays really helps:
Share
Report
All responses (
Answers (
[
{
"Charge Name": "nFO - Business Fiber Voice Service",
"Charge Amount": ".00"
},
{
"Charge Name": "Voice Service Charges for ### 363-####",
"Charge Amount": ""
},
{
"Charge Name": "Access Recovery Chrg-Multi Line Business",
"Charge Amount": "3.00"
},
{
"Charge Name": "CALL FWD BUS. **",
"Charge Amount": ".00"
},
{
"Charge Name": "CALL ID(BUS) **",
"Charge Amount": ".00"
},
{
"Charge Name": "INTERSTATE ACC CHG - BUS-MULTI",
"Charge Amount": "9.20"
}
]
If I look at what is in the pre-processed image, and what you have there, I'm seeing that you've done some work on that data already.
Would that be a bad read of the situation?
I still think it can be done without an apply to each, it'll just take a bit of doing is all.
Irrespective of any logic in there, I would assume that you wish to split any 'mega' array on:
Voice Service Charges for ### 363-####
As that appears to define the separate batches, there.
If you don't wish to expose your flow here, you can send me a private message with it in, as I think that it doesn't appear to be including the multiples (1, 2, etc) that are before the charge descriptions ("CALL FWD BUS. **", etc), and surely you'd want that data, right?
I can't afford to pay for AI just to see how it handles / outputs that image, though, apologies. 😅
I've obviously a few ideas about how you can handle the multiple inputs, but it depends on where they're coming from, that's all. For example, if each array is coming from the same thing, then the slice() stuff might be pointless in its current context, or it might only need to be written once within a clever Select action that works on a range() function.
Thanks so much for your response/ideas. I always like doing things in one call vs. multiples once the process is proven. I'll probably stick with separate calls initially while I'm building. Easier to debug.
And I love your cool dropdown/hidden blurb -- how do you do that?!
Your questions:
Where is the data coming from? This is coming in as Output() from a call to AI Forms / Read Form Input action. If you haven't worked with this before, it is a crazy-complex structure but it is a string.
The form that is being read is an Invoice with a varying number of detail lines presented in two columns per page, going from 1 to 3 pages. In the Modeler, I have mapped each page/column's billing detail content to a table variable in the modeler: GenericDetail1A, GenericDetail1B, then GenericDetail2A, GenericDetail2B, GenericDetail3A, GenericDetail3B
When the AI Reader is executed it returns an output that contains these tables but only if they exist in the form being read. If it is a "short" invoice, Output() won't have a reference "inapplicable" variables -- GenericDetail2A, 2B, 3A, 3B won't be part of the data model returned in Output()
I access the column/page block by referencing the dynamic variable "GenericDetail1A entries", "GenericDetail1B entries", etc...
The snag is when a GenericDetail* value is not returned -- if referenced (ex: Select action), the flow will fail.
Workaround: Use coalesce() to default to an empty array if the "real" table isn't there. If there's a better way to deal with this situation I'd love to hear it!
Will there there always be 6 arrays?
The short answer is no. The model defines 6, but the result coming back from the Form Reader on any particular invoice will return only the ones that were "used". On a short bill with only a few charge lines, it will return GenericCharges1A but none of the rest in its "output()". But they are used in succession if there's a 3B there's a 3A, if there's a 3A there's a 2B and so forth
But what I'm trying to do is take those component clumps of data and reassemble them in the correct order to create a single list of the charges that I can then process -- identify header/footers, pluck off data elements like phone # from section head rows to apply to the detail lines.
What's in the arrays?
Embarrassingly simple.
Union() is not an option for me because it drops non-unique rows. I will have a lot of detail lines that appear to be identical and would be dropped if I used a Union() call.
This provided a missing puzzle piece -- slice(). Plus I do not have a good grip on when an array is really a string and vice versa... This is enormously helpful just as a study in itself.
I will definitely give this a try. The data I'm working with is coming in from a Forms AI output, where the form model is reading in charge detail that comes in two columns per page. A short bill could just be a single column of data, a longer bill could be two columns on one page. The Multi-page table (Preview) option won't work for me in this case because I have to read down, accross, then next page down, across. Six tables: 1A, 1B then 2A, 2B, then 3A, 3B.
So far none of our invoices details have needed tables 3A or 3B but I'm including them to allow for future growth.
Your solution helps me skip a step -- the data comes from the "Read Forms Input" as a part of the Ouptut() string
The individual tables are referenced as GenericDetail1A entries, GenericDetail1B entries, and so forth. But not every invoice will have all 6 tables. I've accounted for that using Coalesce()
So with your approach I can take that text directly into your Slice() functions, no convert-to-string needed!
I tried the concat() approach but the data was already in array format from Select actions.
The secret was concatenate STRINGS not arrays, and a String in proper format can be converted en masse to an array variable -- no looping needed.
This bit of knowledge is going to be applicable in so many places!
I want to make sure this will work in my particular case before clicking Accept as Solution but this sure looks like a winner!
Sorry, I forgot to mention the union function if your source data is in the correct array format:
union(variables('varArray1'),variables('varArray2'), variables('varArray3'))
When you recieve data from sources that returns only text (string) or partial data, then string manipulation functions are very useful to structure your JSON and array objects.
Ellis
Here's a one shot that uses the slice() function that @ekarim2020 showed you, @MelissaReed.
If you put this into a compose (or array Variable, or anywhere!) it is similar to Ellis' version, but just passed through a couple of translation layers instead:
json(
xml(
json(
concat(
'{"root": { "items": ',
slice(string(outputs('arrayOneCNST')), 0, -1),
',',
slice(string(outputs('arrayTwoCNST')), 1, -1),
',',
slice(string(outputs('arrayThreeCNST')), 1, -1),
',',
slice(string(outputs('arrayFourCNST')), 1, -1),
',',
slice(string(outputs('arrayFiveCNST')), 1, -1),
',',
slice(string(outputs('arraySixCNST')), 1),
'}}'
)
)
)
)?['root']?['items']
I've just got the example arrays in 6 compose actions, rather than arrays, but it works the same way.
Working
Here's each step from the inside out:
---
That being said ... I've put a bit of thought into the whether or not:
So if you could show screenshots of your current flow in the original question, that would really help. Just obfuscate anything private like you have in the image of the information that is used to create the arrays.
I believe that there's at least two possible ways to do it in a couple of steps without knowing what the arrays are, but each one might be different depending on how it comes in. Ideally it would also be great to see how that information is presented in a (failed?) flow run, too.
My current thoughts which you can ignore, but could help if you want to go a bit deeper here:
I'm guessing that the arrays are coming in one of two ways:
So if this is #1, then you could write your own 'megaArray', with each item being details on the 6 arrays AND the items from each array as an internal array field in that item. Then after that, use a Select action on a range() of the length of your 'megaArray', to selectively take an item from each of the 6 arrays, and place it as the current item in the new, select, array. Enabling you to place the correct details from each item in the original array inside this new one.
Alternatively, if it comes all in one package, as [[ARRAY_ONE],[ARRAY_TWO],[ARRAY_THREE],[ARRAY_FOUR],[ARRAY_FIVE],[ARRAY_SIX]], then you could do the same, but instead of making your own array, you use a select action to make it, which is even easier. This would be awesome if true, as your logic would be even easier.
I am putting together demos of both ways, because I want it for my own notes, but either option is more complex than the above, or Ellis' example ... it's just that they're potentially data agnostic ... which is the ideal, here. Plus, two steps. That's why it'd be great to see how the information is coming in, or some kind of example.
#1
WarrenBelz
146,765
Most Valuable Professional
#2
RandyHayes
76,287
Super User 2024 Season 1
#3
Pstork1
66,091
Most Valuable Professional
