In one of our projects, we needed to implement a solution to track the visits of sales representatives in form of a visit report. Most of the times there are multiple visit reports for one visit, because multiple representatives attend a visit.

In those visit reports the sales representatives should say if they were the driver at the visit or only a passenger. It’s of course possible that not all representatives drive with the same car, for example because of different starting points or follow-up visits. But if they have the same starting point, the same destination and overall there’s no reason why they should not drive together, it probably doesn’t make sense, that they do not drive all together in one car.

In these cases, there’s a chance for optimization and we needed a workflow that can discover these optimization possibilities. This workflow needs to be able to identify specific duplicates (visit reports) in a list.

In this blog post I’d like to show you how I set up this workflow (I simplified the implementation to focus only on the relevant parts).

For the collection of the visit reports there’s a list called “Visit reports”. The following columns can be found in the list:

Visit-ID (Number)*
Starting Point (Single line of text)*
Destination (Single line of text)*
Date (Date and Time)*
Follow-up visit (Yes/No)*
Driver (Yes/No)*

The unique ID for the visit gets stored in the column Visit-ID (ID for the whole visit, not a single visit report, multiple visit reports can exist for a one visit).

In the “Follow-up visit” column the representatives can define if they had another visit after the current visit and in the “Driver” column they can define whether they have been the driver or not. If there are two visit reports of two representatives that are completely identical except of the point that one was the driver and the other one was not, this totally makes sense and of course is the desired scenario.

This is how the list looks like with four visit reports for two visits:

The goal of the workflow is the identification of the visit with the Visit-ID 1, because the single visit reports of this visit are completely identical and in both visit reports the “Driver” column is set to “Yes”. Identical visit reports with the “Driver” column set to “No” aren’t relevant, because that’s the way it should be.

I worked out the following idea for the workflow. The workflow should process every single visit (Visit-ID) and for every single visit all single visit reports (only if “Driver” column is set to “Yes”) should be stored in a complex collection.

Then the workflow should count the entries (visit reports) in the complex collection and afterwards remove duplicates out of the complex collection. At the end, the workflow should compare the count of the entries (visit reports) before and after the removal of the duplicates. If the count is the same, everything is fine, if the count is not the same, there’s a visit (like the visit with Visit-ID 1) with multiple drivers, so there’s an optimization possibility.

So far so good, let’s start to build the workflow!

As the workflow is not related to a single element (visit report) and should run every day at a defined time, I decided to set up a site workflow.

In the first step, the workflow collects all “Visit-IDs” with a “Query list” action and stores them in a collection variable called “Collection_VisitIDs”. Afterwards the workflow removes the duplicates by using a “Collection operation” action.

In the next step the workflow iterates through all Visit-IDs to check the single visit reports. The Visit-ID gets stored in a variable called “Text_SingleVisit-ID”. In the first step of this action the workflow collects all necessary elements (visit reports) and corresponding properties of the single visit reports and stores them in different collections.

In the “Query list” action there’s a filter on the actual Visit-ID and on the “Driver” column, as only visit reports with “Driver” column set to “Yes” are relevant.

Now the workflow iterates through the  collected elements (visit reports) and the corresponding properties to get all the properties of the single visit reports that need to be added to the complex collection. Therefore, I added another “For each” action and three “Collection operation” actions afterwards, to get all needed properties.

Then the workflow uses the “Build string” action to create the single entries (visit report with needed properties) for the complex collection and afterwards uses the “Collection operation” to add the entry to the complex collection “Collection_ComplexCollection”.

The “Build string” action looks like this:

That’s it for this “For Each” action. At the end of the other “For Each” action the workflow now counts the entries (visit reports) of the complex collection. Afterwards it removes the duplicates and then counts the entries (visit reports) again.

If the number of entries isn’t the same no more it’s a visit where we have two drivers, which is probably one too much, so there’s an optimization possibility. For this blog post the workflow just logs the result into the history list.

This is how the workflow history looks like, after a successful run:

As you can see, the workflow successfully detects the visit with the Visit-ID 1!