numberCreate or Update Excel Row based on SharePoint List based on ID

[kbnp86]

I am trying to setup a connection between a SharePoint list and Excel table. I want people to be able to go to SharePoint list and add or update data and those changes would reflect on the Excel table.

I want it so that when an item is created or modified on the SharePoint List, I want the ID from the SharePoint List to see if it matches with the ID column on the Excel Table. If they match, then the data on the row on the Excel table would update based on the changes from the SharePoint List. If they do not match, then I want it to create a new row on the Excel table with the data from the SharePoint List.

If you look at the attached images, you can see what I have already built our, but I keep getting the Power Array Output as blank. And I think this is causing me some issues with my Flow. Keep in mind that I am comparing the SharePoint list ID (which is a number) and the ID column on the Excel document (which is also a number). I saw online that this might cause issues with Filter Array but I can't figure out how to fix this issue.

FIRST PART OF FLOW

2ND PART OF FLOW - For the text that says string, this is the formula: string(triggerOutputs()?['body/ID']). For the text that says empty, this is the formula: empty(body('Filter_array')).

FLOW RESULTS: As you can see in the OUTPUTS, it is blank. It is always blank no matter what happens.

[v-litu-msft]

Hi @kbnp86,

I tested it on my side but not find the same issue.

Could you please share an instance of the table?

How about you input an ID that already exists manually on the right side?

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

[kbnp86]

This is the video that I found this flow from on YouTube. https://youtu.be/ibqFGgkZ1Cs But as you watch the video, you will see for them it works by adding and updating data from SharePoint to Excel. For me the adding of a new row on Excel works but the Updating a row on Excel does not.

Keep in mind that I input the information onto the SharePoint list first, which then adds a row onto the Excel sheet. This includes the ID# that is auto-created by the SharePoint list (which is only numbers, starting at 1). The goal is for staff not to have to enter an ID# manually.

When I go to add new data on the SharePoint list, it works perfectly and goes right through the flow and the filter array and condition do not find the new ID# on the Excel sheet and then it adds the new row on the Excel table. But when I go to update data on the SharePoint list, the Filter Array comes back empty again and the condition is setup to say if body of the filter array is empty then it is true and the system thinks the ID doesn't exist on the Excel table (but it does) and then creates a new row with the updated data.

Below is a screenshot of the run of a flow for me after updating data on the SharePoint list. Please note, that this matches exactly as if I was entering new data onto SharePoint. (The Filter Array body is supposed to come back with something in it.)

On this example I updated the clients name on the SharePoint list. The filter array is supposed to look at the Excel value, then look at the ID on the Excel sheet (which is auto-created by the SharePoint list), and then look at the string of the SharePoint ID.

Here is the inside of the flow showing the ID number from Excel, that was created by SharePoint. And you can see in the filter array the body output is blank, which is going to cause the condition to come back as true and then it will add a new row.

Here is the next part after the filter array, which is the condition. The condition is looking for an empty body from the filter array. If it is true, then it adds a new row to Excel. If it is false, it should update a row. The problem is the body is always empty, no matter what.

[kbnp86 · Accepted solution]

I was able to get some help on this and this is what we did. First we changed the string in the filter array to string(triggerBody()?['ID']). This did not work, but we did not undo this change. The next step we did was we noticed that my Excel column title for ID has parentheses and forward slashes in it but theirs didn't. After changing the Excel column title from ID (DO NOT DELETE/CHANGE) to Unique ID, and updating the flow to reflect this change, we ran the flow and was able to add and update the way it was supposed to work.