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]

In that case that isn't the right name for the list. How did you determine it was the right name?

[Jo_Pitts1]

@SanfordWhiteman ,

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

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

[Jo_Pitts1]

Yep - that is what I presumed was happening.

See... I really am learning 🙂

It is why I tend to be overly diligent in declaring my variables.  Of course, you can't do that with a cList.  

@michaelstancil , do you have all the fields you need selected in the tree to the right of the token editor?

[michaelstancil]

@Jo_Pitts1 @SanfordWhiteman The object is the correct one, and I've verified it does have data within SF, as well as Marketo, as I can see the values in each specific field when I apply the object as a list condition. Additionally, I am selecting all of the fields within the custom object when I'm in the token editor, as well as the zip from the opportunity. However, when I try and check for values in that custom object, or several other custom objects we have from SF, they all return as false.

#set( $HackermanHomesSold = !!${OtherCustomObject__cList})

That got me thinking that while I am able to see the fields as a list condition, there are no eligible contacts. Which then let me to think further that the issue is that the custom object is not associated with a lead/person/account, even though it exists under the contact level (and also as the account). Could this be the reason? This is how it's currently setup on the contact level:

[SanfordWhiteman · Accepted solution]

#set( $HackermanHomesSold = !!${OtherCustomObject__cList})

should be written

#set( $HackermanHomesSold = !!$OtherCustomObject__cList )

(don’t use curly braces inside operators, only for output)

but if it always sets $HackermanHomesSold to false, that means $OtherCustomObject__cList does not exist (not is empty, but does not exist , meaning implicitly null in Velocity).

---

That got me thinking that while I am able to see the fields as a list condition, there are no eligible contacts.

---

Then you would never expect Velocity to see the objects! Sometimes (i.e. 2nd-level+ objects) a Smart List filter can see objects but Velocity can’t. But never the other way around.

---

Which then let me to think further that the issue is that the custom object is not associated with a lead/person/account, even though it exists under the contact level (and also as the account).

---

If the person is a Lead, and the CO is only available via the Contact, then you absolutely would not see the objects.

[michaelstancil]

@SanfordWhiteman Got it on the operators vs. output!

---

---

Then you would never expect Velocity to see the objects! Sometimes (i.e. 2nd-level+ objects) a Smart List filter can see objects but Velocity can’t. But never the other way around.

---

If the person is a Lead, and the CO is only available via the Contact, then you absolutely would not see the objects.

---

Well, I think we figured out the why it's not working in the end. Good news is that I feel a lot better about this process in practice, so when this is configured correctly. I'm going to reach out to our team and see if we can put these values one step down.

[Jo_Pitts1]

@michaelstancil , you know your piece of code has a double not in it?

[michaelstancil]

@Jo_Pitts1 Oops! Thanks for catching!

[Jo_Pitts1]

@michaelstancil ,

try wrapping all your core code in this #if( ! $display.alt($myList,"").isEmpty() )

(replacing myList with your lists)

#if( ! $display.alt($myList,"").isEmpty() )
  #foreach(myItem in myList)
    #set($thing=myItem.thing)
  #end
#end

(This code was provided by @SanfordWhiteman  when I was getting a similar error).  

So, assign your lists to local variables.  However, before you do any iterations of them test for emptiness

Cheers

[Jo_Pitts1]

@SanfordWhiteman ,

I'm curious.  When I was having the same conundrum as @michaelstancil , you gave me a 'long form' version..

namely: 

#if( ! $display.alt($myList,"").isEmpty() )

Since then you've gone with the simpler 

#if( !$myList.isEmpty() )

For my continuing education and edification, why the change in recommendation (sorry if this seems like a **bleep** question, I'm just trying to get my Velocity as good as it possibly can be)?

Cheers

[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

[michaelstancil]

This is the error I get trying that

 

[Jo_Pitts1]

@SanfordWhiteman ,

Should the empty guard be && or || ?

Cheers

Jo

[SanfordWhiteman]

&& because they both need to be non-empty.

[Jo_Pitts1]

Ahh.. I misread what you'd done there (it's been an overly  long day 🙂 ).

Somewhere in my head I'd seen that you'd only had a single ! and bracketed the two .empty tests (goodness knows how)

i.e. (!( myList.isEmpty() && myOtherList.isEmpty() )

which would have have required an ||.

[Jo_Pitts1]

@SanfordWhiteman ,

we must stop meeting like this.

SNAP (Again)