cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Kudo Kingpin
Kudo Kingpin

Concat values from multiple rows in

Here's the scenario, I've built an Expense report App using PowerApps , that's using SQL Server as the DB. Each Expense Report can have multiple Expense line items and each expense line item could have a different person approving that particular line item.

 

I'd like to send out an E-mail  for each Expense Report to all the approvers (one for each line item) that have not yet approved the specific line item.

 

While I've already done all the filtering etc. What I need help with is as follows:
1. Send one e-mail for each expense report

2. Create a string of distinct approvers for each expense report

 

So that I can get an e-mail for each Expense Claim, addressed to each of the unique approvers for all the line items in the Expense Report.

 

Any help would be greatly appreciated.  

5 REPLIES 5
Highlighted
Dual Super User III
Dual Super User III

Re: Concat values from multiple rows in

Hi!

"While I've already done all the filtering etc. "

Does it mean you already have a draft Power Automate Flow designed? I mean, at least the trigger and the first action blocks? If so, can you share a screnshot from youtr current design so we can get a more accurate idea on how we can help you?

Thanx!



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!



Highlighted
Kudo Kingpin
Kudo Kingpin

Re: Concat values from multiple rows in

FlowScreenShot.jpg@efialttes nothing much to see at the momment. I'm just playing with the proof of concept, trying to understand the best way to get this done. At the moment I'm just using a button trigger, but I will eventually change to a scheduled trigger as I would need the Flow to run once everyday. Once I have the proof of concept working, I can flesh out the rest of the Flow.
Essentially as you will see in the image, I have Get Rows action nested in another, the 1st one or the "enveloping one" gets all rows that meet a certain criteria, and within that there is an apply to each, which contains another Get Rows action which retrieves the line items associated with each claim being looped through.
Where I'm stuck, is trying to concat the value of the approvers column from the line items, so that I can have them in a string e.g. joe@abc.com; jane@abc.com; so that I can send the approvers an e-mail reminding then that they need to review these items. Let me know if you need further info on the scenario. Thanks for looking.
 

Highlighted
Dual Super User III
Dual Super User III

Re: Concat values from multiple rows in

Hi again

"Where I'm stuck, is trying to concat the value of the approvers column from the line items, so that I can have them in a string e.g. joe@abc.com; jane@abc.com"

 

If your output has one column per approver email, there are several approaches: concat(), createArray() and join(), etc all of them based on expressions and WDL funtions

But it is difficult to say without an example.

 

Can you share an example of line item output as displayed in your Flow execution, so we can understand better your challenge?

 

Thanx!



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!



Highlighted
Kudo Kingpin
Kudo Kingpin

Re: Concat values from multiple rows in

@efialttes  I've simplified things a bit. I'm now using "Get Rows from SQL" to bring in rows from a SQL View.

Essentially now I just have two columns, the "ID" column and an "Approver" Column (contains e-mail address).

Below is an image of part of the body of the Get Rows action

 

PendingApprovalLineItems.jpg

 

I found this blog by John Liu https://john-liu-ppph.squarespace.com/blog/2018/6/do-group-by-in-flow-with-two-simple-elegant-loops and I tried to use the union in a Compose action union(variables('TA'),createArray(item()?['TAID'])) but that doesn't work, output is blank.

So at the moment not really getting anywhere, any help is most welcome.

Highlighted
Dual Super User III
Dual Super User III

Re: Concat values from multiple rows in

Hi

 

Can you share a screnshot of your current flow design, displaying also input details, so we can see the input where you placed the union() based expression?

Just doublecking on each action block, so it will expand and we will be able to get a more clear picture

 

Thanx!



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!



Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Upcoming Events

Experience what’s next for Power Automate

See the latest Power Automate innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors
Users online (6,780)