Send data to the correct receiver

[YvoValcon]

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!

[ScottShearer]

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

[YvoValcon]

Hi @ScottShearer,

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

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!

[Cayshin]

Hi @YvoValcon 

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

Here is my sample data;

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.

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.

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.

Save the CSV file.

The whole Flow:

[YvoValcon]

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

[Cayshin · Accepted solution]

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

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:

And our second List Rows to:

Then we can add a "Send Email" like:

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]

Hi @Cayshin ,

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