Reporting missing visits

[MattianUK]

Hi

I am looking to expand on a flow I currently have. It reports on the data saved in a SharePoint list via PowerApps. The issue is, it can only, right now, tell me what’s happened - and I need to know what’s not happened!

the app/list logs visits to sites by agents. I would like a flow to cycle through our agents and report back if they haven’t created a list item in last 24hrs.

is there a way to cycle through a list of users or email addresses in the created by SharePoint list column and then email any missing to remind them and another email with a html to let management know those who have missed logging a visit? 

[grantjenkins]

It can definitely be done. Can you share what your SharePoint List look like for the visits? Also, where do you keep a full list of users/agents that are supposed to attend the visit so we can check if they didn't fill anything in.

[MattianUK]

Hey Grant

Thanks for the reply!

The SharePoint List for Visits  has the following:

Created By (because its done via the App, this is the field I'd want to check against)

Site - a lookup field to a customer list

Check in type - a drop down of different reasons for visits

Check in Location - text field with location grabbed from GPS on the power app

Sessions used - numerical. We operate in sessions, so all staff should do up-to 3 sessions a day - the systems aim is to report how many sessions we complete for each customer. This is where if they do not log, it's an issue.

Currently, I don't have anything setup with the staff in it, but I can do another list, I can do a Office365 Group, or anything really! There are approx 30 staff.

[grantjenkins]

List or Office 365 group would be ok. Just need some way of knowing all the users so we can see which of those haven't fill in their data. Let me know what you choose to go with.

[MattianUK]

Will go for a list 🙂 

[grantjenkins · Accepted solution]

Hopefully this is what you're looking for.

For this example, I'm using the two SharePoint Lists. Agents contains a list of all agents, and Agent Visits has the visits recorded by each agent. In this case, Megan hasn't recorded a visit.

See full flow below. I'll go into each of the actions.

Recurrence is set to run daily.

Get items Agents returns all the Agents.

Get items Visits returns all the Visits in the last 24 hours. It uses the following expression for the Filter Query.

Created ge '@{addDays(utcNow(), -1)}'

Select Agent Visits extracts all the Created objects for each of the items returned from Get items Visits.

Filter array Agents Not Visited checks if an Agent doesn't exist in the Agent Visits list. This will give us a list of agents that haven't logged a visit in the last 24 hours. It uses the value from Get items Agents, and Output from Select Agent Visits.

Style HTML table is a Compose action that contains some CSS for styling the HTML table, so it looks nicer in our email.

<style>
    table {
        border-collapse: collapse;
    }
    table td,
    table th {
        border: 1px solid #ddd;
        padding: 6px 20px;
        text-align: left;
    }
    table th {
        background-color: #1C6EA4;
        color: white;
    }
</style>

Create HTML table uses the output from Filter array Agents Not Visited, and the following expressions for the mappings. It's up to you what you want to map as part of your table.

item()?['Agent/DisplayName']
item()?['Agent/JobTitle']
item()?['Agent/Department']
item()?['Agent/Email']

Send an email uses the output from Style HTML table and Create HTML table.

The email we would receive after our flow runs would look like this.

[MattianUK]

Thanks for this - it works as in, no errors, but unfortunatly it seems to just output the agents from the agents list, even if a visit is logged.

[grantjenkins]

For your Select Agent Visits, you need to go into Text mode. See screenshot below.

[MattianUK]

Ah! Thanks! Thought I did that, but obviously not!
Is there something I can add for it to not send on a weekend, or when there are no missing visits?

Thank you, this has been very helpful.

[grantjenkins]

To get it to only run on weekdays (not the weekend) you can change the Recurrence trigger to Weekly, then specify what days of the week you want it to run. You can also specify the time of day you want it to run.

To get it to only send an email if there is at least one person that didn't log a visit, you can add a Condition directly after the Filter array Agents Not Visited that checks the length of items returned. It will only go into the Yes branch if there is at least one person. The expression used in the Condition is:

length(body('Filter_array_Agents_Not_Visited'))

[MattianUK]

Awesome, thanks!