Importing sql table to sharepoint list periodically

[FH1000]

I have a flow that is connected to a sql server table. This table updates irregularily, rows may change and rows may be added. The table has been imported to a sharepoint list. Now i want to extend the flow to update or add. i have a field: project number that is identifier in both places. How can i flow this?

[abm]

Hi @FH1000 

Have a look at the below SharePoint flow actions.

1. Get item. Here you need to know the list id. If you can't get the id then use step 2.
2. Get items. Here you can filter by your identifier (Project number) you mentioned. This might be a good option for you.
3. Finally once you get the id from step 1 or 2 use Update item to update the list values.

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up.

Thanks

[v-litu-msft · Accepted solution]

Hi @FH1000,

You could create a Recurrence trigger to execute the Flow every day.

The data form the SQL table, and it should be imported to SharePoint, but there is a question that the SharePoint list exists some data from the SQL table before next time import. So, we have two methods to process the old data on the next time import. One is to delete all old data in the SharePoint list, add the whole table into the table; The other is to find the project numbers data in the SQL table that don't exist in the SharePoint, then add these data.

Cause you want to extend the flow to update or add, so the old data may be updated some time. It's a better way to take the method one. The following Flow you could have a try:

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.

[FH1000]

Thanks for your reply @v-litu-msft . Unfortunately the project numbers list is the source for a different list (lookup) so if i delete the list, the references in the other list are also deleted. Unless there is a way to commit this information to the new list.

[v-litu-msft]

Hi @FH1000,

Will you delete any data from the SQL table? I mean just update and add data.

If you just update and add data, you can update each item according to the SQL table, compare each project number, if there is no item with same project number in the SharePoint list, create a new item, otherwise update it.

These are the steps you could refer to:

If you should also delete the data in the SQL table, you might create the following steps to compare each item of the SharePoint list with SQL table rows, if find the project number does not exist in the SQL table, delete the item:

If you have any questions, please contact us.

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.

[FH1000]

@v-litu-msft thanks so far! still seeing some issues:

Unable to process template language expressions for action 'Condition' at line '1' and column '2652': 'The template language expression 'length(body('get_items')?['Value'])' cannot be evaluated because property 'Value' cannot be selected. Array elements can only be selected using an integer index. Please see https://aka.ms/logicexpressions for usage details.'

Is there a specific requirement for field data type?

[v-litu-msft]

Hi @FH1000,

Which condition happens with the issue? There is no special requirement for a data type in the condition as soon as there are the same formate on the right and left.

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.

[FH1000]

@v-litu-msft happens on this one

[v-litu-msft]

Hi @FH1000,

There is no error in the expression, and I have tested on my side it will not have the same issue, could you please use the compose action to check the output of Get items?

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.

[FH1000]

@v-litu-msft  i tried creating a brand new sharepoint list and then things started working(!) Looks like sharepoint and flow doesnt cope well with lots of changes to field types etc. Sorted now!

[RRCORTEZ]

Does this flow require premium licensing? I assume so, because they are listed as premium connectors, but am trying to verify before we look at purchasing anything. 

We are also having issues setting this flow up because we cannot find our gateway, which may be a related issue. 

thanks!

Rob c.