Hello Microsoft Power Automate Community,
I am currently stuck in creating an automation. I have 2 SharePoint lists:
I'm trying to create a recurrence weekly flow that:
Currently, I have
I've opened a lot of forum posts that are similar but not quite and I cannot seem to find a solution to this. Is what I'm doing possible? Any help is greatly appreciated.
Sincerely,
A stressed and troubled intern ;_;
Solved! Go to Solution.
Thanks so much for supplying this info, first of all we need to do the filtration based on Cap Date since warranty is calculated field and can't be use in OData Filter, so I am getting 2.5 years in a past for Cap Date which should be 6 months from the warranty date if my brain is working properly.
Please follow steps below:
1. Get Past Time and Pass to the OData Filter
Expression in oDATa Filter is as below please note the single quotes:
formatDateTime(body('Get_past_time'),'yyyy-dd-MM')
2. Get Unique Cost Centres
Expression Highlighted to get unique Cost Centres:
union(body('Select'),body('Select'))
3. Loop on the cost centres , Filter the original Get Items to get the Assets Per Cost Centre, Get Item to the cost centre Email
Expression 1 & Expression 2 use below:
items('Apply_to_each')?['CostCentre']['Id']
4. Create HTML Table and send email as below
Hope this helps !
1. you need to use a Get Items with a filter query with something similar like below with the warrany date in future
2. After that you need to do the grouping by Cost centre if possible please follow the steps on my blog article here to do grouping by cost centre
https://onlinemgblog.wordpress.com/2022/03/30/building-flows-3-grouping-in-a-power-automate/
Hi Mira, thank you so much for your response. Do you have a way for me to get a list from Sharepoint instead of excel for step 2? I've tried exporting the 'Cost Center' to excel and uploading it to my site but it shows the user by it's name instead of the 'User' type it is on Sharepoint, thus preventing me from getting the email. The cost center list on sharepoint will also get updated once in a while (through Power Apps) so I think having it on excel isn't really feasible.
Thank you for your help though! Appreciate it.
You can use Get Items in SharePoint in a similar way and use the Filter,so you don't need it in excel.
If you provide me with screenshots of your SharePoint list and how they are structured I can replicate on my side and give you the full example.
Hi @Mira_Ghaly
Thanks for your response, I've been trying again and can't seem to figure it out still 😞
This is my first center list: Code (single line of text), and In Charge (User or Group so there's an email).
This is the Assets List: Asset ID (Single line of text), Cost Centre (V-lookup to the ^ list), Warrany End Date (Calculated formula for Cap Date + 3 years), Cap Date (Date and Time, Date only), and User (Person or Grop).
I'd like Power Automate to send out a recurring email to the cost center In-charge a list of the laptops that are reaching the Warranty Expiry Date. There are some cost centers with the same In-charge as well so I have to account for that too.
Thank you so much ;_;
I can see what I can come up with using Power Automate (looks like others might too).
A couple of other options that come to mind.
Option 1: Create a grouped view directly in SharePoint showing the list of assets grouped by Cost Center where the warranty date is less than 6 months away. You could then send an email to the In-charge people with a link directly to the view.
Option 2: Create a Power BI Dashboard where you can get a dynamic summary of assets that have their warranty expiring by cost center, etc. and maybe some reports that can be sent out from there, or for your In-charge people to view directly in Power BI.
Thanks so much for supplying this info, first of all we need to do the filtration based on Cap Date since warranty is calculated field and can't be use in OData Filter, so I am getting 2.5 years in a past for Cap Date which should be 6 months from the warranty date if my brain is working properly.
Please follow steps below:
1. Get Past Time and Pass to the OData Filter
Expression in oDATa Filter is as below please note the single quotes:
formatDateTime(body('Get_past_time'),'yyyy-dd-MM')
2. Get Unique Cost Centres
Expression Highlighted to get unique Cost Centres:
union(body('Select'),body('Select'))
3. Loop on the cost centres , Filter the original Get Items to get the Assets Per Cost Centre, Get Item to the cost centre Email
Expression 1 & Expression 2 use below:
items('Apply_to_each')?['CostCentre']['Id']
4. Create HTML Table and send email as below
Hope this helps !
User | Count |
---|---|
97 | |
40 | |
27 | |
23 | |
16 |
User | Count |
---|---|
130 | |
51 | |
48 | |
36 | |
24 |