cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kaithebuilder
Frequent Visitor

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

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

1 ACCEPTED SOLUTION

Accepted Solutions

@kaithebuilder 

 

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

Mira_Ghaly_0-1665129151228.png

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

Mira_Ghaly_1-1665129228234.png

 

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

Mira_Ghaly_0-1665140404637.png

Expression 1 & Expression 2 use below:

items('Apply_to_each')?['CostCentre']['Id']

 

4. Create HTML Table and send email as below

Mira_Ghaly_1-1665140522795.png

 

 

Hope this helps !

 

If this post helps you with your problem, please mark your as Accepted solution.If you like my response, please give it a Thumbs Up.

Blog: here

View solution in original post

8 REPLIES 8
Mira_Ghaly
Dual Super User
Dual Super User

@kaithebuilder 

1. you need to use a Get Items with a filter query with something similar like below with the warrany date in future

Mira_Ghaly_1-1664955090474.png

 

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/

 

 

If this post helps you with your problem, please mark your as Accepted solution.If you like my response, please give it a Thumbs Up.

Blog: here

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.

@kaithebuilder 

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

 

If this post helps you with your problem, please mark your as Accepted solution.If you like my response, please give it a Thumbs Up.

Blog: here

@kaithebuilder 

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.

 

If this post helps you with your problem, please mark your as Accepted solution.If you like my response, please give it a Thumbs Up.

Blog: here

 

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

kaithebuilder_2-1665105921229.png

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

kaithebuilder_4-1665106777748.png

 

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
Helper I
Helper I

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.

@kaithebuilder

If this post helps you with your problem, please mark your as Accepted solution.If you like my response, please give it a Thumbs Up.

Blog: here

@kaithebuilder 

 

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

Mira_Ghaly_0-1665129151228.png

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

Mira_Ghaly_1-1665129228234.png

 

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

Mira_Ghaly_0-1665140404637.png

Expression 1 & Expression 2 use below:

items('Apply_to_each')?['CostCentre']['Id']

 

4. Create HTML Table and send email as below

Mira_Ghaly_1-1665140522795.png

 

 

Hope this helps !

 

If this post helps you with your problem, please mark your as Accepted solution.If you like my response, please give it a Thumbs Up.

Blog: here

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Community Calls Conversations

Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (2,945)