cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Community Support
Community Support

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

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.
Highlighted
Regular Visitor

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

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. 

 

 

 

 

Highlighted
Regular Visitor

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

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

Highlighted
Community Support
Community Support

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

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.
Highlighted
Regular Visitor

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

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

Highlighted
Regular Visitor

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

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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors
Users online (5,974)