cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Format Sharepoint items in comma delimited list based on second value in Sharepoint list

I have created a flow that will 'Get Items' from a Sharepoint list select the items I want and place them in the body of an email. I now need the items to be place in the email as a CSV string and grouped by the second column value in the list.

 

Current State Flow

Chouston1725_0-1601049324279.png

 

The current output in the email is formatted as so....

Domain

Jurisdiction

Cam123

1

Cam456

2

Cam789

2

Cam1011

1

 

I need the output format to look like below...

 

1

Cam123, Cam1011

2

Cam456, Cam789

 

Any help is valuable!! More information can be provided if needed!

1 ACCEPTED SOLUTION

Accepted Solutions

Hi!

It seems you are looking for an HTML table instead of a CSV table, right? Something like this:

Flow_CSVTraverseArray_6.png

 

If so, I would add a new variable, replance 'Create CSV table' and add 'Append to array variable' instead, and add extra steps just after the 'apply to each': a 'Create HTML table' action block and a 'Send an email' action block.

You will find the details of the 'Intilialize variable' and the 'Append to Array' action blocks in the following screenshot:

Flow_CSVTraverseArray_5.png

 

Hope this helps

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



View solution in original post

11 REPLIES 11
Super User III
Super User III

Hi!

Did you consider "Create CSV table" action block instead?

https://docs.microsoft.com/en-us/power-automate/data-operations#use-the-create-csv-table-action

 

Hope this helps



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



😑Wow...that is a great starting point! @efialttes now the output is 

 

Cam123,1 Cam1011,1 Cam456,2 Cam789,2 

 

Is there a way to group them buy the Jurisdiction column like I have below? 

 

1

Cam123, Cam1011

2

Cam456, Cam789

Hi!

So, my first suggestion was helpful? GReat, this means we are close to a happy ending!

Now, on your new challenge, let me see I understood it... is it OK to have one CSV file per jurisdiction value? Or you need it all in a single file? If the answer is to have it in a single CSV, I guess you can create a new array structure just before invoking 'Create CSV': so the new array's first column will store the jurisdiction, its second second column all the related references, separated by whatever character you decide.

 

Waiting for your feedback!



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



@efialttes multiple files would be fine. I need the email to display the Domain by Jurisdiction without the Jurisdiction being included in the CSV string.

 

Current Output: Cam123,1 Cam1011,1 Cam456,2 Cam789,2

 

 Output Needed: Cam123,Cam1011 

                            Cam456,Cam789

 

If you have any other ideas on how the output could look i'm open to that as well but basically i just need them separated by jurisdiction but only include the domain in the csv string so they can be easily copy and pasted into a backend application.

 

Following the original example, is it OK to obtain this CSV result for Jurisdiction 1?

Flow_CSVTraverseArray_1.png

If so, here you are my suggested approach:

Flow_CSVTraverseArray_2.png

 

Flow_CSVTraverseArray_3.png

Hope this helps



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



To answer the first question, no, i need the text in the email body to show 

 

Cam123,Cam1011

 

The text needs to be in a comma delimited string.

 

Thanks @efialttes 

 

Hi!

Then just replace 'Create CSV table' and use a 'Select' + 'Join' action blocks instead. Remember to assign to 'Join with' input the delimiter character you expect (comma, right?)

Flow_CSVTraverseArray_4.png

Hope this helps



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



@efialttesFor the Union Expression in Apply to Each, I keep receiving the error message below....Union Error.png

 

 

Hi!

The reason for the error is, your second 'Select' action block was renamed automatically as 'Select 2'. In such case you need the following expression:

union(body('Select_2'),body('Select_2'))

The union() based expression I shared is based on the fact I renamed manually the second 'Select' action block, and called it 'Select Jurisdiction' instead. In such case the original expression works

union(body('Select_Jurisdiction'),body('Select_Jurisdiction'))

 

Hope this helps

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Wow this has helped a ton, thank you! @efialttes 

 

So, now that the data is separated it is sending 2 different emails one for Jurisdiction 1 and one for Jurisdiction 2.

 

First Question: Is there a way for the lists to come in one email but still separated by Jurisdiction in the body of the email?

 

 

Second Question:

There is no way to identify which Jurisdiction the email is referring to. How would I allow the person receiving the email to know which jurisdiction the list pertains to?

Chouston1725_1-1601402352633.png

 

Hi!

It seems you are looking for an HTML table instead of a CSV table, right? Something like this:

Flow_CSVTraverseArray_6.png

 

If so, I would add a new variable, replance 'Create CSV table' and add 'Append to array variable' instead, and add extra steps just after the 'apply to each': a 'Create HTML table' action block and a 'Send an email' action block.

You will find the details of the 'Intilialize variable' and the 'Append to Array' action blocks in the following screenshot:

Flow_CSVTraverseArray_5.png

 

Hope this helps

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



View solution in original post

Helpful resources

Announcements
PP Bootcamp Carousel

Global Power Platform Bootcamp

Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.

secondImage

Power Platform Community Conference On Demand

Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!

MPA Community Blog

Power Automate Community Blog

Check out the community blog page where you can find valuable learning material from community and product team members!

Top Solution Authors
Users online (4,230)