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

[LDenckert]

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

[v-xida-msft]

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:

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

The flow works successfully as below:

Best regards,

Kris

[LDenckert]

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. 

[LDenckert]

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

after that it throws errors:

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

[v-xida-msft]

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.

Best regards,

Kris

[LDenckert]

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

[LDenckert]

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