How To: Complex Get SharePoint List Items

[MarionS]

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]

[efialttes]

Sharepoint?

[MarionS]

Yes for SharePoint.

[efialttes]

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!

[MarionS]

I am trying to take a summary of this table

And move the summary into this table

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.

[MarionS]

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

[efialttes]

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!

[MarionS]

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.

[efialttes]

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

[efialttes]

@MarionS 

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

Thanx!

[MarionS]

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.

[efialttes]

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!

[MarionS]

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.