Update Excel row keeps adding rows rather than updating

[lewis_ward1]

Good Afternoon, 

I have an excel file which mirrors my SharePoint list. When a new action is added I want it to take the SharePoint generated ID number and check the Excel file to see if the ID number is present. I followed a you tube video and it seems to work for the adding the row to excel but when it looks to update it just seems to keep adding it and i end up with duplicates. 

I have attached images of the flow. Can anyone see any issues with the below?

The spreadsheet has around 1250 rows.

Any help will be appreciated.

Thank you 

[lewis_ward1]

When I look at the Filter Array's outputs the body always comes back as [ ] which I believe why it always thinks it's empty. 

I have tried various options but cannot seem to get it to work.

[v-alzhan-msft]

Hi @lewis_ward1 ,

You should check the length of the body from the filter array:

length(body('Filter_array'))

Best regards,

Alice   

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lewis_ward1]

Hi @v-alzhan-msft ,

I have tried this and keep getting the same result when i look on the flow it always comes back as true.

I cannot seem to see a body output on the filter array. 

Do you think anything else could be wrong? I've taken a screenshot of the excel sheet as well to show you that. 

Thank you. 

[v-alzhan-msft]

Hi @lewis_ward1 ,

The Body output of Filter array return no item so the flow would always run into the if yes branch of the Condition.

Please make sure there is a row in the excel table has the IDS column equal to the string ID.

Best regards,

Alice   

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lewis_ward1]

Hi @v-alzhan-msft ,

The value 1200 is on the IDS column.

What I can see in  the array is that  stops at line 256 and anything before this the flow works. 

Anything after that it doesn't. There is no gaps in the table or differences. 

Do you know why it would just stop there?

[lewis_ward1]

I am going to raise a ticket with Microsoft on this issue as i get an error message that takes me to a website which displays the a message. 

The link starts with @ odata.nextLink": then the link and ends with "me&%24skip=256"

The website shows this message when you follow the link. 

{
  "Message": "Missing Authorization header for a privileged call on connection.",
  "Source": "product policy"
}

 This maybe why anything greater than ID of 256 is always coming back empty. 

[lewis_ward1 · Accepted solution]

I have just contacted Microsoft and spoken to one of there representatives who was very helpful.

She explained the issue and we was able to come to a solution on this issue. 

I will explain what we did to hopefully help somebody else in the future who maybe having the same issue. 

• In the list row settings accessed through the 3 dots (...) We turned on the Pagination setting with the threshold increased past the 256 limit that seemed to be the default. The maximum limit currently set by Microsoft is 2048 rows.

I then asked that if my table was to get more rows than 2048 what would the best option be to resolve this. She explained that the best option would be to filter the rows by having it look down a column for the ID number. 

• To do this we clicked 'Show advanced options' on the list rows present in a table box and then in Filter Query we entered the  IDS eq 'ID'
  • IDS is the column name in my excel file, then you put a space followed by eq  which means equals then another space open quote ' Dynamic Content [ID] followed by close quote '

This then filters the list and if it finds the ID it updates the row and if it doesn't it will then add the row.

Hopefully this will help someone as it really helped me.

Thanks