Order of Archive; Filtering an Array; Failing on Compose Action

[CindyZ]

I have a SHAREPOINT list of events I have filtered to archive by a particular date. 

I also have related documents in document library.  The EventID in the Document library matches to the ID of the list items.  Not all list items to be archived will have related documents and some items will have multiple documents.  After the documents are moved to the archive document library and the list item is archived, then I will delete the list item.

My GetItems filters my list items perfectly. 

Since I already have the ID from the filtered GetIems, I'd like to refer to that array of records when I check to see if there are matches to the ID for any related documents in the document library.

I have a few of questions.

1. Can I perform the copy and delete for the list item as well as documents moves in parallel or should I archive the list first and then move the documents and then delete the list item? 

In an attempt to accomplish what I need and ensure this is working correctly, I've inserted a Select and a Compose after my GFPO-GetFilePropertiesOnly which I'd hoped would filter my Get Items to find related documents in the document library and then show me the count of the filtered items.

However, the SelectItemsWithDocs is pulling all records (even if no related documents exist) and ComposeItemsWithDocs is not returning a count.   The SelectItemsWithDocs is, in fact, pulling the properties of the matching documents when they exist.

1. Can someone tell me what I need to do so the Select pulls only the file properties of the documents needed instead of all properties for all list items.
2. What do I need to do to get compose to correctly calculate how records have documents to be moved and/or the total documents to be moved?
    

Note: this is failing at the point of Compose/CountItemsWithDocs with this message:

"Unable to process template language expressions in action 'Compose_2' inputs at line '0' and column '0': 'The template language expression 'length(outputs('GFPO-GetFilePropertiesOnly')?['body']['Select'])' cannot be evaluated because property 'Select' doesn't exist, available properties are 'value'. Please see https://aka.ms/logicexpressions for usage details.'"

Thanks for your consideration in helping me out with this.  

Cindy

[creativeopinion]

@CindyZ It's not ideal to use a Get File (Properties Only) action in an Apply to Each action. You'll want to use a Filter Array action instead.

A Get Items/Get Files (Properties Only)/Get Events—basically any action that returns multiple items should always be outside of the Apply to Each loop. This way you only retrieve the items ONCE. A Filter Array action should be used to filter out those items that have already been returned.

I would recommend you create a new flow and start fresh. Remember to rename your actions to keep your flow organized and test often at the end of each section so you can understand what's going on in your flow. 

Get Items

It sounds like you are already filtering out your list using a Filter Query which is great.

I'm not sure if the Select action you have after the Get Items action in your original flow is necessary. Doesn't look like it (unless I'm missing something)

Return Count of Items

Whenever I use a Filter Query in a Get Items action, I always like to return the count of items returned in a Compose action. This is helpful when building a flow and can also be used to troubleshoot your flow.

Insert a Compose action. Add an Expression. Use the length() function.

Select the Dynamic content tab and insert the value dynamic content from the Get Items action into the length() function.

Run a test. Review the outputs and ensure the Compose action is displaying the correct number of items you are expecting.

Get Files (Properties Only)

Add a Get files (properties only) action to the root of your flow. Fun fact. The Get Files (properties only) returns folders as well. You need to use a Filter Query to return Files only. Refer to this YT Short. If there are any other custom columns you can filter your files on (besides the EventID) you can add to your filter query. 

Loop through Each SP Item

Add an Apply to Each action to your flow. Ensure you are selecting the proper dynamic content. You want to loop through each SP list item, select the value dynamic content from that action. 

Filter Files

Insert a Filter Array action into the Apply to Each action. In the From field, insert the value dynamic content from the Get files (properties only) action. You want to use this action to filter the files returned by the Event ID column. 

From your original flow—it looks like you are comparing the value from the Event ID column to the SP ID column. 

In the first value field select the dynamic content from the Get Files (properties only) action for the column in your document library that is storing the EventID (in my example, my column name is CustomID). Ensure you are selecting the correct dynamic content. 

Leave the operator as is equal to. In the second value field insert the ID dynamic content from the Get Items action. 

It's important to note that the ID dynamic content from the Get Items action is an integer. If your Event ID is storing a string of text, you'll need to convert that to an integer using the int() function.

Return a Count of Filtered Files

Whenever I use a Filter Array action, I always like to return the count of items returned in a Compose action. This is helpful when building a flow and can also be used to troubleshoot your flow. Insert a Compose action. Add an Expression. Use the length() function.

Select the Dynamic content tab and insert the body dynamic content from the Filter Array action into the length() function.

Run a test. Review the outputs. 

You might want to refer to to these two YT Tutorials:

Are you using the Microsoft Power Automate Filter Array Action wrong?

In this video tutorial I’ll show you 3 practical ways to use the Filter Array action and how to use it properly.

1️⃣ Cross-Referencing Data

2️⃣ Filtering by Key

3️⃣ Substring Matching

Did you know that the Condition action has a limit of 10 conditions? Although it might look like the Filter Array action can only accept one condition—this is not true. By using the advanced mode you can enter multiple conditions into a Filter Array action with an expression.

IN THIS VIDEO:

✅ 3 Ways to Use the Filter Array Action

✅ How to use the Scope Action to Group Actions

✅ How to Check the Number of Items returned from a Filter Array Action

✅ How to Cross-Reference Data in Excel with a SharePoint List

✅ How the Filter Array Action Works

✅ How to Access the Dynamic Content from a Filter Array Action

✅ How to Filter Items by a Key

✅ How to Filter Items by Matching a Substring

✅ How to Use Multiple Conditions in a Filter Array Action

---

3 Mistakes YOU 🫵 are Making with the Apply to Each Action in your Microsoft Power Automate Flow

In this video tutorial I’ll go over how to avoid these common mistakes when using the Apply to Each action in a Power Automate flow:

1️⃣ Looping through a Single Item

2️⃣ Creating Unnecessary Nested Loops

3️⃣ Looping through an Unfiltered Array

At the end of the video I share a few helpful insights when it comes to using the Apply to Each action in your flow.

IN THIS VIDEO:

✅ How to avoid the Apply to Each action with a single item array

✅ How to use the item() function to access dynamic content in an array

✅ How to prevent unnecessary nested Apply to Each action loops

✅ How to use the Select action

✅ How to convert an array to a string with the Select action

✅How to use the Filter Query field

✅ How to count the number of items in an array

✅ How to use a condition control

✅ How to use the concurrency control

✅ How to set a top count

✅ How to use Compose actions for troubleshooting

Hope this helps!

If I helped you solve your problem—please mark my post as a solution ✅.
Consider giving me a 👍 if you liked my response!

👉 Level up your Power Automate skills by checking out my tutorials on YouTube
👉 Tips and Tricks on TikTok and Instagram

[CindyZ]

@Thanks so much for this thorough response.  I'll be working on this today and let you know the outcome. 

[CindyZ]

I'm really sorry that I'm still having trouble because I believe your instructions are perfectly clear so I'm not sure where I'm misunderstanding.    Below is what I currently have. 

I tried a number of runs. I tried to run it without the integer correction.  That ran beautifully but did not return any records. 

I did not start over as you suggested simply because my flow works fine up until the point of where I Get Files (Properties  only).  Also, yes, you are correct re the Select but I wanted to actually see what records were being pulled since I had so many.  I can delete that once I'm comfortable. 

If found that at the point where you said "leave the operator as is equal to. In the second value field insert the ID dynamic content from the Get Items action that even if I selected the ID from the GetItemsEventsListthat it would always revert to the ApplyToEachFromGetItemsEventsList.  It would also run fine except it still showed me all four hundred records.  It did not filter out only those records where the ID's matched and it did not pull any document references -- I was getting that yesterday. 

So, I finally forced the "GetItemsEventsList" into the field after "is equal to" and this is the result.  

I'm anxious to find out where I've gone wrong; especially since I've tried this multiple times in multiple ways.  

Thanks again for all your help.  I'd just found your videos yesterdays and fully watched the short as well as the other recommended ones today.  I look forward to working my way through all the others.  Your teaching is crystal clear.  

[creativeopinion]

@CindyZ The dynamic content you have inserted into the second value field is incorrect. You also need to swap the dynamic content. 

Take a look at the Apply to Each action, you are looping through each SP Item.

The purpose of the Filter Array action is to filter out the file(s) that have the Event ID that matches the current SP Item's ID (or whatever unique ID you are using to search) that you are looping through. 

For that reason, the value inserted into the Filter Array action should be taken from the Get files (properties only) action—which you have.

Ensure that, the dynamic content entered in the first field, is the dynamic content of the column from your document library. In my case, it's Custom ID. Because my Custom ID column is a number column (I don't require the int() function).

Leave the operator as equal to since you are looking for a match. 

In the second value field you want to insert the dynamic content of whatever column in your SP list has the value you are matching to your document library. In my case, I'm just using the default SP ID column. Select the appropriate dynamic content in your flow. 

Hope this helps!

If I helped you solve your problem—please mark my post as a solution ✅.
Consider giving me a 👍 if you liked my response!

👉 Level up your Power Automate skills by checking out my tutorials on YouTube
👉 Tips and Tricks on TikTok and Instagram