Help Required: Using an Empty function to set a variable

Question

I would like to be able to set a value (and print a content area into my email) where an expression evals as empty (IF EMPTY). What would be the best way to achieve this?

The journey is triggered by Sales Cloud updates when a prospect becomes a customer. At present, I've tried pulling from a synchronized DE as well as adding the field into the entry data and setting it directly from the Journey DE but have been unsuccessful with both attempts.

Here's each attempt:

1. Synchronized DE -

<!--%%[
VAR @fibreDate, @newFibre
SET @rows = LookupRows("Opportunity_Salesforce","Id", "Id")
SET @row = row(@rows,1)
SET @fibreDate = field(@row,"Opportunity:Fibre_Service__r:First_Fibre_Connected_Date__c")
IF empty(@fibreDate) OR @fibreDate == "" THEN
 SET @newFibre = "True"
ENDIF
]%% -->

%%[IF not Empty(@hasUFB) AND @newFibre=="True" THEN]%%
<!--Holiday-Message-BEGIN-->
<table class="email-container" role="presentation" style="max-width: 600px !important; background-color:#e5f1ff" width="100%" cellspacing="0" cellpadding="0" border="0" align="center">
  <tr>
    <td style="padding:10px 0px; background-color:#fff4e5">
      <table role="presentation" cellspacing="0" cellpadding="0" border="0" align="center">
        <tr>
          <td style="padding-top:5px">
            <img alt="Important info icon" src="image url" style="max-width:34px;height:34px;" width="34" height="34">
          </td>
          <td style="text-align:center;padding:10px 10px 10px 10px;">
            <h4 style="margin:0; color:#F7941D;font-size:20px;font-weight:bold; text-align:center;font-family:Calibri,Arial,sans-serif;">
              HasFibre and FibreNew YOUR FIBRE
            </h4>
          </td>
        </tr>
      </table>
      <table role="presentation" width="90%" cellspacing="0" cellpadding="0" border="0" align="center">
        <tr>
          <td class="mobile-padding" style="padding:0 20px 0px; font-size: 16px; line-height: 140%; color: #666666; text-align: left;">
            <p style="font-family:Calibri,Arial,sans-serif;">
              Please note that over the holiday season there may be a delay with your fibre connection, and although we will do our best it may not be completed until mid-January. %%=IIF(@offerType=="@youChoose","Any connection delays will also impact the delivery of your chosen incentive.","")=%%</p>
          </td>
        </tr>
      </table>
    </td>
  </tr>
</table>
%%[ENDIF]%%
<!--Holiday-Message-END-->

2. Directly from the Journey DE -

<!--%%[
VAR @fibreDate, @newFibre
SET @fibreDate = Opportunity:Fibre_Service__r:First_Fibre_Connected_Date__c
IF not Empty(@hasUFB) AND empty(@fibreDate) THEN
 SET @newFibre = "True"
ENDIF
]%% -->

%%[IF @newFibre=="True" THEN]%%<!--Holiday-Message-BEGIN-->
<table class="email-container" role="presentation" style="max-width: 600px !important; background-color:#e5f1ff" width="100%" cellspacing="0" cellpadding="0" border="0" align="center">
  <tr>
    <td style="padding:10px 0px; background-color:#fff4e5">
      <table role="presentation" cellspacing="0" cellpadding="0" border="0" align="center">
        <tr>
          <td style="padding-top:5px">
            <img alt="Important info icon" src="image url" style="max-width:34px;height:34px;" width="34" height="34">
          </td>
          <td style="text-align:center;padding:10px 10px 10px 10px;">
            <h4 style="margin:0; color:#F7941D;font-size:20px;font-weight:bold; text-align:center;font-family:Calibri,Arial,sans-serif;">
              Just HasFibre YOUR FIBRE
            </h4>
          </td>
        </tr>
      </table>
      <table role="presentation" width="90%" cellspacing="0" cellpadding="0" border="0" align="center">
        <tr>
          <td class="mobile-padding" style="padding:0 20px 0px; font-size: 16px; line-height: 140%; color: #666666; text-align: left;">
            <p style="font-family:Calibri,Arial,sans-serif;">
              Please note that over the holiday season there may be a delay with your fibre connection, and although we will do our best it may not be completed until mid-January. %%=IIF(@offerType=="@youChoose","Any connection delays will also impact the delivery of your chosen incentive.","")=%%</p>
          </td>
        </tr>
      </table>
    </td>
  </tr>
</table>
%%[ENDIF]%%
<!--Holiday-Message-END-->

Looking at my test data, I can see the Journey data includes null/empty value for that particular field - what am I doing wrong?

Any help would be greatly appreciated.

Answer 1

Your code structure generally looks fine, but your lookups are a bit tangled. The variable values are probably not what's expected.

I think you can scroll down to the last line of code in this comment and find a quick solution, but for a better understanding of what's happening, here's the full rundown.

To help debugging, after the first endif, in line 8, print the values of your variables: %%=v(@hasUFB)=%% and %%=v(@fibreDate)=%%. Also print %%=v(@newFibre)=%% because why not.

Then, it's weird to do a lookup by ID using lookupRows. An Opportunity ID like any SF ID is unique, so a simple lookup should do, there will never be more than one record found. But of course you should know the opportunity ID to search for if you go to the synchronized DE.

old, example 1.

SET @rows = LookupRows("Opportunity_Salesforce","Id", "Id")
SET @row = row(@rows,1)
SET @fibreDate = field(@row,"Opportunity:Fibre_Service__r:First_Fibre_Connected_Date__c")

In line 1, you set your variable "rows" to all rows where the VALUE of the field "ID" is "Id". As in, you look for rows where the field "id" is physically filled with the two letters "Id". That will be an empty rowset in any circumstance, as the ID will always be an 18 digit string. I cannot imagine you actually intend this, and I would not expect the followup code to work as expected.

So, in this example you have to give your code an opportunity Id to start from. How you find the right opportunity, no idea, that depends on your usecase. Probably some lookup related to your recipients' _subscriberkey. But starting from this you should be able to make progress:

new

Set @myOpp = "00690000002BoMV" /* obviously made this up, use a real one from your system */

SET @fibreDate= Lookup("Opportunity_Salesforce","First_Fibre_Connected_Date__c","Id", "Id")
/* look in ""Opportunity_Salesforce", 
return field value of "First_Fibre_Connected_Date__c" 
where field "Id" 
equals @myOpp */

Of course, you could solve the entire problem of "finding the right opp" by just using the opportunity in the journey DE - your example 2. You used the direct reference to the field name there.

old example 2:

SET @fibreDate = Opportunity:Fibre_Service__r:First_Fibre_Connected_Date__c

Try using AttributeValue to deal with empty values.

References: https://ampscript.guide/attributevalue/ https://ampscript.guide/best-practices-attributevalue-empty/

It is good practice to assume that any of the contextual personalization variables/strings in an email or web page might not exist or be blank. If you reference a Profile Attribute or Data Extension column name that does not exist in your sending audience, your send will terminate with a runtime error. To minimize that possibility, you can use the AttributeValue function to proactively check those contextual variables.

Need AMPScript function to convert string to decimal

new example 2

SET @fibreDate= AttributeValue("Opportunity:Fibre_Service__r:First_Fibre_Connected_Date__c")