2

Does anyone know if there's a way to check with ampscript whether a field exists within a data extension or not? For example, if Column001 doesn't exist with the data extension use Column002 instead. The issue is that since Column001 doesn't exist in the DE the email throws an error and fails before running my logic below. Any ideas how I can work around this by chance? Thanks in advance everyone.

IF Not Empty(Column001) THEN
  SET @VAR = Column001
ELSE
  SET @VAR = Column002
ENDIF

Thanks,

3

Yes, with the AttributeValue() function. If the column doesn't exist then the function will return an empty string.

%%[
var @var, @col1, @col2
set @col1 = AttributeValue("Column001")
set @col2 = AttributeValue("Column002")

IF Not Empty(@col1) THEN
  SET @VAR = @col1
ELSE
  SET @VAR = @col2
ENDIF
]%%
  • Hi Adam, finally had a chance to test this logic out and it worked great. Thanks for your help! – Nolan May 23 '16 at 15:18
3

None of these answers address the question in the context it was asked. All of the answers assume we are talking about the "Sending" Data Extension, or, in other words, Subscriber Attributes, which just so happened to be the poster's situation and the suggested answer works for them. However, I had the same exact question and it wouldn't be addressed by either of the above answers. I'm talking about reading fields in data extensions that are NOT the default Sending Data Extension.

So, here's the information that may help the rest of us:

Let's say you are looking up multiple fields from a row in a custom DE

%%[
set @lookupValue = "whatever"
set @rows = LookupRows("YourDataExtensionName","LookupColumn", @lookupValue)
set @rowCount = rowcount(@rows)
if @rowCount > 0 then
    set @row = row(@rows,1) /* just get row #1 */
    set @DEColumn1 = field(@row,"DEColumn1")
    set @DEColumn2 = field(@row,"DEColumn2")
    ]%% DEColumn1 is %%=v(@DEColumn1)=%%, DEColumn2 is %%=v(@DEColumn2)=%% %%[
else
    ]%% No rows found %%[
endif
]%%

If, for instance, "DEColumn2" does not exist in your DE the script will throw an error and halt execution. So how would you avoid this situation and deal with tables that may or may not have this particular field?

Field function to the rescue. As per the documentation: "Adding 0 as the third parameter indicates that the call will return a NULL value if the specified data extension field does not exist. Any other value (or a missing value) indicates the call will return an error if the specified data extension field does not exist."

So now we can do this

%%[
set @lookupValue = "whatever"
set @rows = LookupRows("YourDataExtensionName","LookupColumn", @lookupValue)
set @rowCount = rowcount(@rows)
if @rowCount > 0 then
    set @row = row(@rows,1)
    set @DEColumn1 = field(@row,"DEColumn1")
    set @DEColumn2 = field(@row,"DEColumn2", 0) /* note the 0 */
    if @DEColumn2 is NULL then set @DEColumn2 = "not defined or is nullable"
    elseif @DEColumn2 == "" then set @DEColumn2 = "defined, but empty"
    endif
    ]%% DEColumn1 is %%=v(@DEColumn1)=%%, DEColumn2 is %%=v(@DEColumn2)=%% %%[
else
    ]%% No rows found %%[
endif
]%%

So the variable @DEColumn2 will have a valid value only if the field exists and is not empty. And the script will not throw an error. This does not however answer the question whether the field exists if it's value is NULL, or is it just a nullable field.

However, if all you need is to do something conditionally based on the value of the field IF IT EXISTS AND IS NOT EMPTY then the if not empty(@DEColumn2) then will perfectly suit your needs, as empty checks for NULL as well

0

One case Adam's answer doesn't answer is when you have the column but don't have a value you in it... I haven't found a way to solve for this... Field() seems like it might since you can indicate if you want a null passed if it does not exist, but it needs row to be there -- which I don't think one is inside an email.

AttributeValue only seems to pass an empty string whether the field is there and whether it is only empty. Which is important to distinguish (in my case anyway)

Any idea how to do that?

  • I can't yet comment on anything but my own posts, otherwise I would have added this as a comment. – Adrian Cotter Jun 1 '16 at 0:04
0

if the value isn't empty after AttributeValue then inside of the if, put another if to see if the value of the column is null or not. Assuming in your data extension you have a column named column1 you could do something like:

%%[

var @var, @col1, @col2

set @col1 = AttributeValue("Column001")

set @col2 = AttributeValue("Column002")

IF Not Empty(@col1) THEN

  if not empty([Column001]) then 

     SET @VAR = [Column001]

  else

    SET @VAR = "*something else*"

  endif

ELSE

  SET @VAR = @col2

ENDIF

]%%

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.