cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

Selecting Entries from SQL Table - adding in Sharepoint List - ONLY if not already List

Hi there,

 

I am a newbie when it comes to Flow, so my question might seem trivial:

 

I want to do the following:

 

extract a set of contacts from an SQL Table

 

and add those into a sharepoint list, that are not already in that list.

 

so I would go about this like this:

 

action: get rows (as defied by sql table and columns)

for each

define array SQL (values of the sql data)

get items (sharepoint list)

define array SP (values of sharepount list)

condition

if array SQL = array SP

when yes then nothing

when no

sharepoint create item 

 

would that work ? is that the right way to go ? 
Thank you in advance

regards

6 REPLIES 6
Community Support
Community Support

Hi @LDenckert,

 

Could you please share a bit more about data structure of your SQL table and SharePoint list?

Further, which SQL Server connector do you use in your flow? on-premises SQL server or SQL Server on Azure Service?

I don’t think an array could be able to compare to another array with “=” in Microsoft Flow. I have made a test on my side and please take a try with the following workaround:

  • Choose a proper trigger, here I use Flow Button trigger.
  • Add “Get rows” action of SQL Server connector.
  • Add “Get items” action, specify Site Address and List Name.
  • Add a Variables-> “Initialize variable” action, Name set to ContactArray, type set to Array, Value set to null.
  • Add a “Apply to each”, Input parameter set to output of “Get items” action. Within “Apply to each”, add “Append to array variable” action, Name choose ContactArray, Value set to dynamic contactName of “Get items” action.
  • Add a “Apply to each 2”, Input parameter set to output of “Get rows” action. Within “Apply to each 2”, add a Condition, left input box set to ContactArray variable, right input box set to dynamic ContactName content of “Get rows” action. In middle drop down, choose contains.

Within “If/no” branch of Condition, add “Create item” action.

 

Image reference:4.JPG

 

5.JPG

Note: I use On-premise SQL server on my side.

 

The flow works successfully as below:6.JPG

 

Best regards,

Kris

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

I just edited my flow to replicate yours. awaiting results. 

 

result:

the flow runs without errors, but still no items are added to the sharepoint list. 

 

I am working with a Sharepoint online list

 

the sql table comes from an on-prem sql server, connected via on-prem-data-gateway. 

connection works, in both directions. we have successfully replicated whole tables, so we know the connection is not the problem. 

 

 

 

 

ok , tweaked it a little. now it runs well. once.

 

after that it throws errors:

 

image.png

 

something like the data value cannot be NULL.

 

I have deleted one item from the list so there should be some point in the run where it has to enter something, but the flow just fails. 

 

any advice ? 

thanks in advance

Hi @LDenckert,

 

The error message told that the value that you provided within Value field of "Append to array variable" action is null.

Please check if you have filled proper dynamic content of SharePoint list within Value field of "Append to array variable" action.18.JPG

 

 

Best regards,

Kris

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

ok it works,

 

but it now doubles all entries (it basically writes the same set of entries into the list)

it does not seem to check if those entries are aleady there... 

I redid the flow the way it was first suggested. 

it runs fine the first time (with an empty sharepoint -list)

 

the moment the list is filled, it throws out errors (again, the array data type error)

 

I am overmatched...

the easiest workaround for me would be to do adjust the workflow so that it

 

1st -> deletes all items in sp list

2nd -> does the re-entry of items from the SQL Table

 

since new data is only provided via the sql table anyways... not nice.. but it would work at least

Helpful resources

Announcements
PP Bootcamp Carousel

Global Power Platform Bootcamp

Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.

secondImage

Power Platform Community Conference On Demand

Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!

MPA Community Blog

Power Automate Community Blog

Check out the community blog page where you can find valuable learning material from community and product team members!

Top Solution Authors
Users online (6,693)