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

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

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

Screen Shot 2020-11-22 at 8.11.03 PM.png

 

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

Screen Shot 2020-11-22 at 8.13.18 PM.png

 

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

Screen Shot 2020-11-22 at 8.14.00 PM.png

 

1 ACCEPTED SOLUTION

Accepted Solutions

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.

View solution in original post

3 REPLIES 3
v-litu-msft
Community Support
Community Support

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?

Screenshot 2020-11-23 165303.jpg

 

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.

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.

 

kbnp86_0-1606139783556.png

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.

kbnp86_1-1606140120030.png

 

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_2-1606140222974.png

 

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.

View solution in original post

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Users online (3,178)