In this blog I will ask the user to input the name of a film then loop over 100 films to find the entered film using the Loop condition action in Power Automate Desktop.

The basic structure is as follows:

• Ask the user to enter the full name of a film.

• Open an Excel file containing 100 films with various film details.

• Loop over the film list until the entered film is found.

• Display the location and other details of the film.

• Display a message if the film is not found.

From this point onwards I will refer to Power Automate Desktop as PAD.

Creating a new flow

Create a new flow with a suitable name.

The new flow added with a suitable name.

Storing the name of the film to search for

I'll use a Display input dialog action to ask the user for the film name.

The Display input dialog action, filled in with an appropriate question. The variable name has been changed and the ButtonPressed variable turned off.

Although not essential I will now add an If action to test if the user has entered something. If they haven't I'll display a message and stop the flow.

The If action testing the variables value using the {x} icon. The test is to see if the value stored is empty.

If we find that the user hasn't entered anything, I'll use the Display message action to tell them that the flow will be stopped.

The message a user will see if they enter nothing. The button variable is turned off.

Now to add the Stop flow action so that the flow doesn't continue if nothing has been entered.

An image of the complete If action, testing to ensure a value has been entered by the user. If a value hasn't been entered the flow will stop, otherwise it will continue after the End.

Opening the Excel file

Here's a small part of the list of films that we'll loop over:

A sample of the films to loop over. The starting cell will need to be B2.

I'll add a Launch Excel action to the flow to open this file.

Excel will open the stated file from the given path. You can use the file selector icon (highlighted) to pick the file you want to open.

In the Launch Excel action you can rename the variable for the instance of Excel. In this case I've called it Film_List.

Setting the starting cell

The first film name is in column B, row 2 on the worksheet.

Later in the flow I will need to increment the row number to move down through the films, so the row number needs to be a variable. To do this, add a Set variable action to the flow.

I've renamed the variable to Row_Number. The value at this point will be 2 for row 2 of the films list.

Now I need to read from the cell in column B. I'll do this using a Read from Excel worksheet action and refer to the value of the Row_Number variable.

The Read from Excel worksheet action set to read from cell B2 at this point. The value read from Excel will be stored in the variable that I've renamed Excel_Film_Name.

A quick test at this point will ensure I have the first film in the list stored before I enter the loop.

The film entered is irrelevant at this point of testing.

Here's what the flow's variables look like at this point:

The film stored in the Excel_Film_Name variable is indeed the first film on the Excel sheet.

The Loop condition action

In PAD, the Loop condition action performs a block of actions repeatedly, as long as a specified condition is met.

The Loop condition in the Actions pane showing the tooltip.

After adding a Loop condition action I need to specify the condition I'm testing.

The Loop condition action asks what you are testing and the value against which you are testing it.

The Operator property allows you to choose how to compare the two values.

These operators are available for the test.

I want my loop to keep going while the entered film name is not equal to the film name contained in the Excel film list.

The Loop condition action set as required.

Here's what the action looks like after adding it to the flow:

The actions I want to loop will be inserted between the Loop condition and End blocks.

Making sure the loop stops

A common problem with loops is when they continue without ending, this is referred to as an infinite loop. What happens if we go through the full list of films without finding a match?

You should always plan an exit strategy for the occasions when your loop criteria have not been met. In this case we could check how many rows we've processed to see if we've passed the end of the list.

Including the column headers my list is 101 rows. I will introduce a test so that if that number is exceeded a message appears and the flow stops.

If this condition is True the actions within the If block will be performed.

Here's what the loop looks like with the extra If action added:

The If will check the current row number, display the message and stop the flow if the condition is True. If the condition is False the flow will continue after the If block.

Working through the list

The first film in the list is Jurassic park on row 2. If a user searches for this, the condition for the loop will not be True and PAD will continue the flow after the end of the loop.

For any other film PAD will need to continue looking at the next cell down until the entered film is found.

To look at the next row we can increase the value of the Row_Number variable using an Increase variable action.

The Row_Number variable being increased by 1, ready for the next film.

I now need to read the contents of the cell in column B, using the row number stored in the variable that has been increased. I can use another Read from Excel worksheet action to do that.

This action will now read the contents of the next film in the list.

I need to read the contents of the cell into the Excel_Film_Name variable. I can do this by selecting the variable in the action.

The value read from Excel needs to be stored in the Excel_Film_Name variable, ready for the next iteration of the loop.

Capturing more film details

Eventually the entered film will be found in column B using the stored row number.

The loop should then end and PAD will continue the actions after the loop.

I'd like to include other details about the film once it's been found. I can use another Read from Excel worksheet action to do this:

The date the film was released is stored in column C, which this action will retrieve. The variable has been renamed Excel_Film_Date.

I can use yet another Read from Excel worksheet action to read the film length from column J.

The film length is stored in column J, which will be stored in the renamed variable.

The next action will be adding the message that displays the data I have stored in variables.

Its a good idea to test the flow and observe the value of the variables to check everything is as expected.

Searching for Titanic returned these values.

I've hidden some columns from the screenshot above, but the values stored are correct for Titanic.

Tidying up the results

A minor issue is that the date stored for the film contains the time too.

I only want to include the date portion of the stored value. I can use a Get subtext action to do this.

The Get subtext action will allow me to retrieve the first 10 characters of the date.

Here's how I've configured the action:

This Get subtext action will store the first 10 characters from the Excel_Film_Date variable in a variable called Short_Film_Date.

I can now include all the data that has been retrieved in a Display message action.

The message to be displayed for the film details with its button variable turned off.

The final action is to close the instance of Excel with a Close Excel action.

The final action to close Excel without saving.

Testing the flow

With the flow complete I would run a series of tests for the following to ensure I get the expected results.

• Not entering a film to search for.

• Search for the first film in the list.

• Search for a film that is not contained in the list.

Satisfied with the outcomes of the above tests I can now search for any film.

Searching the list for Bee Movie.

The message resulting from the above search.

See the other blogs in this series for more PAD loops.