Hi,
I'm new to power-automate, so I wanted to ask if anyone knew how I could potentially execute the following or if it is even possible. [Sorry if this is too long/detailed]
Scenario: I want to create an inventory stock control of our supplies. Currently, the stock is dispensed/recorded in my company's secure web application, but the web application is not able to give any indication/alert that the stock needs to be re-ordered.
Desired outcome: I want to create a report in Excel (that i will later put in Power BI) that will show stock on hand, re-order level and re-order quantity (based on last 3 month sales and annual sales history), and when the stock reaches the re-order level, a notification via MS Teams message is sent.
My thought process to execute this:
>> Create a master excel workbook in one-drive that contains the following columns
Supply code*, supply name, supply size, vendor name, current stock on hand, sales from last 3 months, sales from last year, re-order level, re-order quantity
*The supply code will be unique to each product
>> An automatic excel report will be generated twice a day from the web application that contains the columns underlined above and will be placed in one-drive. Power automate will have to compare each supply code from the automated report, and based on "supply code", update the master excel workbook "stock on hand" column
- if the automated excel report has a new code that isn't in the master excel, it should create a new row and fill the information of the underlined columns above (which will be available in the automated excel report)
>> for the 3 month sales history and annual sales history, should I generate an automatic excel report from the web application, let power automate copy/paste the rows to a new master excel workbook, and add a formula that calculates the sum of each sale then create another flow to extract this information and put it in the original Master excel sheet?
- once this part is figured out, I would need to put a formula in the original master excel sheet to dynamically calculate the re-order level/quantity based one historical sales
>> Finally, once the stock on hand = re-order level, a MS Teams message should be sent to the team to review
Your advice is appreciated!
