Not a power automate user yet: can I make this flow?

[BenjaGM]

Hello, I am considering power automate for my work, can you help me understand if this flow can be done?

1 - I need to gather information from my clients (like sales) on a weekly basis.

2 - This information must be stored in a "main database"

3 - After that, I need to send an automatic report using the info gathered back to each client.

Thanks in advance!

[nickellis74]

Yes, that can be done.

If you're just starting out, and if the data is relatively simple (a list, for example) then I would be tempted to store the data in SharePoint. The connector for that is very thorough, makes everything you need very easy to achieve and best of all doesn't require a premium licence. People in your business can then simply load the data directly into SharePoint, no need for any Power Automate to gather information.

Compiling and sending the report would probably be easiest as a spreadsheet. I don't love spreadsheets for this use case, and I am sure purists will argue that Power BI is better (because it is) but it's also more expensive and a MUCH steeper learning curve. Just for now, lets stick to Excel.

Before you start building a flow, you will need a list in SharePoint of your customers and their email addresses (you should use this as a lookup column in the main data list to ensure people don't mis-spell names, etc.). Also, create a template of the report which includes an area which is a named table. This won't work without that. If you're not sure what I mean, this will help: Overview of Excel tables - Office Support (microsoft.com). Store that in SharePoint or OneDrive, and then your flow steps will be like this:

1. Use a Schedule trigger to set when the reports are sent (or use the Instant Flow button option if you want direct control)
2. For each row in that list...
   1. Create a copy of the Excel file and rename it "Client name - Date.xlsx" or whatever you want to call it. I would suggest creating a specific document library to store these in with two extra columns - Customer (lookup from the list described above) and Date Sent (date time field).
   2. Use the SharePoint "Update file (Properties only)" action to set the customer column on the file you just added
   3. Use the SharePoint connector "Get Items" action, with a filter to the specific customer, to get the list of data
   4. Use the Excel connector "Add a row to a table" action to fill in the rows of the table
   5. Use the Outlook "Send an Email" action to send the report to the client
   6. Use the SharePoint "Update file (Properties only)" action to set the Date Sent field. For this I would suggest simply inserting utcNow() from the formula option