Perhaps I am a veteran at comparing lists but I certainly did not *feel* like it this week! (Ugh, perception versus reality, we meet again). A quick Google search will show a downloadable action from my friends at UnofficialSF which solves a similar problem. However, I had some trouble configuring it and I was looking for values that are MISSING from one collection variable rather than values that appear in BOTH. I think this action can produce this result, but I’m happy to say that I was able to get to my desired outcome with only standard Flow elements. Here’s how I did it (and you can too!)

the story

At my day job, we give grants to nonprofits. Some grants are fulfilled by putting a check in the mail; others are fulfilled by “direct deposit.” When a nonprofit sends us their direct deposit info, we need to update all of their open grants with specific info. One of these updates is adding contacts to these grants, so that they get a notification when the direct deposit payment is processed. While there may be many contacts at an Org, the only contacts that should get added are Contacts that are designated to receive payment notifications. The thing that kicks off all of these activities is updating a “Direct Deposit” checkbox on the nonprofit org record. All of the Contact sorting and linking should be automated.

the technical version

Let’s assume we have an Organization, a bunch of Contacts, and a bunch of Opportunities. If a field on the Org gets updated, we need to add some Contacts to all of the Opportunities (as Opportunity Contact Roles). The Contacts that get connected to Opportunities have a custom field where Random_Criteria_Checkbox__c=TRUE. However, some Opportunities already have some OCRs, so we need to sort Contacts that already exist as OCRs and OCRs that must be created. Then, we need to create the rest of the OCRs.

the first try

My first attempt at solving this problem involved a “nested loop” where I compared each Contact to each Contact Role.

Fortunately, this DID add contact roles for everyone who did not already have a Contact Role.

UNfortunately, it ALSO added duplicate Contact Roles for contacts that already existed.

Let’s explore why that happened (green box is working properly, red box is working as designed but producing bad data – later in this blog post, we will replace the red box with a new solution).

Let’s say we have two contacts, Sam and Emily. Both should receive payment notifications. One is connected to an Opportunity and the other isn’t. How will the Flow (as it is designed in the pic above) handle this?

  1. Let’s assume there is only one Opportunity, so we’ll do the first Loop only once. We have a collection variable to scoop up existing OCRs for that Opportunity.
    *we are not supposed to do a Get operation inside of a Loop, but I don’t see a way around it!
  2. We know one OCR exists (Sam), so we are on the left side of the Decision Element. (There is no problem with the right path of. theDecision Element, so we are going to ignore it)
  3. Now we loop through the Contacts.
    • We’ll do Sam first.
      • For Sam, we compare Sam’s Id to the Contact Id in the first OCR. Sam=Sam, so we do nothing!
      • For Emily, we compare Sam’s Id to Emily’s Id. Emily =! Sam, so we create another Sam junction record (OCR).
    • Now do the second loop through the Contacts. It’s Emily’s turn.
      • OCR 1 is Sam. Sam =! Emily, so we create another Emily junction record (OCR).
      • OCR2 is Emily. Emily=Emily, so we do nothing.
  4. We use a collection variable to stage the new OCRs so that we can save computing power by creating them all at once. So, now we create a Sam OCR and an Emily OCR. (More field values are assigned in the Flow).
  5. UH OH. Now we have 2 OCRs each for Sam^2 and Emily^2 … connecting them to the same Opportunity. YIKES.

Reality: This solution is not perfect and I don’t recommend that you do it. But on the other hand, IT WORKED for us, and for a long time! Maybe because many Orgs just didn’t have a lot of contacts signed up for Payment Notifications. It took months/maybe over a year to notice/locate the problem and in the meantime, we benefitted a lot from the automation. I’m not proud of my mistake, but I’m proud of my impact by contributing, reflecting, learning, and reworking, constantly striving for improvement.

the better solution

From identifying the problem to solving it took about 2 weeks, and 3 days of actively feeling stuck. Blargh? Here are the main features of the refactor:

  1. In the green highlight below, you will see that there are two sequential loops rather than a nested loop (loop inside of a loop). This makes sure that that we compare each contact to the list of OCRs only once, which avoids the duplicate OCR proliferation problem.
  2. Compare each Contact Id to [list of contact ids from OCR list] rather than OCRs one at a time
  3. Using a variable and clearing it out at the end (“set OCR list to zero”) (I’ll explain this more below)

This version of the Flow starts out the same way as the previous one (scroll down for side by side comparison).

After the split for Opportunities with existing OCRs, we do a special operation where we loop through the OCRs and store the Contact Ids in a Text Variable.

HUH?!

Yup. You cannot compare “lists” in two Collection Variables, so instead, I moved the Contact Ids to a list that looks like this: (well, these are Opportunity Ids, but you get the idea)

006R000000c8AeqIAE, 006R000000c8CaPIAU

How does one achieve this?

You need to start out with a Variable and a Formula.

My Variable looks like this (image below). Basically, it is a placeholder for Ids that will be populated later.

My Formula resource is set up as follows:

  • Name = f_appendContactIdsFromOCR
  • Type = Text
  • Formula plain text = 
{!ContactIdsFromOCRList} &','& {!Loop_through_OCRs_for_this_Request.Contact.Id}

The formula takes whatever was in the list [variable], stores it, adds a comma, and then adds another value to the end. Ever time the Flow loops through an OCR, the ContactIdsFromOCRList variable gets longer.

Ok, now that we have a Variable and a Formula, let’s put them to work.

We are going to loop through all of the OCRs for the Opportunity we are on, and store the Contact Ids one at a time. That is the first loop that you see in the Green Highlighted box above.

Let’s take a closer look at the Assignment Element in that loop, because it was tricky for me to get.

This Assignment is where we “store” the current (latest) value of the Formula! Every time the Flow goes through this loop, the ContactIdsFromOCRList gets longer.

We are “converting” a list of records to a list of Ids so that we can use the Ids later. And that “later” moment is now!

Now is when we finally loop through the Contacts who should receive notifications. OMG. We queried for them so long ago!

The Decision Element here is very special. Let’s open it up and take a look:

Here is the magic moment where we get to look for the Contact Id in the list of existing Contacts connected to the Opportunity via OCR. 🎉🎉🎉🎉🎉🎉

Before we exit the Opportunity Loop Iteration and move on to the next Opportunity, we need to clear out the list of Contact Ids that are stored in the ContactIdsFromOCRList variable. I do that by assigning the variable a value of <blank> so it is ready for the next rodeo.

The rest of the Flow has stayed exactly the same. If the Contact is already an OCR, do nothing. If the Contact is NOT an OCR, assign values and stage it. Last, but not least, create the damn OCRs.

Comparison of two Flows

This image has an empty alt attribute; its file name is Screen-Shot-2022-12-08-at-6.12.59-PM-2-844x1024.png

how i solved it

I was not born knowing how to do this.

Heck, I didn’t even know how to do this a few weeks ago!

I did a lot of tinkering, got frustrated, took a nap, complained to a friend, and even set up a Zoom to get some support (but I figured it out before the Zoom date came around). I also made a video to celebrate the solution when I finally got it working.

I also consulted two enormously helpful forum posts that got me unstuck. I hope that this blog post can put these forum posts in dialogue and light the path for other people trying to solve a similar issue. May the SEO be forever in my favor!

Leave a Reply