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

[SanfordWhiteman · Accepted solution]

---

how is it getting past the empty guard?  Is it because an undefined list is different to an empty list?

---

A nonexistent list (or any nonexistent reference) is short-circuited to null. And !null is always true.

Note: isEmpty() is never called in this case because there‘s nothing to call it on. So

#if( !$nonexistentThing.isEmpty() )

is effectively the same as

#if( !$nonexistentThing )

As usual, this digs into the details of what is true, what is false, and what is somewhere between the 2. Which is why you want your $references to always exist!

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

Sorry, missing a dollar sign up there. Edited.

[michaelstancil]

@Jo_Pitts1 Thanks for that! Silly question but how do I input the no value check?

@SanfordWhiteman I updated based on the edit, and now I get:

Could it be where I'm placing the Token?

[SanfordWhiteman]

Sure you don’t have another {{my.token}} in the email that’s specifically pointing to the 0th element?

[michaelstancil]

0th element? 

I have a few other tokens that come after that reference the created merged list, such as this:

${mergedOpptyAndAVMList.get(0).ITEMFROMAVMLIST__c}

[SanfordWhiteman]

Well you can’t have those without checking if the list is empty. Trying to referencing the first/[0]th element of an empty list is always a fatal error. Just like referencing the second/[1]th element of a list with only one element.

[michaelstancil]

Got it, I've updated the first token that calls this to as follows:

#if( ! $display.alt($mergedOpptyAndAVMList,"").isEmpty() )
  #foreach(ITEMFROMAVM in mergedOpptyAndAVMList)
    #set($AVMCurrentValue=${mergedOpptyAndAVMList.get(0).ITEMFROMAVM})

I get this error:

 

[SanfordWhiteman]

Large number of errors there. Typos and logic as a whole.

This will at least compile (assuming it’s only the top of your token and you’re #ending that #if and #foreach later on):

#if( !$display.alt($mergedOpptyAndAVMList,[]).isEmpty() )
  #foreach( $avm in $mergedOpptyAndAVMList )
    ## $avm is set to the current item in $mergedOpptyAndAVMList

But I don’t get what you’re trying to do with the list. As it iterates, $avm is set to each object in the list in turn. You should never, ever be referencing object [0].

[michaelstancil]

This might be where there is some coding teaching here, what is object[0]? Sorry, I'm trying based on what languages and logic structures I know, but I'm sure it's not the prettiest, apologies for that and thank you again for helping.

As far as what I'm doing, maybe it will help if I user story it. Now that we have the mergedOpptyAndAVMList, there are three different values based on what the list was merged on that I want to display in the email. I had created the three tokens to pull each, and I was doing each as mergedOpptyAndAVMList.Item1, mergedOpptyAndAVMList.Item2, and mergedOpptyAndAVMList.Item3, where the .item is what the item was called from the AVM list.

Does that make more sense?

[Jo_Pitts1]

@michaelstancil ,

I think there is an understanding gap around how the foreach loop works:

#if( ! $display.alt($mergedOpptyAndAVMList,"").isEmpty() )
  #foreach(ITEMFROMAVM in mergedOpptyAndAVMList)
    #set($AVMCurrentValue=${mergedOpptyAndAVMList.get(0).ITEMFROMAVM})

1. Your first line is the empty guard... that makes sure we don't enter the loop if the list is empty.. that's hunky dory!
2. The second line is your foreach loop.  It works by iterating the entire list.  On each iteration, it assigns the current element in the list to the named variable.  In your example, each iteration assigns an item from mergedOpptyAndAVMList to ITEMFROMAVM.  If you have (for example) 6 items in your list, the foreach loop will be executed 6 times, and each time ITEMFROMAVM will be set to the appropriate value in the list.
3. it feels like your third line is you attempting to do what the second line is doing as of right.  You don't need this line.

By object[0], @SanfordWhiteman is referring to the generic notion of the zero indexed item of a list (the zero indexed item is the FIRST item).  If you try to access the [0] item in an empty list, it errors as there is no first item (as it is empty).  Substitute 'object' for the name of the list you are using (i.e. mergedOpptyAndAVMList) to make it specific to your use case.

I have to profess, I don't understand your use case as you've explained it.  Sorry.

Cheers

Jo

[michaelstancil]

@Jo_Pitts1  @SanfordWhiteman 

So I asked our engineering team for some assistance, and we did some debugging. We're able to go into the Opportunity Loop, but when we loop through the AVM, nothing happens. So in the code below, we get pineapple, but not oranges. Is there something basic I'm missing? I've checked ALL of the values within the AVM custom object in the script token.

#set( $HackermanValue1= "Apple")
#if( !$OpportunityList.isEmpty() && !$AVM_Values__cList.isEmpty() )
  #foreach( $oppty in $OpportunityList )
   #set( $HackermanValue1 = "pineapple")   
    #foreach( $avm in $AVM_Values__cList )
     #set( $HackermanValue1 = "oranges")
    #end      
  #end
#end
${HackermanValue1}

Am I missing something obvious? I can see values within AVM dataset, so I know they aren't empty.

[SanfordWhiteman]

---

Am I missing something obvious? I can see values within AM dataset, so I know they aren't empty.

---

What do you see when you simply output

 ${AVM_Values__cList}

 ?

That needs to be part of your testing — dumping the raw data.

[michaelstancil]

@SanfordWhiteman The result in the email is:

${AVM_Values__cList} pineapple