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

(0) Share

Report

Posted on by LDE

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

Categories:

Building flows

I have the same question (0)

All responses (6)

Answers (0)

v-xida-msft on at

Like (1)

Report
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

Was this reply helpful? Yes No
LDE 20 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
LDE 20 on at

Like (0)

Report

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

Was this reply helpful? Yes No
v-xida-msft on at

Like (0)

Report

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

Was this reply helpful? Yes No
LDE 20 on at

Like (0)

Report

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

Was this reply helpful? Yes No
LDE 20 on at

Like (0)

Report

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

Was this reply helpful? Yes No