Sync SQL server from a Sharepoint list

[Anonymous]

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

[v-yuazh-msft · Accepted solution]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

Is it possible to screenshots the flow? Thank you