cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Bjorn
Level 8

Comparing Two Lists and Finding Differences

I'm new to Flow and stuck on what I thought this would be a simple task. I'm storing a list of Salesforce Accounts (names and IDs) in Sharepoint. I'm syncing them when the Salesforce Account object is created/modified, but there's obvioulsy no 'On Delete' trigger. I'm trying to create a Flow that sets an inactive flag on a Sharepoint list item when its corresponding record has been deleted in Salesforce. My attempt is below, but is highly inefficient (a query inside a loop) and chokes when Salesforce returns a 404 on a deleted record.

 

2018-04-05_16-31-33.png

 

I had hoped to get two result sets, A and B, loop through A and use some sort of 'in_array' type function to find the A items not in B. I also tried filter array but couldn't get that to work. Any pointers? 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Bjorn
Level 8

Re: Comparing Two Lists and Finding Differences

I finally got it working, posting this for anyone who needs a way to keep lists in sync.

 

I get my two lists, A and B, and select just the IDs into two new arrays (not required, but I needed to cast a Salesforce string to an int for comparison). If the array lengths don't match, we've deleted from List B. I loop through List A, and if that item is not in List B, update List A by setting an IsActive flag to false (didn't want to delete the Sharepoint item for legacy use).

 

This would be considerably easier if Flow had an EXCEPT function! It has UNION and INTERSECTION, not sure why it's missing except: https://docs.microsoft.com/en-us/azure/logic-apps/logic-apps-workflow-definition-language#functions

 

From https://www.essentialsql.com 

UnionInsersectExcept.jpg

 

My Flow:

2018-04-18_17-35-19.png

View solution in original post

25 REPLIES 25
Super User
Super User

Re: Comparing Two Lists and Finding Differences

Have you tried setting the run after on the condition to on failure?

Community Support Team
Community Support Team

Re: Comparing Two Lists and Finding Differences

Hi @ Bjorn,

 

The 404 error accurs because you couldn't use the deleted record id to search for record in Salesforce Account,and if you use the deleted record id to search for record in Salesforce Account, the flow would run failed because this record doesn't exists.

 

 

Do you want to sync the account list with the Salesforce Account?

Do you have problem sync the account list with the Salesforce Account which is deleted from Salesforce Account?

 

Microsoft flow Salesforce connector support the trigger "When a record is created" and "When a record is modified", the trigger "When a record is deleted" of Salesforce connector is not supported in Microsoft Flow currently, If you would like this feature to be added in Microsoft Flow, please submit an idea to Flow Ideas Forum:

https://powerusers.microsoft.com/t5/Flow-Ideas/idb-p/FlowIdeas

 

As an alternative way, you could take a try to create the flow as below to delete the items in sharepoint list which is deleted in the Salesforce Account:

 

Capture.PNG

Capture.PNG

 

Please have a try and let me know if it works.

 

Regards,
Alice Zhang

Bjorn
Level 8

Re: Comparing Two Lists and Finding Differences

Thanks for the replies. What I'm really looking for is a way to compare two lists to find mismatches. In any other language, I'd loop through one array, and for each item, check if it exists in the second array. Your suggestion loops through a list, and for each item, does a 'Get Records'. Running a seperate query on each item could get expensive when my list has thousands of items. Can't I just query for two lists and then compare them within Flow?

 

This idea is pretty much what I'm trying to do:

https://powerusers.microsoft.com/t5/Building-Flows/Comparing-File-Lists/td-p/64178

 

But I'm not having luck with it. I also tried the idea below, but I'm having trouble with my 'Contains' syntax:

 

2018-04-11_15-03-19.png2018-04-11_15-04-32.png

 

My Salesforce 'Get records' returns the body:

{

  "@odata.context""https://msmanaged-na.azure-apim.net/apim/salesforce/XXXXXXX/$metadata#datasets('default')/tables('Account')/items",
  "value": [
    {
      "@odata.etag""",
      "ItemInternalId""XXXXXX",
      "SharePoint_ID__c""556"
    },
    ...

 

 

In my case, Sharepoint "ID" is equal to the Salesforce "SharePoint_ID__c". But my condition:

@not(contains(body('Get_records'), items('Apply_to_each')?['ID']))

 

isn't correct. Do you know what the correct syntax is for contains? I also tried body('Get_records')?['SharePoint_ID__c'] 

thinking it needed the key, but that's also a type mismatch.

 

 

Highlighted
Bjorn
Level 8

Re: Comparing Two Lists and Finding Differences

I finally got it working, posting this for anyone who needs a way to keep lists in sync.

 

I get my two lists, A and B, and select just the IDs into two new arrays (not required, but I needed to cast a Salesforce string to an int for comparison). If the array lengths don't match, we've deleted from List B. I loop through List A, and if that item is not in List B, update List A by setting an IsActive flag to false (didn't want to delete the Sharepoint item for legacy use).

 

This would be considerably easier if Flow had an EXCEPT function! It has UNION and INTERSECTION, not sure why it's missing except: https://docs.microsoft.com/en-us/azure/logic-apps/logic-apps-workflow-definition-language#functions

 

From https://www.essentialsql.com 

UnionInsersectExcept.jpg

 

My Flow:

2018-04-18_17-35-19.png

View solution in original post

kbirstein1
Level: Powered On

Re: Comparing Two Lists and Finding Differences

Hello,

 

Would you kindly give more details about the variables "SharePoint List" and "Salesforce List"? What is the syntax for creating these variables? For instance, I assume you are creating an array variable from the SharePoint list items returned by the "Get Account Master List" but I'd like to see the syntax within the Variable action.

 

Thanks.

 

 

Bjorn
Level 8

Re: Comparing Two Lists and Finding Differences

Correct, I'm taking the output from Salesforce and Sharepoint queries and putting just the ID values into two arrays for easy comparison. Not necessary of course, I could have just compared the original JSON output. But when I started that Flow I kept thinking there was something like a SQL 'except' operator available to filter two arrays. There isn't, so I have to loop through the first array to find the IDs not in the second array.

 

2018-06-18_11-08-34.png

alsey
Level: Power Up

Re: Comparing Two Lists and Finding Differences

Hi @Bjorn,

Can you expand the each step in full so we can see the logic of each step.

This will help myself and others.

Regards,
Alistair

Aaronpatel
Level: Powered On

Re: Comparing Two Lists and Finding Differences

Hi,

Is it possible to compare two SharePoint lists based on a unquie key (only exist once per column in each list) and pull a set of records that don't appear in both list?  I am comparing a master list with another list to find the difference and email customer to complete their survey.  Eventually, both list should have the same number of unique records and I would know all surveys are completed.

Bjorn
Level 8

Re: Comparing Two Lists and Finding Differences

Here's the expanded version. This Flow sets an inactive flag on a SharePoint list item when that item no longer exists in Salesforce. On the Salesforce Account object I added a custom field (Sharepoint__ID__c) that stores the corresponding SharePoint item ID. I have another Flow that is triggered onChange of Salesforce Accounts  -- it adds/updates the SharePoint item, and populates the Saleforce custom field with the SharePoint ID if it's blank.

 

  • Every 8 hours query the SharePoint Account list, put the ID's into an array.
  • Get the Salesforce Accounts, put Sharepoint__ID__c into an array.
  • Create an empty array to store any deleted Account IDs.
  • Compare the counts of the Salesforce and SharePoint ID arrays. If they're not the same they're out of sync, loop through the SharePoint ID array and see if that ID is also in the Salesforce ID array. If not, add the missing ID to the deleted Account array.
  • Loop through the deleted Account array and update the SharePoint item by setting the IsActive flag to No.

 

flow1.png

flow2.pngflow3.png

 

  • One final thing to note: on the settings for both the Salesforce and SharePoint queries, I turned on pagination and set the limit to 5000. I have ~1000 Accounts I'm keeping in sync, so we want to make sure we get all the records to compare.

flow4.png

Bjorn
Level 8

Re: Comparing Two Lists and Finding Differences

Yeah, that's effectively what I'm doing here. In your parent list add an integer column and store the ID of the child item. Then whenever something changes on the parent list, update the child list accordingly. In our SharePoint I use an Account list in 5 different site collections and store the 5 child IDs in the master list to keep things in sync. I could have used a third-party cross-site lookup app I suppose, but opted to use the Flow parent/child approach instead. If your two lists are in the same site collection maybe you could use a lookup column instead?

alsey
Level: Power Up

Re: Comparing Two Lists and Finding Differences

Hi @Bjorn,

Thanks for your expanded flow. This helps alot in comparing expressions and seeing how you used flow in each step.

Regards,
Al
Aaronpatel
Level: Powered On

Re: Comparing Two Lists and Finding Differences

@Bjorn.  Thank you for the detailed explanation.  

Aaronpatel
Level: Powered On

Re: Comparing Two Lists and Finding Differences

 @Bjorn I did step by step pretty close to what you have described and my flow complies fine.  After the run it show "False" on "Same Number of Account" step.  I am wondering how to debug it. The log it correct, since number of records in the SharePoint lists don't match it should give a negative response.  

At your convenience please look at the attached picture for my SP lists and advice.

All I am trying to do is compare a master list (SFBForm_Course_2018GF1 - all students with their associated courses) to a child list (SFBForm_Students_1018GF1 - only students that have completed a survey).   The column that matches are Key = Stu_Email_Course_Key.  I want to see a list of students that haven't completed a survey to I can send them a reminder email.

Best

Aaron.

 

Bjorn
Level 8

Re: Comparing Two Lists and Finding Differences

I don't see any atatched images.

Aaronpatel
Level: Powered On

Re: Comparing Two Lists and Finding Differences

My apology.Flow_SP_List_101718.jpg

Bjorn
Level 8

Re: Comparing Two Lists and Finding Differences

We would need to see your Flow in order to help you.

Aaronpatel
Level: Powered On

Re: Comparing Two Lists and Finding Differences

Here is the images of flow. Tired best to caputure all.MS_Flow_101718 (1).jpgMS_Flow_101718 (2).jpg

Bjorn
Level 8

Re: Comparing Two Lists and Finding Differences

A quick look and it seems correct to me, what's failing? Can you screen shot the error?

 

Assuming you're referencing the correct array Output in your ForEachSharepointRecord loop, and that condition is:

@not(contains(body('SelectSurveyList'), items('SelectMasterList')))

Aaronpatel
Level: Powered On

Re: Comparing Two Lists and Finding Differences

@BjornThanks a bunch for helping out!MS_Flow_101718_Error.jpg

I had @not(contains(body('SelectSurveyList'),items('ForEachSharePointRecord'))).  I changed it to @not(contains(body('SelectSurveyList'), items('SelectMasterList'))) and I am getting an error.  Please see the attached image.

Helpful resources

Announcements
firstImage

New Ranks and Rank Icons in April

Read the announcement for more information!

firstImage

Better Together Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Community Summit North America

The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (8,980)