Comparing Two Lists and Finding Differences

[Bjorn]

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.

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? 

[Pieter_Veenstra]

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

[v-yuazh-msft]

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:

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

Regards,
Alice Zhang

[Bjorn]

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:

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.

[Bjorn · Accepted solution]

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 

My Flow:

[kbirstein1]

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]

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.

[alsey]

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]

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]

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.

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

[Bjorn]

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]

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]

@Bjorn.  Thank you for the detailed explanation.  

[Aaronpatel]

 @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]

I don't see any atatched images.

[Aaronpatel]

My apology.

[Bjorn]

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

[Aaronpatel]

Here is the images of flow. Tired best to caputure all.

[Bjorn]

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]

@BjornThanks a bunch for helping out!

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.