I have an excel online file which collects MS form responses from users (salespersons) about their sales activity.
Here's an example of the sales hierarchy:
Sample Sales Hierarchy
And an example of the form responses for a specific date looks like:
Sample form responses for the date
What I am trying to accomplish here is:
For example, I wish Boss 1 to receieve a mail with the following in mail body:
Mail to Boss 1
And Boss 2 to receieve a mail with the following in mail body:
Mail to Boss2
Can you please provide any hints/suggestions?
@abm , @Paulie78 , @PrasadAthalye , @DamoBird365 , @Pstork1 , @tom_riha
Thanks and regards.
Solved! Go to Solution.
Hi @HarshavardhanG :
I assume there are two tables:
The flow should be:
Expression:
1\
12\
items('Apply_to_each_3')['SaleCount']
13\
items('Apply_to_each_3')['BossEmail']
14\
items('Apply_to_each')['Boss']
15\
items('Apply_to_each_2')['SalesPerson']
16\
17\
variables('CoutSalesPerson')
Best Regards,
Bof
You can follow the steps given by @v-bofeng-msft
There are different ways you could implement this.
1. Read the first excel file and get names
2. Filter the second excel file using names from step 1
3. Check the count result of the filter
4. Construct a compose and get all the details you want. Format the compose.
5. Append this result in a string
6. Add an If condition to check whether the Boss value is changed from the first loop. This means you can send email using the append string value under the body of the email.
7. Also remember to re-initialise the value of the boss where you need to compare for the IF statement.
8. Finally outside loop use send email again to send the last Boss record details.
Hope it make sense.
Thanks
If you liked my response, please consider giving it a thumbs up
Proud to be a Flownaut!
Learn more from my blogHi @HarshavardhanG :
I assume there are two tables:
The flow should be:
Expression:
1\
12\
items('Apply_to_each_3')['SaleCount']
13\
items('Apply_to_each_3')['BossEmail']
14\
items('Apply_to_each')['Boss']
15\
items('Apply_to_each_2')['SalesPerson']
16\
17\
variables('CoutSalesPerson')
Best Regards,
Bof
Thanks for the detailed steps. Let me try this and revert.
I'm guessing that I will have to use 2 'ListRowsPresentInATable' functions (excel online connector) for 'SalesHierarchy' & 'FormResponses' variables, instead of the 'initialize variable' used in the example.
I'm a beginner, so please bear with me.
Thanks again!
You can follow the steps given by @v-bofeng-msft
There are different ways you could implement this.
1. Read the first excel file and get names
2. Filter the second excel file using names from step 1
3. Check the count result of the filter
4. Construct a compose and get all the details you want. Format the compose.
5. Append this result in a string
6. Add an If condition to check whether the Boss value is changed from the first loop. This means you can send email using the append string value under the body of the email.
7. Also remember to re-initialise the value of the boss where you need to compare for the IF statement.
8. Finally outside loop use send email again to send the last Boss record details.
Hope it make sense.
Thanks
If you liked my response, please consider giving it a thumbs up
Proud to be a Flownaut!
Learn more from my blogThank you so much for taking out your precious time to demonstrate your logic (stated above) on Teams. I definitely learnt some new concepts and will surely try and troubleshoot the remaining stuff.
Thanks a lot once again!
Another consideration for you is using Xlookup, this would allow you to combine your two tables and you can query the data using excel scripts, rather than the excel table action. I've also done a video on creating pie and column charts using your scenario as I had been looking for a proof of concept use case for the charts and so this has inspired me 😉. My Flow has 7 or so actions and uses a form as a method of input to the excel table. Hopefully it inspires you too.
Take a look here https://youtu.be/Tj2jasorczU
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien
P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉
The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.
Learn to digitize and optimize business processes and connect all your applications to share data in real time.
User | Count |
---|---|
31 | |
29 | |
28 | |
18 | |
11 |