Marketing Cloud Engagement - AMPscript Logic to Handle Null or Empty Values

Data Extension Configuration

One way to prevent this issue from surfacing is to architect your Data Extensions to disallow null or empty values. When you first Create a Data Extension in Contact Builder, there is a "Required" checkbox when creating the fields for your Data Extension. The "Required" checkbox ensures that no data is added to the Data Extension without a value. There is also an option for a "Default Value".  Having a "Default Value" for an attribute disallows null or empty values, while also allowing imports or SQL queries to add rows without values for the required fields. 

Note: Having a Required field without a Default Value can cause processes, such as Imports or SQL Queries, to fail with an error similar to "Cannot insert a NULL value into a non-nullable column". Please test all processes that would interact with a data extension with this configuration before going live. 
 

AMPscript Logic to Handle Null or Empty Values

If the Data Extension already exists and cannot be recreated, or there are scenarios for your business that make sense to have a null or empty value, there are still solutions to solve for this issue. AMPscript gives content creators flexibility to account for these scenarios

Let's look at an example. Let's say the below Data Extension called "OrderConfirmation" is being referenced in an Email.
 

The AMPscript code being executed in an Email is below.
 

%%[
Set @price = Lookup("OrderConfirmation","DiscountPrice","ContactKey",_subscriberkey)

Set @tax = "0.08"
]%%

Price: %%=v(@price)=%%
<br><br>

%%[SET @total = FormatNumber(multiply(@price,@tax),"C2", "en-US")]%%

Price with Logic: %%=v(@price)=%%
<br><br>

Your tax is %%=v(@total)=%%

We want to set the @price variable equal to the DiscountPrice value in the OrderConfirmation Data Extension based on the ContactKey that we are sending to. For the purpose of exhibiting the problematic behavior, we set the @tax variable to a static "0.08". We later print the @price variable in the email using the v() function. For the two example rows, we are able to print the variable with no error. The first would show "Price: 80.00" and the second would show "Price: ". Where we run into issues later in the code is when we attempt to calculate the @total variable using the Multiply function. When we Preview and Test on the first contact "Example1", we see the final line rendered as "Your tax is $6.40". However, when we test "Example2" we get an error banner similar to the below. 
 

Invalid value specified for function parameter.
  Function Name: Multiply
  Parameter Name: Value1
  Parameter Ordinal: 1
  Parameter Type: Numeric
  Submitted Value:

If we add a line of code to account for the null or empty DIscountPrice, the function call no longer fails.
 

%%[
Set @price = Lookup("OrderConfirmation","DiscountPrice","ContactKey",_subscriberkey)

Set @tax = "0.08"
]%%

Price: %%=v(@price)=%%
<br><br>

%%[IF empty(@price) THEN 
SET @price= Lookup("OrderConfirmation","Price","ContactKey",_subscriberkey)
ENDIF]%% 

%%[SET @total = FormatNumber(multiply(@price,@tax),"C2", "en-US")]%%

Price with Logic: %%=v(@price)=%%
<br><br>

Your tax is %%=v(@total)=%%

The IF/THEN logic looks for the @price variable that was initially set equal to the DiscountPrice value from the OrderConfirmation Data Extension. We use the Empty() function to check whether the value is null or empty. If it is, we set the @price variable to another field value, "Price" instead. Assuming that Price is a required field, this should prevent any errors from occurring in the Multiply function due to null or empty values. 
 

Resources

• AMPscript 201
• AMPscript 301
• Marketing Cloud Alert Manager Errors
• Marketing Cloud AMPscript OMM_FUNC_EXEC_ERROR