cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

How To: Complex Get SharePoint List Items

I am working on our city's COVID-19 emergency management SharePoint. I need to query a list and get the last entry for two indexes. What I need to get is the latest report (no matter how old it is) per department.

 

Example sudo SQL:

Select

[Incident],

[Department],

Max([Reported Date]),

Sum([Value 1]),

Sum([Value 2]),

Sum([Value 3])

From Reports Where [Incident] = 'COVID-19' and Max([Reported Date])

GROUP BY

[Incident],

[Department]

12 REPLIES 12
Highlighted
Dual Super User
Dual Super User

Re: How To: Complex Get SharePoint List Items

Sharepoint?



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted
Helper I
Helper I

Re: How To: Complex Get SharePoint List Items

Yes for SharePoint.

Highlighted
Dual Super User
Dual Super User

Re: How To: Complex Get SharePoint List Items

So, can you share a screenshot from your flow design? If you did not implement anything yet, can you share also a screenshot of your Sharepoint column definition, and identify which columns you need to work with and which is ther datatype? 

The more context info we get, the faster you'll get a useful answer

Thanx!



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted
Helper I
Helper I

Re: How To: Complex Get SharePoint List Items

I am trying to take a summary of this table

dashboard.PNG

And move the summary into this table

pivot.PNG

To do this, I need to get the latest report of each department (they are entering reports on a daily bases) and sum the reports to get an overall report. Then I used the pivot table to make a pie chart on a SharePoint page.

Highlighted
Helper I
Helper I

Re: How To: Complex Get SharePoint List Items

I do not have a flow to show, I haven't even attempted to do what I am talking about.

Highlighted
Dual Super User
Dual Super User

Re: How To: Complex Get SharePoint List Items

Hi again!

I think now I understand better the challenge, thanx for the effort!

On more question:

"To do this, I need to get the latest report of each department (they are entering reports on a daily bases) and sum the reports to get an overall report."

 

Do you need to get the latest report of each department by getting the report whose column 'Reported' corresponds to the very same day/the day before you plan to run the flow, right?

Thanx!

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted
Helper I
Helper I

Re: How To: Complex Get SharePoint List Items

I think I'm satisfied with what I have now.

 

I created two copies of the same list: List A, List B.

List A is used to submit reports and contains all reports, for all departments, for all time.

List B only contains the last report submitted to List A, for all departments.

I populate List B with a flow on List A that fires when a new report is created.

The flow either inserts a copy of the submitted report into List B if there is no report for that department, or it overwrites the report for that department if there is one in List B already.

 

The end result is List B contains the last report submitted by a department. I use that for reporting and charts.

 

Thank you for your time and help.

 

 

Highlighted
Dual Super User
Dual Super User

Re: How To: Complex Get SharePoint List Items

Hi!

So, working with List B...

how many records are we talking about?

 

If not much records I would suggest:

-Either Manual Trigger or REcurrence trigger

-Get items from list B

-Add N 'Initialize variable' action blocks, one per concept, all of them type Integer

-Add 'Apply to Each', assign as its input 'Get items' output. Now inside it add N 'Increment variable', assign to each the corresponding concept from current iteration, just select them from Dynamic Content menu

Now outside the 'Apply to each' add a 'Create item' action block to your pivot table, variables should have totals you are looking for

 

Pessimistic about time processing if nr of items in List B is huge, but since this Flow design is simple, it's easy to test

Hope this helps

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted
Dual Super User
Dual Super User

Re: How To: Complex Get SharePoint List Items

@MarionS 

Did you finally overcome your challenge? Do you need additional help?

Thanx!



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted
Helper I
Helper I

Re: How To: Complex Get SharePoint List Items

What I ended up doing is using a Power Automate flow. Each time a record is created in the detail table, I search the summary table for that department and update the summary department records. It seems to be working fine.

Highlighted
Dual Super User
Dual Super User

Re: How To: Complex Get SharePoint List Items

Hi!
Just curiosity... You mean you have a Detail list (guess a list where each employee status is reported daily on an individual basis, right?) and everytime new info is added to the Detail list your flow updates the Incident report list item that corresponds to its department?
Glad to hear everything's working fine
Thanx!


Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted
Helper I
Helper I

Re: How To: Complex Get SharePoint List Items

Almost. Each department submits a report every day but only the latest report is what matters. So, I make a summary table of all of the latest reports by department.

Helpful resources

Announcements
firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

firstImage

Join the new Power Virtual Agents Community!

We are excited to announce the launch of Power Virtual Agents Community. Check it out now!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

Top Solution Authors
Top Kudoed Authors
Users online (9,556)