Column Total per department

[StevenP]

Hi,

I currently have a visitor sign in app that has the below columns within an SP List:

Name (Inserted in PowerApps)

Department (Drop down in Powerapps)
SignIn (utcNow())

SignOut (utcNow())

For the past few weeks I have been using a separate flow to report on the visitors, using GetItems and then select to format it and then send it as a html table, however now I have been asked for a weekly summary of visitors to each department such as:

Department 1: 57 visitors

Department 2: 42 visitors

Department 3: 21 visitors

I've spent a few hours on it and making zero progress, would appreciate it if the community could help me with this one 🙂

Thanks!

[rsaikrishna]

@StevenP 

Try the following steps:

1. Create a SharePoint list "Departments" to store all the department names.

2. Create a scheduled flow which runs once on every sunday (for example).

3. In the flow, Get Items from "Departments" - This will get all the depts from the list.

4. Place Apply to each action to loop thru each department

5. Within Apply to each, place Get Items action to get the items from "visitors". In the Get Items action, you can see Filter Query field. In this field, place Department Name eq '<current dept name>' and SignIn ge '<expression: add(utcnow(),-7)>' and SignIn le '<expression: utcnow())>'

6. Now, you can get length of the items (item count). You can set a variable with HTML tags in the required format.

7. Finally, outside the Apply to each action, send an email with variable content.

Regards

Krishna Rachakonda

If this reply helped you to solve the issue, please mark the post as Accepted Solution. Marking this post as Accepted Solution, will help many other users to use this post to solve same or similar issue without re-posting the issue in the group. Saves a lot of time for everyone.

[StevenP]

Thanks for your time replying. Then I would just add the new list as an additional data source and then use the contents for the drop down?

[rsaikrishna]

@StevenP 

Yes. 

Alternate workaround without using new list:

By using Select and Union, you can achieve it as well.

https://powerusers.microsoft.com/t5/General-Power-Automate/Distinct-Values-From-SP-List/td-p/48377

Note: In the post, check the reply from "Automation" person on 02-03-2020 09:08 AM

Regards

Krishna Rachakonda

If this reply helped you to solve the issue, please mark the post as Accepted Solution. Marking this post as Accepted Solution, will help many other users to use this post to solve same or similar issue without re-posting the issue in the group. Saves a lot of time for everyone.

[StevenP]

Thanks, I've managed to get the data out to an array but how do I then total each department?

[[{"Department Visiting":"Department1"},{"Department Visiting":"Department1"},{"Department Visiting":"Department2"},{"Department Visiting":"Department2"},{"Department Visiting":"Department3"},{"Department Visiting":"Department3"},{"Department Visiting":"Department3"}]]

[StevenP]

I re-thought my approach, rather than doing it direct in flow I setup a screen that counts the rows based on the department name:

CountRows(Filter('Guest Register','Department Visiting'="Department 1"))

Is it possible in power apps to schedule a flow to run? I could setup an email button to email direct from the app, however I'd like it to be automated rather than needed someone to press the button?

[rsaikrishna · Accepted solution]

@StevenP 

Use Case:  There is a SharePoint list "Visitors Information" which tracks the users, department, time in and time out. We would like to send a weekly report on the number users visited each department.

SharePoint List Schema:

The above list contains:

Visitor Name : Single line of Text

Department Visiting: Single line of Text

TimeIn: Date and Time

TimeOut : Date and Time

Power Automate

Step 1: Created a Schedule flow which runs every Saturday.

Get Items : Retrieves the visitor information from "VisitorInfo" list who visited during last 7 days.

In the Filter Query, you can see an expression:

TimeIn ge '@{addDays(utcNow(),-7)}' and TimeIn le '@{utcNow()}'

Step 2:  Get unique department names

Select Departments:   This is Select data operation.

From: value property from Get Items

Map:  Mapping the value "Department Visiting" to "DeptName"

Compose Union of Departments: To get unique department names

Use following expression:

union(body('Select_Departments'),body('Select_Departments'))

Step 3: Create string variable

varHtmlTableRows is a string variable to store all the department name and no. of visitors in HTML tags.

Step 4: Loop thru each department to get no. of visitors

Note: We are not reading the SharePoint list for every department name. If we do that, it is a performance hit. We already have all the visitor information from Get Items action in Step 1.

Select an output from previous steps: Outputs is the value from "Compose union of departments" action created in Step 3.

Filter array: 

a. value is the value from Get Items action created in Step 1.

b. Comparing "Department Visiting" is equal to Output of "Current Department Name" action.

Append to string variable:

We are creating HTML table row for each department and appending it to the string variable varHtmlTableRows.

Outputs is the value from "Current Department Name" action.

<tr><td>@{outputs('Current_Department_Name')}</td><td align='center'>@{length(body('Filter_array'))}</td></tr>

Step 5: 

Checking if varHtmlTableRows is null or empty. If this variable is empty or null, then there are no visitor items.

Compose HTML Style: Using this style while sending email for table.

<style>
table {
  border-collapse: collapse;
  width: 100%;
}

th, td {
  text-align: left;
  padding: 8px;
}

tr:nth-child(even){background-color: #f2f2f2}

th {
  background-color: #4CAF50;
  color: white;
}
</style>

Expanded "Send an email (V2)" action screen shot:

Note: In the body field, the first Outputs is from compose action named as "Compose HTML Style" and second Outputs is from compose action named as  "Construct HTML table".

Here is the output email:

Regards

Krishna Rachakonda

If this reply helped you to solve the issue, please mark the post as Accepted Solution. Marking this post as Accepted Solution, will help many other users to use this post to solve same or similar issue without re-posting the issue in the group. Saves a lot of time for everyone.

[knsrinath]

Hi @rsaikrishna 

Thank you for sharing Power Automate flow.

I am developing Canteen Management System.

I have created a flow with html flow to send Email body When Item Created.

But I am unable to create totals and Day summary as follows

Departments will be different based on requirement. 

Request you help me to create a flow with Email Body and PDF attachment.

Thanks & Regards