SOLVED

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

Go to solution
michaelstancil
Level 3

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

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

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

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

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

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

@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

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

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

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

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

Sorry, missing a dollar sign up there. Edited.

michaelstancil
Level 3

@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:

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

Could it be where I'm placing the Token?

SanfordWhiteman
Level 10 - Community Moderator

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

michaelstancil
Level 3

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

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

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:

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

SanfordWhiteman
Level 10 - Community Moderator

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

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

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

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

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

@SanfordWhiteman The result in the email is:

 

${AVM_Values__cList} pineapple