How to lookup a value in one SFDC field (like Vlookup)

[michaelstancil]

Hi there,

So I'm more versed in Excel, so that's how I'll explain what I'm trying to do. I have a data set on a contact level that contains several items based off zip code. My opportunities have a zip code as well, and I want to get those items (one at a time) based on that zip code. 

Would creating a token that had a function of iF opportunityZIP matches contactZip, pull contactItem1 work?

[SanfordWhiteman]

---

Would creating a token that had a function of iF opportunityZIP matches contactZip, pull contactItem1 work?

---

Well, that’s not actual code. So hard to say if it would “work” or not. 🙂

In Velocity, if you want to match objects in List A (= Opportunities) based on whether a certain property value matches equals some property value in Object B:

• #foreach over List A
• check if each $iteratorVariable.property1.equals($lead.property2)
• do something interesting on a match

[michaelstancil]

I think I follow, so would this be correct in theory? Forgive me for sounding it out

#foreach over (${OpportunityList)
#if(${OpportunityList.get(0).Zipcode__c}.equals(${AVM_Values__cList.get(0).Zip_Code__c}))
#set( $TOKEN = $ITEM FROM CONTACT LIST)
#end

[SanfordWhiteman · Accepted solution]

Well... not quite. (For one thing, you’re continually referencing the first/zero-th object in the list, which is a mistake a lot of people make.)

The shape of the new AVM object isn’t really clear, but it now sounds like you’re trying to intersect 2 different lists based on the equality of a specific property. Originally, it seemed like you were trying to intersect a list (the Opportunities) with a single object (the Lead).

Here’s one way to do a list intersection:

#set( $mergedOpptyAndAVMList = [] )
#foreach( $oppty in $OpportunityList )
  #foreach( $avm in $AVM_Values__cList )
    #if( $oppty.Zipcode__c.equals($avm.Zip_Code__c) )
      #set( $void = $oppty.putAll($avm) )
      #set( $void = $mergedOpptyAndAVMList.add($oppty) )
    #end
  #end
#end

This will leave you with the list $mergedOpptyAndAVMList — a new list where each member is an Opportunity object + AVM object merged into a single meta-object.  The objects are “joined” on their respective zip code properties.

[michaelstancil]

@SanfordWhiteman I think I am getting it, sorry to be so dense.

I am replacing the $oppty and $avm with the full variable name, yes, and then the void with the token name, or do I need to add another line like the below after the formula?

And then with this $mergedOpptyAndAVMList, does that live specifically within this email token? 

[SanfordWhiteman]

---

I am replacing the $oppty and $avm with the full variable name, yes, and then the void with the token name, or do I need to add another line like the below after the formula?

---

Those are iterator variable names, you don’t need to replace them. They are dynamically assigned to each object in the list, as the loop continues.

Just like this in JS:

for( let whatever of ["a","b","c"] ) { 
  // whatever is assigned to “a”, “b”, “c” in turn
}

---

and then the void with the token name

---

No, $void is literally $void.

---

And then with this $mergedOpptyAndAVMList, does that live specifically within this email token?

---

It‘s accessible in this token and any other Email Script token included after that token in the HTML.

[michaelstancil]

Gotcha. So what I've done is change the tokens that reference the AVMList to reference the new merged list, so for example:

${mergedOpptyAndAVMList.get(0).ITEMFROMORIGINALLIST__c}

I've also put the the token that runs the merge at the top, but when I try and send a sample I get the following error:

These are the contents of the token, they seem to be identical:

#set( $mergedOpptyAndAVMList = [] )
#foreach( $oppty in $OpportunityList )
  #foreach( $avm in $AVM_Values__cList )
    #if( $oppty.Zipcode__c.equals($avm.Zip_Code__c) )
      #set( $void = $oppty.putAll($avm) )
      #set( $void = $mergedOpptyAndAVMList.add($oppty) )
    #end
  #end
#end

Any ideas?

[SanfordWhiteman]

You can’t use Send Sample to test Velocity.

This error means at least one of the lists is empty — which you should expect in Sample mode.

But unless you make sure to only qualify people for the send who have at least one Opportunity or AVM, it can happen in a real email as well. So you can check for $someList.isEmpty() to make sure this never happens.

[michaelstancil]

Ah I see.

I've prepared a way to send a live email to myself, with the list merge at the top of the body, but when I approve and close the email I get the following:

[SanfordWhiteman]

Wrap it in an empty guard:

#set( $mergedOpptyAndAVMList = [] )
#if( !$OpportunityList.isEmpty() && !$AVM_Values__cList.isEmpty() )
  #foreach( $oppty in $OpportunityList )
    #foreach( $avm in $AVM_Values__cList )
      #if( $oppty.Zipcode__c.equals($avm.Zip_Code__c) )
        #set( $void = $oppty.putAll($avm) )
        #set( $void = $mergedOpptyAndAVMList.add($oppty) )
      #end
    #end
  #end
#end