Only execute Data Extension AMPscript functions if the Data Extension exists

Question

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?

Answer 1

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)=%%

Answer 2

EDIT

I would use @BradSapkota's answer (utilizing SSJS try/catch) for this issue, but wanted to leave this here for reference in case this answer can help another related use case.

---

I would recommend doing this via WSProxy - but even so, it can run a bit slow and should never be used in a sendable environment.

As you need it inside an email, your only real choice would be to have this code on a CloudPage and then do an HTTPGET to this page and send across the DE as a parameter in the get. You can then have the page return a value of "Exists" or "Empty" via the GET that you can then parse.

CloudPage

You can do a filtered retrieve request and then do a rowcount on the reponse to see if it returned empty or if it returns the DE info.

For Example:

var prox = new Script.Util.WSProxy();
var cols = ["Name"];
var filter = {
    Property: "Name",
    SimpleOperator: "equals",
    Value: "yourDE"
};
var desc = prox.retrieve("DataExtension", cols, filter); 

Example Empty Response:

{
  "Status":"OK",
  "RequestID":"d1db75de-5736-4634-ab71-297e7e7d1571",
  "Results":[],
  "HasMoreRows":false
}

Example Existing Response:

{
  "Status":"OK",
  "RequestID":"d333fcc9-8b0d-4799-ace6-50340fbe482d",
  "Results":[{"Name":"yourDE"}],
  "HasMoreRows":false
}

and from there you parse the "Results" array to see if it contains the data extension in question's info. If it does not, then you know it does not exist.

For example:

 if(desc.Results.length > 0) {

     Write('Exists');

} else {

    Write('Empty')

}

Inside Email

You would place the following GET script inside the email to return the results from the CP.

%%[
    SET @deExist = HTTPGet(@yourURL)

    IF @deExist == "Exists" THEN

      /* Your stuff if it exists*/

    ELSE

      /* Your stuff if empty */

    ENDIF

]%%