Filter List Excel Rows by New ID

[mschwing]

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.

[jedunn]

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]

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

[mschwing]

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

[jedunn]

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.

[mschwing]

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. 

[mschwing]

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?

[jedunn]

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.

[mschwing]

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.'.'.

[fchopo]

Hi @mschwing 

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

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

Hope it helps!

Ferran