SOLVED

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

Go to solution
michaelstancil
Level 3

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

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?

3 ACCEPTED SOLUTIONS

Accepted Solutions
SanfordWhiteman
Level 10 - Community Moderator

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

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.

View solution in original post

SanfordWhiteman
Level 10 - Community Moderator

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


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!

View solution in original post

SanfordWhiteman
Level 10 - Community Moderator

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

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

View solution in original post

37 REPLIES 37
SanfordWhiteman
Level 10 - Community Moderator

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


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
Level 3

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

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
Level 10 - Community Moderator

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

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
Level 3

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

@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
Level 10 - Community Moderator

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


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
Level 3

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

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:

Screen Shot 2021-12-16 at 4.16.32 PM.png

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
Level 10 - Community Moderator

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

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
Level 3

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

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:

Screen Shot 2021-12-16 at 8.43.29 PM.png

SanfordWhiteman
Level 10 - Community Moderator

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

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