[SalesForce] Only execute Data Extension AMPscript functions if the Data Extension exists

I had a client ask me last week how you can put a conditional statement around a Data Extension function, where if the Data Extension does not exist, then don't process the code within it. The AMPscript is used in an email.

There are many scenarios why you would want to do this. For example, perhaps the Data Extension has been deleted due to an assigned Data Retention Policy.

The following code…

%%[
var @rows
set @rows = LookupRows('DE Name', 'Field', 'Value')
]%%

…returns the error:

The Data Extension name for a LookupRows function call is invalid. A Data Extension of this name does not exist

You can't try counting the rows either. For example, the following code also returns the same error.

%%[
if RowCount(LookupRows('DE Name', 'Field', 'Value')) == 0 then
/* do something */
endif
]%%

I thought that perhaps you could first use WSProxy or platform API functions to try and retrieve the External Key of the DE name, and if it returns a value, then you could set the DE name as a variable in an AMPscript DE function, but this isn't possible as you can't use WSProxy or platform API functions in a send context.

Is there a solution to this?

Best Answer

Since the approach is similar to a previous post, I have reused the same code and made some slight modifications.

I have used the mixture of SSJS and AMPscript to cover this use case for the LookUp Rows.

The script below uses try and catch within SSJS to execute AMPscript and SET a variable which then can be used later in your email to run logic based on the value of that resulted variable.

If you copy and run this straight within an email the result will be false assuming this DE doesn't exist in your BU. However, if you change it to valid DE Name and a valid field in SSJS and render the email the result will be true.

Example:

<script runat="server" language="javascript">
    Platform.Load("core", "1");
    try {
        var CheckDE = DataExtension.Init("DE_Name_TO_Check");
        var CheckDEStatus = CheckDE.Rows.Lookup(["FieldName"], [Value]);
</script>

    %%[SET @DEExists = "true"]%%

<script runat="server" language="javascript">
Platform.Load("core", "1");
    }
    catch (e) {
</script>

    %%[SET @DEExists = "false"]%%

<script runat="server" language="javascript">
Platform.Load("core", "1");
    }
</script>

This is to confirm if DE Exists: %%=v(@DEExists)=%%
Related Topic