Condition to Check for the existence of a SP item on another SP list

[cmegow]

I've posted on this topic before and not gotten anywhere.  Here's the challenge:

I have two Sharepoint Lists: 'Project Status List' and 'Purchase Order List'

My Purchase Order List has a single line of text field called 'Project' which is programmed to act as a look up column through PowerApps which pulls the 'Project Name' from the Project Status List.  The 'Project Name' column is also a single line of text which is created from several other fields through Power Automate so that there are QC boundaries. 

What I need to be able to do is create a flow that will add an Item to a third list 'Archive Project List' and delete the item from the "Project Status List' if the column 'Project Status' is changed to 'Completed'.  However, the condition of this action must be that there are no items on the 'Purchase Order List' in the 'Project' column that match the 'Project Name' for the item on the 'Project Status List'.

I've gotten as far as setting up ta condition that will perform an action for the Yes result of the condition.  However, I can't get the No function to run if there are no items still on the 'Purchase Order' list for my current approach.  

I'm purposefully not posting my work thus far, because it doesn't work, and the last time I did that, I did not get a response that solved my problem.  I am hopeful for a new approach because my original one did not work.

Thanks for your help (and brain power)

[Paulie78]

Because I am a slightly unclear, does the following diagram represent the logic correctly?

The part that I am slightly confused about is if the trigger is when an Item is changed (to completed) that will be the last time that project is edited and thus it will not run again and therefore never be removed if any items remain in the purchase order list.

Would it not be better to run it as a scheduled flow so that it can clear out completed projects when the purchase order list contains no matching items?

[sudharsan1985]

Hi @cmegow 

I have a couple of questions

1. Are you getting any error while executing the flow?
2. When do you think the items in the Purchase Order list is cleared for a project?
3. Are you sure while changing the status of the project in the Project Status list, there are no items in the Purchase Order list?
4. If you are not receiving any error in the flow, what do you get for the count of items action on Purchase Order list for a particular project name? 

If you think the status is changed before deleting the items in the Purchase Order list, then I would suggest converting the flow to be scheduled to run per day fetching all the completed projects, and do the necessary validation and action.

[cmegow]

@Paulie78 Thanks for posting the diagram.  I've edited it for what I actually need it to do below.  Essentially, we can't close out a project if there are items on the Purchase Order list that reference the project.  How would a count be used to query the Purchase Order List?

To address your scheduled flow approach, that is what I'm doing now.  Right now I'm using null as the condition to which I query the Project Name.  The problem is that when there are no results, the flow does not execute the No side of the condition and add the project to the archive and delete it from my list.  It's not a big deal to have my scheduled flow run each night to check for completed projects, but doing so changes the last edited date and makes it hard to judge age of items. some of our projects stick around for 3-5 years.  So, I'd prefer not to use the scheduled flow.

@sudharsan1985 

Questions answered below.

1. Are you getting any error while executing the flow? No sir!
2. When do you think the items in the Purchase Order list is cleared for a project? We have flows in place to Archive Purchase Orders when all invoices are paid against them.
3. Are you sure while changing the status of the project in the Project Status list, there are no items in the Purchase Order list? This is a business process problem for us.  This flow is in place for quality control.  If a Project Manager tries to close a project they have been working (to make their handle time look good), but they haven't closed all of their purchase orders (received all the invoices), then, we can't consider the project closed.
4. If you are not receiving any error in the flow, what do you get for the count of items action on Purchase Order list for a particular project name? I am not using a count as my query.  I think this is where my Flow could be improved.

Here is my current Flow

[cmegow]

@Paulie78 or @sudharsan1985 wondering it one of you could share the formula for a condition which counts records on my SP list.  I think that would work.  A link to an article or video would be wonderful!  Thanks for your help.

[Paulie78 · Accepted solution]

Hi @cmegow

It isn't too difficult, thanks for getting back with the updated flow diagram, it makes things much more simple to comprehend. So this is how I would do things:

1. Trigger for the flow is when an item is updated in the Project Status list. Add a trigger condition to check that the Project Status is set to completed. This will prevent the row from running when irrelevant.
2. Exactly as you have in your screenshot, perform a get items action on the Purchase Order List. 
3. Add a compose step after the Get Items action with the code:
   
4. Use the output of numRows in a condition, if it is equal to zero take one set of actions, if it is not then do something else. 

The code for step three would be:

length(outputs('Get_items')?['body/value'])​

Hope this helps.

[cmegow]

@Paulie78 Thanks for your help.  I added the a 'Compose' step right after my 'Get Items' , then tested it against the condition of equal to zero and got it working.  Thank you so much.  I really appreciate your help!!!

[Paulie78]

Well done! Feels good when you get it working right? 😁