Unanswered

Concat values from multiple rows in

(0) Share

Report

Posted on by UB400

356

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.

I have the same question (0)

All responses (5)

Answers (0)

efialttes 14,756 on at

Like (0)

Report

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!

Was this reply helpful? Yes No
UB400 356 on at

Like (0)

Report

Re: Concat values from multiple rows in

@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.

Was this reply helpful? Yes No
efialttes 14,756 on at

Like (0)

Report

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!

Was this reply helpful? Yes No
UB400 356 on at

Like (0)

Report

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

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.

Was this reply helpful? Yes No
efialttes 14,756 on at

Like (0)

Report

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!

Was this reply helpful? Yes No