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]
Sharepoint?
Proud to be a Flownaut!
Yes for SharePoint.
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!
Proud to be a Flownaut!
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 do not have a flow to show, I haven't even attempted to do what I am talking about.
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!
Proud to be a Flownaut!
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.
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
Proud to be a Flownaut!
Did you finally overcome your challenge? Do you need additional help?
Thanx!
Proud to be a Flownaut!
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.
Proud to be a Flownaut!
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.
The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.
Announcing a new way to share your feedback with the Power Automate Team.
Learn to digitize and optimize business processes and connect all your applications to share data in real time.
User | Count |
---|---|
72 | |
23 | |
17 | |
17 | |
13 |
User | Count |
---|---|
125 | |
35 | |
30 | |
28 | |
26 |