Update Item Choice selection based on other List

Question

I'm relatively new to all this, so I apologize if this is simply not possible or incredibly easy.

I have two lists, the first is 'Projects' which is a list of various projects being worked on, and another list is 'Clients' which is a list of clients I have. One of the 'Projects' columns is a Lookup from 'Clients', to define which client the project is for.

Within the 'Client' list is another column called Pipeline, with a "yes" or "No" choice. I want the 'Pipeline' column to update based on whether there are any projects being worked on in the 'Projects' list.

To add a level of complication, once a project has been completed in the 'Projects' list it is not removed, but the 'Status' column is set to from "✕" to "✓". I only want the 'Pipeline' column in 'Clients' to show that there are projects if they are "✕" ie. not complete.

Here is what the 'Projects' List looks like:

Here is what the 'Client' List looks like:

Please let me know if this is possible!

Many thanks,

Nathan

Answer 1

you can use MS flow/ SharePoint Designer 2013 workflow

1. use send http request to SharePoint list action for Project list
2. In for each loop check if there is any incomplete project if yes
3. get Current item client id send another request using filter and update Client list pipeline column .

Answer 2

Depending on your environment, SharePoint Designer Workflow and MS flow(Power Automate) are both proper options.

If you are going to use Workflow, it may be better to make the workflow be triggered when there are items created/modified. Loop through all list items and check if there are projects belong to the client of current item still in "working on" status. Update the pipeline column in client list according to the loop check.