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:
From Reports Where [Incident] = 'COVID-19' and Max([Reported Date])
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
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.
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?
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.
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
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.
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.