0

Having trouble with lookup not returning what's expected.

The @LOTeamName is what I'm having trouble returning. No errors when I run it, it just doesn't return anything even though I know it's there.

%%[

/* Find LO Info */
/* Find OwnerID - Look in contacts first then leads */
Set @rsContacts = LookupRows("Contact_Salesforce","ID",_Subscriberkey)

If RowCount(@rsContacts) > 0 Then
 Set @OwnerID = Field(Row(@rsContacts,1),"OwnerID")
Else

 Set @rsLeads = LookupRows("Lead_Salesforce","ID",_Subscriberkey)

 If RowCount(@rsLeads) > 0 Then
  Set @OwnerID = Field(Row(@rsLeads,1),"OwnerID")
 EndIf

EndIf

)

/* Find user info */
If Not Empty(@OwnerID) Then

 Set @rsUsers = LookupRows("User_Salesforce","Id",@OwnerID)

 if RowCount(@rsUsers) > 0 then

  Set @LOFirstName = Field(Row(@rsUsers,1),"FirstName")
  Set @LOLastName = Field(Row(@rsUsers,1),"LastName")
  Set @LOTitle = Field(Row(@rsUsers,1),"Title")
  Set @LO_NMLS_Number = Field(Row(@rsUsers,1),"LO_NMLS_Number__c")
  Set @LOStreet = Field(Row(@rsUsers,1),"Street")
  Set @LOCity = Field(Row(@rsUsers,1),"City")
  Set @LOState = Field(Row(@rsUsers,1),"State")
  Set @LOPostalCode = Field(Row(@rsUsers,1),"PostalCode")
  Set @LOPhone = Field(Row(@rsUsers,1),"Phone")
  Set @LOMobilePhone = Field(Row(@rsUsers,1),"MobilePhone")
  Set @LOFax = Field(Row(@rsUsers,1),"Fax")
  Set @LOEmail = Field(Row(@rsUsers,1),"Email")
  Set @LO_Branch_URL = Field(Row(@rsUsers,1),"LO_Branch_URL__c")
  Set @LOBranch_NMLS_Number = Field(Row(@rsUsers,1),"Branch_NMLS_Number__c")
  Set @LO_Photo = Field(Row(@rsUsers,1),"CC_PhotoURL__c")

 EndIf 

 If Not Empty (@LOBranch_NMLS_Number) Then

  Set @LOTeamName = Lookup("BranchWebsite", "BranchTeamName_Override__c", "Branch_NMLS_Number__c", @LOBranch_NMLS_Number)

 endif

EndIf
]%%

4
  • Have you verified that (a)LOBranch_NMLS_Number is definitely not empty? It appears a definition of (a)LOTeamName is dependent on this var. – Gortonington Mar 29 '18 at 13:01
  • I have the "If Not Empty (@LOBranch_NMLS_Number) " before the lookup. Does that not suffice? @Gortonington – Stephanie Wood Mar 29 '18 at 14:44
  • I understand that, I am saying if LOBranch_NMLS_Number is empty Then LOTeamName will not fill, regardless if it has value or not. So have you verified that on your tests that LOBranch_NMLS_Number does have a value? – Gortonington Mar 29 '18 at 15:01
  • Yes on my tests I have made sure that LOBranch_NMLS_Number does have value – Stephanie Wood Apr 2 '18 at 13:01
0

Perhaps the field actually has a Null value instead of EMPTY. AMPscript doesn't evaluate for NULL when it's comparing against an attribute.

To get around this try:

IF @LOBranch_NMLS_Number != "" or @LOBranch_NMLS_Number is NOT NULL THEN
0

I generally debug lookup challenges with a series of outputs. Using a debug flag variable allows me to turn the debugging output on and off.

My rule of thumb for debugging is:

Output variable names and values after every lookup and before every conditional

Doing this will help you trace the path through your conditionals.

%%[

var @debug
set @debug = 1

/* Find LO Info */
/* Find OwnerID - Look in contacts first then leads */

Set @rsContacts = LookupRows("Contact_Salesforce","ID",_Subscriberkey)

If RowCount(@rsContacts) > 0 Then
 Set @OwnerID = Field(Row(@rsContacts,1),"OwnerID")
Else

 Set @rsLeads = LookupRows("Lead_Salesforce","ID",_Subscriberkey)

 If RowCount(@rsLeads) > 0 Then
  Set @OwnerID = Field(Row(@rsLeads,1),"OwnerID")
 EndIf

EndIf

/* Find user info */
If Not Empty(@OwnerID) Then

 Set @rsUsers = LookupRows("User_Salesforce","Id",@OwnerID)

 if RowCount(@rsUsers) > 0 then

  Set @LOFirstName = Field(Row(@rsUsers,1),"FirstName")
  Set @LOLastName = Field(Row(@rsUsers,1),"LastName")
  Set @LOTitle = Field(Row(@rsUsers,1),"Title")
  Set @LO_NMLS_Number = Field(Row(@rsUsers,1),"LO_NMLS_Number__c")
  Set @LOStreet = Field(Row(@rsUsers,1),"Street")
  Set @LOCity = Field(Row(@rsUsers,1),"City")
  Set @LOState = Field(Row(@rsUsers,1),"State")
  Set @LOPostalCode = Field(Row(@rsUsers,1),"PostalCode")
  Set @LOPhone = Field(Row(@rsUsers,1),"Phone")
  Set @LOMobilePhone = Field(Row(@rsUsers,1),"MobilePhone")
  Set @LOFax = Field(Row(@rsUsers,1),"Fax")
  Set @LOEmail = Field(Row(@rsUsers,1),"Email")
  Set @LO_Branch_URL = Field(Row(@rsUsers,1),"LO_Branch_URL__c")
  Set @LOBranch_NMLS_Number = Field(Row(@rsUsers,1),"Branch_NMLS_Number__c")
  Set @LO_Photo = Field(Row(@rsUsers,1),"CC_PhotoURL__c")

 EndIf 

 If Not Empty (@LOBranch_NMLS_Number) Then
   Set @LOTeamName = Lookup("BranchWebsite", "BranchTeamName_Override__c", "Branch_NMLS_Number__c", @LOBranch_NMLS_Number)
 endif

EndIf

if @debug == 1 then 
    output(concat("<br>RowCount(@rsContacts): ", RowCount(@rsContacts)))
    output(concat("<br>RowCount(@rsLeads): ", RowCount(@rsLeads)))
    output(concat("<br>ownerId: ", @ownerId))
    output(concat("<br>RowCount(@rsUsers): ", RowCount(@rsUsers)))
    output(concat("<br>@LOBranch_NMLS_Number: ", @LOBranch_NMLS_Number))
    output(concat("<br>@LOTeamName: ", @LOTeamName))
endif

]%%

Reference

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.