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)
define array SQL (values of the sql data)
get items (sharepoint list)
define array SP (values of sharepount list)
if array SQL = array SP
when yes then nothing
sharepoint create item
would that work ? is that the right way to go ?
Thank you in advance
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:
Within “If/no” branch of Condition, add “Create item” action.
Note: I use On-premise SQL server on my side.
The flow works successfully as below:
I just edited my flow to replicate yours. awaiting results.
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:
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
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.
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
Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.
Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!
Check out the community blog page where you can find valuable learning material from community and product team members!