Comparing SharePoint list item values against another list

Businesses often face scenarios where they need to compare items across two SharePoint lists to identify discrepancies, synchronize data, or trigger specific actions. This can be challenging without an automated solution. Power Automate offers a way to automate these comparisons.

There are several reasons why you might need to compare items in two SharePoint lists:

1. Data Synchronization: Ensuring that information in two lists is consistent and up-to-date.
2. Error Detection: Identifying and rectifying discrepancies between lists.
3. Reporting: Generating reports based on the differences or similarities between lists.
4. Automation: Triggering workflows based on the comparison results, such as sending notifications or updating records.

Prerequisites

Two SharePoint lists with at least one common column (e.g., “ID” or “Title”) to use for comparison.

Power Automate

The example we are taking here is maintaining the Count of Products in inventory.

We have two lists, one for tracking product sales (Product Sales) and another for tracking the current count of products (Product Count). We want to automate the process of updating the product count based on new sales entries. Specifically, when a new sale is recorded:

1. If the product already exists in the Product Count list, increment its count.
2. If the product does not exist in the Product Count list, add a new entry for the product with a count of one.

Flow logic

• Two SharePoint lists:
  • Product Sales: Contains product sales data with columns like Product Name, Sale Date, Quantity, etc.
  • Product Count: Contains product count data with columns like Product Name and Count.

Flow Designer

We start by adding a trigger on the Product Sales list so that we can update the item count every time there is a new sale. We also initialize two variables that we shall use later in the flow. We start with Item Count = 0.

We then query the Product Count list to see if there are any Products matching the Product Name.

Please note that the Filter Query property only supports the Internal field names.

If there are any matching records, we’ll capture the Item Id and update the Item Count from 0 to the count in that row.

Item Count =0 will suggest that there were no matches in the Products Count table so this is a new record, else we add the Quantity to the Count value in the row.

Testing our flow

Let’s add a new Product

The flow creates the new item.

Now lets add a sale for an existing Product

As expected, it updates the Quantity (from 3 to 4 in our case)