3

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?

3

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)=%%
  • 2
    You could also set the @DEExists variable with SSJS to keep the logic within one language like Variable.SetValue("@DEExists", "true"); – Travis Naughton Sep 24 '18 at 2:07
  • While this verifies if the DE exists, it does not solve the issue. As per the second code example in my question, I still want to perform a LookupRows operation if @DEExists == true, but the LookupRows function is still evaluated, even though it's wrapped in a conditional statement, as per this gist. – Eliot Harper Sep 24 '18 at 2:08
  • 1
    The LookUpRows is already performed in the try step, so basically why would you do a LookUpRows twice? or if you do need to perform LookUpRows again, can you not do it within the try block where the AMPscript is set? Since that part of the AMPScript will only execute if the DE and fields are valid. – Brad Sapkota Sep 24 '18 at 2:15
  • 1
    @TravisNaughton sure makes sense. However, I thought OP might want to run more logics using AMPscript within the try and catch block :) – Brad Sapkota Sep 24 '18 at 2:25
  • 1
    @BradSapkota - To speed this up, you don't need to declare the CORE library or Initiate the DE, you can use Platform.Function.LookupRows('DE_Name_To_Check','FieldName',value'); which does not require CORE. It will only be a slight improvement in processing speed, but every millisecond counts inside email processing. Other than that, great job finding a simple solution for a complex issue! – Gortonington Sep 24 '18 at 12:50
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

]%%
  • I mentioned in my question that I need to this in an email. As per my last paragraph in my question, I considered this approach too, but you can’t use WSProxy in an email :-/ – Eliot Harper Sep 23 '18 at 19:43
  • You are right, you did - I apologize! That is what I get for skimming it :( I edited my question to reflect usage inside of an email. – Gortonington Sep 23 '18 at 19:51
  • My only hesitation now in accepting this answer in it's current form is that this will make one request for each email (and others may hit this issue if they try this answered question). If you are sending thousands of emails, this could be problematic. It would be ideal if we could use Content Syndication to make the request once, as the start of the send (assuming that the Data Extension name won't change, and in this scenario, let's say it won't). However I haven't been able to get Before;HTTPGet to work inside of an AMPscript block. – Eliot Harper Sep 23 '18 at 23:26
  • If you can come up with a solution, I'd be happy to accept this, but I don't feel comfortable about accepting it in its current form as I wouldn't want others to implement it and hit rate limiting issues on CloudPages (which I've seen before). – Eliot Harper Sep 23 '18 at 23:28
  • 1
    thank you @Gortonington. The try and catch is underrated and can be used in so many different scenarios. – Brad Sapkota Sep 24 '18 at 1:55

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.