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!