cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
YvoValcon
Helper I
Helper I

Send data to the correct receiver

Hi all,

 

I have an excel file that contains data from several suppliers. I want to send the relevant data to the right supplier via a Power Automate flow. However, I am stuck for days building a flow but nothing seems to work. Does anyone have experience with this?

 

Thanks in advance!

1 ACCEPTED SOLUTION

Accepted Solutions

@YvoValcon 

 

 

1) I think you may be using the wrong "Current Item" dynamic content. In the dynamic content list, you should see two "Current Items": one listed under Apply to Each and a second under Apply to Each 2.

Cayshin_0-1661170071930.png

Cayshin_1-1661170094422.png

 

2) To have the emails sent to the correct address, there are a few different ways we could do it. The simplest would be to have another column in our dataset that contains the email address. 

Then we can change our "Append to Company Array" to this:

Cayshin_2-1661170394791.png

And our second List Rows to:

Cayshin_3-1661170448353.png

Then we can add a "Send Email" like:

Cayshin_4-1661170540830.png

Cayshin_5-1661170566356.png

Attachment name can be whatever you want it to be.

 

If you can't add an email column to the dataset, then you'll need to build some kind of lookup table elsewhere that we can reference in the flow. This could be a SharePoint List, another excel file, etc...

We could also potentially use a "Switch" action instead - they're like conditions, but it can do different things based on what matched (aka, if Case matches "XYZ" then we'd hardcode the email to xyz@email.com; if Case matches "ABC", then we'd hardcode the email to abc@email.com. I'd only recommend this if there will only ever be a handful of the companies that never change, otherwise the Flow may become bloated and messy.

 

3) Is your "Create File" step within the "Apply to Each 2" step? If it's outside, Output won't be available to use.

View solution in original post

6 REPLIES 6
ScottShearer
Super User
Super User

@YvoValcon 

The first thing that you need to know is that your Excel data must be in a table.  Is your data in a table?

If so, can you explain a little more about what you are trying to do?  A screen shot of the Excel table would help as well.

 

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Scott
YvoValcon
Helper I
Helper I

Hi @ScottShearer,

 

Thank you for your reply. Yes the data is in a table. Below, you can find a mockup screeshot.

YvoValcon_0-1660920177466.png

 

Now I want to send out the data (via a scheduled flow) that is relevant for each company to the company.

How can I make sure that company A gets the data from all columns but only see their own data? The same applies for Company B and Company C?

 

Thanks in advance!

Hi @YvoValcon 

I've created a test Flow that should accomplish what you're looking for.

Here is my sample data;

Cayshin_7-1660938244361.png

 

 

What we'll be doing is:

1) Compile a list of all the unique Companies (A, B, C, etc) from the table.

2) Iterating over that list of Companies to filter the table by that company.

3) Take the resulting output and convert it to a CSV.

4) Save the resulting file (or whatever else you feel like doing with it)

 

Initialize our array variables and list the rows from our desired file/table.

Cayshin_0-1660937684330.png

 

For each row(value) in the table, append the Company in that row to our array.

Then, we'll use the union() expression on our first array to create a list of just the distinct values (Companies) and set that list to our second array.

Cayshin_2-1660937730058.png

 

Now, for each Company in this unique list, we'll do another List rows, but this time we can filter it by the Company.

We then add a "Convert to CSV" action to turn the output into a csv table. You can add/remove whichever columns you want to appear in the CSV.

Cayshin_3-1660937838881.png

 

Save the CSV file.

Cayshin_9-1660938536397.png

 

 

 

The whole Flow:

Cayshin_11-1660939873071.png

 

 

 

 

Hi @Cayshin ,

 

Thank you so much for the work! Three questions regarding your flow:

  1. I get the following error message: Flow save failed with code 'InvalidTemplate' and message 'The template validation failed: 'The inputs of template action 'List_rows_present_in_a_table_2' at line '1 and column '3048' is invalid. Action 'Apply_to_each' must be a parent 'foreach' scope of action 'List_rows_present_in_a_table_2' to be referenced by 'repeatItems' or 'items' functions.'.'. --> what do you suggest is the problem?
  2. How can I ensure that the email are sent to the correct email adress?
  3. And for the last step (create file): I cannot use "Outputs" for the "File Content", any ideas?

 

Best,

Yvo

@YvoValcon 

 

 

1) I think you may be using the wrong "Current Item" dynamic content. In the dynamic content list, you should see two "Current Items": one listed under Apply to Each and a second under Apply to Each 2.

Cayshin_0-1661170071930.png

Cayshin_1-1661170094422.png

 

2) To have the emails sent to the correct address, there are a few different ways we could do it. The simplest would be to have another column in our dataset that contains the email address. 

Then we can change our "Append to Company Array" to this:

Cayshin_2-1661170394791.png

And our second List Rows to:

Cayshin_3-1661170448353.png

Then we can add a "Send Email" like:

Cayshin_4-1661170540830.png

Cayshin_5-1661170566356.png

Attachment name can be whatever you want it to be.

 

If you can't add an email column to the dataset, then you'll need to build some kind of lookup table elsewhere that we can reference in the flow. This could be a SharePoint List, another excel file, etc...

We could also potentially use a "Switch" action instead - they're like conditions, but it can do different things based on what matched (aka, if Case matches "XYZ" then we'd hardcode the email to xyz@email.com; if Case matches "ABC", then we'd hardcode the email to abc@email.com. I'd only recommend this if there will only ever be a handful of the companies that never change, otherwise the Flow may become bloated and messy.

 

3) Is your "Create File" step within the "Apply to Each 2" step? If it's outside, Output won't be available to use.

YvoValcon
Helper I
Helper I

Hi @Cayshin ,

 

It worked, you are a king! Thanks a lot!

 

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Users online (1,843)