cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mschwing
New Member

Filter List Excel Rows by New ID

I am trying to build a flow that takes forms for excel responses and adds a new line to a list with the responses and uses the form ID that is autopopulated in the excel table every time a form is submitted as the title for the list line, but every time I run the flow it recreates all of the existing lines for previous form responses. How do I filter the rows listed from excel to pull the ID for the new line added when a form gets submitted and create a new list line entry based off just the new input? I tried using filter array by excel ID > the list title thinking this would only pull new items, but it is not working.

 

mschwing_0-1648759916156.png

mschwing_1-1648759969822.png

 

 

 

 

 

 

 

 

 

 

9 REPLIES 9
jedunn
Microsoft
Microsoft

Do you know if the 'title' from sharepoint is a string or a number? I would bet that the 'greater than' operation will not work if it's still a string.

fchopo
Super User
Super User

Hi @mschwing 

@jedunn is right! Please, instead of using Title, use the following expression to convert the title to an integer value:

int(items('filter_excel_to_New_Rows')?['Title'])

 Hope it helps!

Ferran

Did I answer your question? Please consider to mark my post as a solution to help others.
Proud to be a Flownaut!
mschwing
New Member

@fchopo Where in the flow would I place this expression?

jedunn
Microsoft
Microsoft

I would put the expression in the filter array step, and it would replace the 'title' token, where the arrow points in the below picture of your flow.

jedunn_0-1648831508279.png

 

I tried putting the expression, but now when I run the flow it just continuously adds new rows to the list of all of my existing form response IDs and never stops running. 

Realized I wasn't inputting the expression properly. I fixed that, but now the flow is failing and getting this message. Do I also need to input an expression for the excel ID to be converted to an integer?

mschwing_0-1648849042882.png

 

jedunn
Microsoft
Microsoft

That error looks like it is telling you that you can't compare a string with an integer. So yes! The ID will need to be converted to an integer. It's likely you can do that with a similar expression in the same flow step.

I tried converting the excel ID to an integer, but flow is now failing and giving me this message-

 

Flow save failed with code 'InvalidTemplate' and message 'The template validation failed: 'The workflow action 'Filter_array' at line '1 and column '2590' references the action 'Get_Excel_Data' of type 'OpenApiConnection': only the actions of type 'foreach' are allowed to be referenced by 'repeatItems' or 'items' functions.'.'.

 

mschwing_0-1649086056946.png

 

fchopo
Super User
Super User

Hi @mschwing 

As you're inspecting the value inside a foreach, try the following expression:

int(item()?['ID'])

Hope it helps!

Ferran

Did I answer your question? Please consider to mark my post as a solution to help others.
Proud to be a Flownaut!

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Power Automate Designer Feedback_carousel.jpg

Help make Flow Design easier

Are you new to designing flows? What is your biggest struggle with Power Automate Designer? Help us make it more user friendly!

Top Solution Authors
Top Kudoed Authors
Users online (3,334)