cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dennisb88
Responsive Resident
Responsive Resident

Comparing SQL and SharePoint

Hi all,

 

I'm try to build a flow that compare two lists:

1) SharePoint list with names and person ID's

2) SQL table with names and person ID's.

 

To do this, I try this blog from @DamoBird365 Comparing two arrays without an apply to each? - Microsoft Tech Community.

However I'm sure that both lists are identical, it don't show me any data.

 

My flow is build as follow:

dennisb88_0-1645196984968.png

 

 

Result example:

dennisb88_1-1645196809710.png

Does anyone know how to fix this?

 

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @dennisb88 

 

I am not sure....

 

Here's my equivalent of SQL - an array of manufacturer and colour

DamoBird365_1-1645201566560.png

 

And my Microsoft List:

 

DamoBird365_0-1645201549646.png

 

The difference is that SQL has KIA, my list does not.

 

DamoBird365_2-1645201639757.png

The from for my Filter is my Compose for SQL.

 

The condition is @not(contains(body('Select'), item()?['Manufacturer']))

i.e. the select array does not contain the manufacturer....

 

The input history shows the full array in the filter array and the output is KIA:

DamoBird365_3-1645201746149.png

Could it be something like the action name having a | in it?  Or have you renamed it after building the filter?  Normally it wouldn't let you run it if the name doesn't match.  Try removing and re-adding.

 

Damien

View solution in original post

9 REPLIES 9
DamoBird365
Microsoft
Microsoft

Hi @dennisb88 

 

Your filter should be based on contains, rather than equal.  You want to compare an array with a value but you’re using is equal to.

 

Damien

Hi @DamoBird365 ,

 

Thank you for your reply.

I try to use 'does not contain' (because I want only the records that not stored in SharePoint.) but I got all the 398 records back. Where I'm expect to get zero records because the lists are equal at this moment.

Also When I delete one record in SharePoint, I expect only that record back (because it's available in my SQL export) but it shows all the records.

 

any idea?

Hi @dennisb88 

 

Have you updated your flow since the screenshot to does not contain?

 

so the filter array should be output of select does not contain Id?

 

Damien

Hi @DamoBird365 ,

 

Yes I changed the flow to does not contain to try but with the same results:

Current flow:

dennisb88_0-1645199144627.png

The array that I expect should be only contains the data from SQL that Not available on SharePoint.

 

Hi @dennisb88 

 

I am not sure....

 

Here's my equivalent of SQL - an array of manufacturer and colour

DamoBird365_1-1645201566560.png

 

And my Microsoft List:

 

DamoBird365_0-1645201549646.png

 

The difference is that SQL has KIA, my list does not.

 

DamoBird365_2-1645201639757.png

The from for my Filter is my Compose for SQL.

 

The condition is @not(contains(body('Select'), item()?['Manufacturer']))

i.e. the select array does not contain the manufacturer....

 

The input history shows the full array in the filter array and the output is KIA:

DamoBird365_3-1645201746149.png

Could it be something like the action name having a | in it?  Or have you renamed it after building the filter?  Normally it wouldn't let you run it if the name doesn't match.  Try removing and re-adding.

 

Damien

Hi @DamoBird365 ,

 

Thanks for helping.

I don't know why it was not working at but it's working fine when I export first my SQL to another SharePoint list and make from there the compare. 

After the weekend I will check further how I can make it work directly with SQL. Because I'm not modify the data or something like that.

 

Thanks again and have a nice weekend.

Hi @dennisb88 


I would love to hear how you get on in time. Just wondering what the sql server does as the screenshots you showed me looked right and the filter is just comparing arrays. Pleased that the two lists work though. 

I don’t have access to a sql server. Otherwise I would try and give it a go too.

 

Good luck and keep me posted.

 

Damien

Hi Damien,

 

I've take some more time to get this working. I don't know why or what's changed om some background settings, but now It's working fine. I did no changes in my flow.

Nog I can compare SQL rows and SharePoint lists on Formula 1 speeding level!

 

Thanks again for helping.

DamoBird365
Microsoft
Microsoft

HI @dennisb88 

 

Nice one!!  Glad it's doing the job as expected.  Definitely super efficient.  Enjoy the F1 speed 😂

 

Damien

Helpful resources

Announcements
 WHAT’S NEXT AT MICROSOFT IGNITE 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Register for a Free Workshop.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Users online (2,833)