Power Automate - How Can I Update a SharePoint List after Comparing Multiple Values from Another List

Question

I have a list of vendors, Vendor Contacts, and I need to update their Agreement Status (Active/Expired/NA) based on another list, Contracts Tracker, that contain their Agreements/Contracts. A vendor will have multiple contracts on the contracts tracker at any given time.

Examples:

Vendor Contacts:

Vendor	Agreement Status
Vendor 1	Active
Vendor 2	Expired
Vendor 3	NA

Contracts Tracker:

Vendor	Contract Name	Agreement Status
Vendor 1	Name 1	Active
Vendor 1	Name 2	Expired
Vendor 2	Name 1	NA
Vendor 2	Name 2	Expired
Vendor 3	Name 1	NA

I need to update the a vendor's status only after comparing the status of all of its contracts. The logic would be as follows:

For any vendor listed in the Vendor Contacts, if any contract is Active on the Contracts tracker, set the agreement status on the contacts list as Active. If there are no active contracts, but there are expired or NA contracts, then set the status to expired. If there are only NA contracts, then set the status to NA.

How would I accomplish this using Power Automate?

I've seen tutorials that compare single items on each list using an ID or name, but I don't know how to get the contracts for each vendor then do the comparison. I'm new to all of this so I'm not sure where to start.

Answer 1

first for getting all the contracts, there is an action called Get Items. You can set an OData filter to filter all the entries from your second list, say Contracts Tracker.

If it is a single text field, the query will be quite like Vendor eq xxx

Here is an old flow for testing with Get items action.

In the query, I set 2 conditions, one is that the text field (from List 2, which is Contracts Tracker list ) needs to equal the "IDRange Value" value. This dynamic content is from another list1, which can be your Vender Contract list.

This action will return a collection of items. As you want to check if any of them has status equals active.

Here is an option you can have a try. You can set the query as (Vendor eq xxx) and (Status eq Active). If at least one item is returned, it means you need to set the status to be active. Or you need to set it as NA.

For checking if Get Items Action returns more than Zero Records, you can refer to this blog:https://www.c-sharpcorner.com/blogs/check-if-get-items-action-returns-more-than-0-records-using-flow