Answered

Sync SQL server from a Sharepoint list

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

I have what I think is a super simple need but can't for the life of me figure out how to do this.. Looking for an example that someone has done before..

Need:
I have a SharePoint Online list that has about 10 columns in it. One of the columns is a "Ticket Number" field (Text field).
I have a set of users that will update, delete and insert records into this table.
I have a SQL Server table that has the same columns and I need to keep it in sync with the SharePoint online list.

I am trying to use FLOW to keep them in sync.
I created a "one time" export flow that pulls all the existing SharePoint list items and inserts rows into the SQL Server table.. Which is EMPTY to start with.
Works like a charm.

Now I want to keep any changes going forward and here is where I am having struggles.

Updates and Deletes to existing SharePoint Items.
I can't make this work as the SQL Server connector for "Update Row" or "Delete Row" needs a ROW ID.
How do I get the Row ID for the "Ticket Number" item I want to Update or Delete in SQL server when the Flow is triggered from the SharePoint Online list activity?
When I inserted new rows the connector for Insert Row does not allow you to specify the ROW ID.

Also for the SharePoint connectors

There is a "When an Item is Created" but there is not a "When an Item has been modified" ..
There is a mixed "When an item has been created or modified"
But I want to perform an insert on a new item and an update on a modified item..

Categories:

Building flows

I have the same question (0)

All responses (4)

Answers (1)

Sort by

Verified answer

v-yuazh-msft Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!

Hi @Anonymous,

The trigger "When an Item has been modified" is not supported in microsoft flow currently,

Please make sure you have a column value in sharepoint list the same as a column value in sql server connector.

For example, you have a Title column in the in sharepoint list, when an item is created in the sharepoint list, the item would be created in the sql server with the same Title.

I have made a test on my side to sync excel table from a sharepoint list when an item is created or when an item is modified, you could refer to my flow to create your flow to sync SQL server from a sharepoint list.

The screenshot of the flow as below:

Best regards,

Alice

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi and thanks for your response... The "issue" is with the SQL Server connector / function to update a row in SQL Server. The method to update a row via the SQL Server connector requires the ROW ID.

Problem is, there is not a method to retrieve the ROW ID ...

What is needed is an update to the "Update" method and "Delete" methods that allow you to manipulate a row in the SQL db without the ROW ID. Be able to use a standard update or delete SQL QUery with an appropriate where clause...

See the basic flow I am trying to create... I can determine if the Item in the SharePoint list is new or has been updated.. If new no problem inserting a row into the table... But if the item in the SharePoint list has been modified I need to simply update the corresponding row.. BUT the UPdate function requires the Row ID from SqlServer... How do I get this? That is the SHOW STOPPER to this seemingly simple flow...

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

I was able to solve this problem....

Apparently the "Row ID" is the Primary Key of the row. So I can now insert, update and delete rows in a SQL Server DB based on the actions of someone updating the corresponding rows in a SharePoint List.

Now I just need to learn a bit more about Power Apps and develop an App that works directly with SQL Server and cut out the SharePoint list... 🙂

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Is it possible to screenshots the flow? Thank you

Was this reply helpful? Yes No