Getting the sum of items that are reaching a date range and sending out an email

[Anonymous]

Hello Microsoft Power Automate Community,

I am currently stuck in creating an automation. I have 2 SharePoint lists:

• Cost Center: Including Cost Center (text), and an In-charge (User)
• IT Assets: Including Laptop IDs, Cost Center(Vlookup to Cost Center list), and Warranty Expiry Date (date) [Amongst other things].

I'm trying to create a recurrence weekly flow that:

1. From the IT Assets List, Checks the laptops' warranty expiry date is within 6 months from today.
2. If they are, sum up all the laptops that are expiring with the same Cost Center ID.
3. Sends out a email reminder to the person in-charge from the Cost Center List.

Currently, I have

• Recurrence Flow (Every Monday)
• Get Items from my Sharepoint IT Assets list (With link and list) 
• Initialise variable (expiringSum)
• Apply to each (If Warranty Expiry Date is equal to addDays(utcNow(),-180) 
  • Yes: expiringSum +1
  • No: ?

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 ;_;

[Mira_Ghaly]

@Anonymous 

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/

[Anonymous]

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.

[Mira_Ghaly]

@Anonymous 

You can use Get Items in SharePoint in a similar way and use the Filter,so you don't need it in excel.

[Mira_Ghaly]

@Anonymous 

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.

[Anonymous]

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 ;_; 

[Grant_Jenkins]

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.

[Mira_Ghaly]

@Anonymous

[Mira_Ghaly · Accepted solution]

@Anonymous 

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 !