Flow Sp list aggregate multiple items into one item in new SP list

[aidas2]

Hello,

I'm new to using power automate and have a task as follows with which I'm having a hard time :

I have an SP list A which I need to use as a basis for creating SP list entries in sp list B based on a set of rules:

• if Column value Tema is repeating across multiple rows for >3 of the same Calendar week in SP list a then create one entry in SP list B with the same Category, Tema Values.
• If possible it would be perfect to have the case numbers somehow listed in a separate column in list B as well.

See example picture below - I have Tema repeating value (Kasa) and it is repeating for 3 Calendar Weeks (CW 07). So the result of this should be one new entry in SP list B which would have random Title, Category, Tema in its columns and if possible a list of all the Case Numbers in a separate column.

You can also see that it's repeating for CW 05 but because it's less than 3, a new entry should not be created in sp List B.

This should be dynamic, etc, it should scan for values from sp list A in and if a Tema column value repeats across 3 or more of one calendar week, then create a task in sp list B etc.

So far I have:

1. Get sharepoint items action from sp list A,
2. Condition to filter out unwanted entries from sp list A. If yes do nothing, if No continue.
3. If no - Condition to check only one category for simplicity of testing. If No do nothing, if Yes continue.
4. If yes - Create SP item in sp list B with randomized title (rand(1,100000)) 

From here I'm having a hard time figuring out how to create the logic for checking if the Tema value repeats and if so, does it repeat more than 3 times across the same calendar week across all the rows in sp list A.

I'm new so any input is helpful! Thank you!

[v-jefferni]

Hi @aidas2 ,

First of all, you will need to initialize an array variable to hold unique Tema values that are included in list A.

union(outputs('Get_items')?['body/value']?['Tema'],outputs('Get_items')?['body/value']?['Tema'])

With this array, you can use Filter array in apply to each to get records that of each Tema, then calculate the number of rows in Condition to check if result is larger than 3, if yes, get the unique Calendar week array in Compose and filter the outputs of Filter array based on each Calendar week:

if result is greater than or equal to 3, create new item in list b, using values from 'Filter array 2'. For example, the category:

union(body('Filter_array_2')?['category'],body('Filter_array_2')?['category'])

and to get all case numbers together, use below expression:

join(body('Filter_array_2')?['Case Number'],',')

it will concatenate all case numbers from the outputs of filter array 2 with a coma as separator.

Best regards,

[aidas2]

Hi, @v-jefferni 

Thank you for the extensive write up, this is amazingly helpful not just with this case but will be in the future as well!

I've setup the flow as you've instructed but am getting this error when trying to test:

---

InvalidTemplate. Unable to process template language expressions in action 'Initialize_variable' inputs at line '0' and column '0': 'The template language expression 'union(outputs('Get_items')?['body/value']?['Tema'],outputs('Get_items')?['body/value']?['Tema'])' cannot be evaluated because property 'Tema' cannot be selected. Array elements can only be selected using an integer index. Please see https://aka.ms/logicexpressions for usage details.'.

---

Do I understand correctly that the Tema column values have to be integer instead of string for the variable to work?

Thank you!

[v-jefferni]

Hi @aidas2 ,

I'm so sorry about this. I have forgotten that cannot use above expression to get column values from Get items action. Please use a Select action to create an array with only Tema column values, then in initialize variable action use below expression.

union(body('Select'),body('Select'))

Best regards,

[aidas2]

Hi, @v-jefferni 

Thank you for taking the time to troubleshoot this with me!

That solved the issue but now I'm getting a similar one in the Compose above Apply to each 2 with iInput:

union(body('Filter_array')?['CalendarWeek_x0028_OpenedDate_x0'],body('Filter_array')?['CalendarWeek_x0028_OpenedDate_x0'])

InvalidTemplate. Unable to process template language expressions in action 'Compose' inputs at line '0' and column '0': 'The template language expression 'union(body('Filter_array')?['CalendarWeek_x0028_OpenedDate_x0'],body('Filter_array')?['CalendarWeek_x0028_OpenedDate_x0'])' cannot be evaluated because property 'CalendarWeek_x0028_OpenedDate_x0' cannot be selected. Array elements can only be selected using an integer index. Please see https://aka.ms/logicexpressions for usage details.'.

At first I thought it could be due to the name of the filed (original error said Calendar Weeks), so I looked up the technical name of the field which is "CalendarWeek_x0028_OpenedDate_x0" and changed to it but that didn't resolve the issue as seen above.

It seems like the issue is identical as before so I created a new select "SelectCW" with the Calendar week column in it and used it in the Compose above Apply to each 2:

"union(body('Filter_array')?['SelectCW'],body('Filter_array')?['SelectCW'])":

Still failed though with the same problem:

InvalidTemplate. Unable to process template language expressions in action 'Compose' inputs at line '0' and column '0': 'The template language expression 'union(body('Filter_array')?['SelectCW'],body('Filter_array')?['SelectCW'])' cannot be evaluated because property 'SelectCW' cannot be selected. Array elements can only be selected using an integer index. Please see https://aka.ms/logicexpressions for usage details.'.

Once again I am asking for support 😅

[v-jefferni · Accepted solution]

Hi @aidas2 ,

Thanks for your response.

You need to drag and drop SelectCW to just below Filter array, change From to Filter array value outputs, map the column CW to item()?['CalendarWeek_x0028_OpenedDate_x0'], and then use expression union(body('SelectCW'),body('SelectCW')) in Compose instead.

Best regards,

[aidas2]

Hi, @v-jefferni 

Thank you, I think I have marked it as solution a bit too soon though 😅

I did the changes but it doesn't want to work with this error:

Flow save failed with code 'InvalidTemplate' and message 'The template validation failed: 'The inputs of template action 'Compose' at line '1 and column '2136' cannot reference action 'SelectCW'. The action 'SelectCW' is nested in a foreach scope of multiple levels. Referencing repetition actions from outside the scope is supported only when there are no multiple levels of nesting.'.'

After I put the SelectCW under Filter array and try to map Calendar weeks column to item()?['CalendarWeek_x0028_OpenedDate_x0'] it puts the SelectCW it into an Apply to each which breaks the functionality I presume and that's what the error is about.

Any way to go without the Apply to each or am I doing something wrong here?

[v-jefferni]

Hi @aidas2 ,

SelectCW should not be included in the Apply to each 3 Control. The Control came up because you have selected an output from Get items. The Select operator is used for giving another name to a column and include values in this column directly, also means to restructure a table/array. So, the column name is fixed. 

Same logic and method as the first Select operator:

Best regards,

[aidas2]

Hi, @v-jefferni Thank you for the prompt answer. This time the flow worked yet it didn't create anything due to the Filter array 2 returning no results from what I can tell.

I double checked all the fields and all seems in order.

I remember with your initial solution before the select it did work if I am not mistaken so perhaps it had some affect or maybe the input of Filter array 2 should be not Body but Outputs of Compose 🤔

Thank you for your patience, it's a great learning experience for me 😅

[v-jefferni]

Hi @aidas2 ,

Not exactly. The problem is in Filter array 2, the expression needs to be changed because of column name:

@equals(item()?['CalendarWeek_x0028_OpenedDate_x0'],items('Apply_to_each_2')?['CW'])

Best regards,

[aidas2]

Hi, @v-jefferni Thank you, it was a good solution!

I was having issues with the last action of create item but after some reading up online I tried using [6] (it was the 7th retrieved entry in the array) instead of ['Category'] and it worked.

Instead of:

union(body('Filter_array_2')?['category'],body('Filter_array_2')?['category'])

Used:

union(body('Filter_array_2')?[6],body('Filter_array_2')?[7])

 
But now there's one last issue (hopefully) 😅 The Join to collect relevant case numbers is not working.

By using the expression below it says that property  'Title' cannot be selected. Array elements can only be selected using an integer index.

join(body('Filter_array_2')?['Title'],',')

So then I tried using this below as it's the 4th item in the array:

join(body('Filter_array_2')?[3],',')

But got the following error:

Unable to process template language expressions in action 'Create_item' inputs at line '0' and column '0': 'The template language function 'join' expects its first parameter to be an array. The provided value is of type 'Object'

So I figured I would create a select just below filter array 2 and use it as the case number field but following the error description it wouldn't work as it's not an array.

I tried anyway, I can see that it returns the correct data in the output but the join is unable to use it with the following error as expected:

Unable to process template language expressions in action 'Create_item' inputs at line '0' and column '0': 'The template language expression 'join(body('SelectCAT')?['CAT'],',')' cannot be evaluated because property 'CAT' cannot be selected. Array elements can only be selected using an integer index. 

And I'm out of ideas, so once again I am asking for support 😅

[v-jefferni]

Hi @aidas2 ,

In Select actions, you can use a simple expression to get a simple array:

map in key value mode will give you an array including column names. But in Text mode you can simple select column values into an array. Now you can use join(body('Select'),';') to concatenate all values of Select output to be a string value for later use.

Best regards,

[aidas2]

Hey @v-jefferni  Thank you, that did the trick but once again I'm having an issue 😅

It generates the data but the result is longer than 255 symbols so it can not create the item.

I found that the union retrieves all the data from filter array instead of just the category or tema for example and that's why it says too many symbols.

I tried creating extra selects with map fields for category and tema as it works for the join and retrieves juts the cases but then it doesn't want to retrieve the data or perhaps I'm doing it wrong.

The select are as follows - From:Body, Map: item()?['Tema']

What other way would be to just take the relevant Category and Tema fields from the Filter array 2 results in my case 🤔

See attached pictures.

Thank you so much and sorry for bothering this much 😅

[v-jefferni]

Hi @aidas2 ,

There must be some logic problem in your flow, I cannot understand what those additional Select are used for. Could you please share all your flow actions in a snip? 

Best regards,

[aidas2]

Hi, @v-jefferni 

items('Apply_to_each_2')?['Tema']

if I use this in the create item then it generates entries but the Tema and Category entries are not unionized, just shows a repeating list of the same Tema value which is expected.

Then I tried using this 

union(items('Apply_to_each_2')?['Tema'],',')

Got the following error:

InvalidTemplate. Unable to process template language expressions in action 'Create_item' inputs at line '0' and column '0': 'Template language function 'union' expects parameters of same type, but found 'Null,String' distinct types.'.

So lastly I tried creating SelectCAT and SelectTema with Map attribute item()?['Tema'] and use them in the create item but it also kinda works. - it is returning all of the details of the list a and puts them in the category and tema fields of list b for some reason, even though SelectCAT and SelectTema only return the relevant values.

body('SelectCAT') 

Error:

OpenApiOperationParameterValidationFailed. The 'inputs.parameters' of workflow operation 'Create_item' of type 'OpenApiConnection' is not valid. Error details: The API operation 'PostItem' requires the property 'item/Category' to be a string of maximum length '255' but is of length '1723'.

pictures of my entire flow

Thank you!

[v-jefferni · Accepted solution]

Hi @aidas2 ,

There could be multiple duplicate Title and Category in outputs of Filter array 2, please try and use below expressions for the two parameters of Create item:

Category:

join(union(body('SelectCAT'),body('SelectCAT')),',')

Cases:

join(union(body('SelectCASE'),body('SelectCASE')),',')

Tema:

items('Apply_to_each')?['Tema']

union is for getting unique values, join is for concatenating unique values with the coma operator.

Best regards,

[aidas2]

Hi, @v-jefferni Thank you so much! After using your advise and tinkering a bit with some other things, the flow is working as intended now.