Use ampscript to check data extension has an EmailAddress field

Question

I've got a custom process which allows users to match manually uploaded data to SF ContactID values. It relies on the EmailAddress field being called EmailAddress and having the correct field type to work. We've had some instances where users have created an EmailAddress field as Text Type, or they called it email_Address or some other name that won't parse.

I currently have some error management in place for names that are too long or have a space in their name - both of which cause the rest of the process to fail.

Is there a way - preferably using ampscript rather than SSJS, I can retrieve all fields from the specified data extension, and throw an error if there isn't an EmailAddress field with the correct name and type?

For example, this is my error management for the other issues:

%%[
 set @de = RequestParameter("de")
 set @len = Length(@de)
 set @sp = IndexOf(@de," ")
 IF @len < 27 AND @sp == 0 THEN
]%%
  Run SSJS
%%[ELSE]%%
Error Message
%%[ENDIF]%%

I'd like to check for an EmailAddress field at this stage too.

Answer 1

Your ability to use Ampscript for this is limited. I would use SSJS/WSProxy to do this. Here is some code which will list all your columns in a specific data extension:

<script runat="server">

    Platform.Load("core", "1");

    var api = new Script.Util.WSProxy();

    try {

        var customerKey = "S0M3-GU1D-K3Y-G03SR1G4T-H3R3";

        var cols = [
            "Name", 
            "MaxLength", 
            "IsRequired", 
            "FieldType", 
            "DefaultValue", 
            "Ordinal"
        ];

        var result = api.retrieve("DataExtensionField", cols, {
            Property: "DataExtension.CustomerKey",
            SimpleOperator: "equals",
            Value: customerKey
        });

        Write(Stringify(result));
        
    } catch(error) {
        Write(Stringify(error));
    }   

</script>

You can adjust it to validate if a given data extension has a field called EmailAddress having the right data type too:

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

var api = new Script.Util.WSProxy();
var typeCount = 0;
try {

    var customerKey = "27352299-BFC4-4B8A-B333-6BDBAE49BDD6";

    var cols = [
        "Name",
        "FieldType"
    ];

    var result = api.retrieve("DataExtensionField", cols, {
        Property: "DataExtension.CustomerKey",
        SimpleOperator: "equals",
        Value: customerKey
    });

    for (var i = 0; i < result.Results.length; i++) {
        if (result.Results[i].FieldType == 'EmailAddress' && result.Results[i].Name == 'EmailAddress') {
            var rightNameAndType = true;
            typeCount++;
            Write("rightNameAndType");
        } else if (result.Results[i].Name == 'EmailAddress') {
            var rightName = true;
            Write("rightName");
        } else if (result.Results[i].FieldType == 'EmailAddress') {
            var rightType = true;
            typeCount++;
            Write("rightType");
        }
    };
    if (!rightNameAndType) {
        Write("No correct field was found");
    }
    if (typeCount > 1) {
        Write("More than one email field found");
    }
} catch (error) {
    Write(Stringify(error));
}
</script>

This checks if there is a field with both the right type and name. Also, it checks if more than one field exists with the type EmailAddress (which will prevent you from using it for sends).

Answer 2

You can retrieve data extension metadata (which fields exists) via the API.

AMPScript is the worst way to interact with the API. So while there might be odd ways to do this, you'll be writing Javascript in a complicated AMPScript wrapper. This makes it even worse than struggling with SSJS.

<script type="javascript" runat="server">
Platform.Load("core",1);
var prox = new Script.Util.WSProxy();

var deCustomerKey = "Your DEs CustomerKey"
// retrieve the de
var cols = ["Client.ID","CustomerKey","DefaultValue","FieldType","IsPrimaryKey","IsRequired","MaxLength","ModifiedDate","Name","ObjectID","Ordinal","Scale"];
var filter = {
    Property: "DataExtension.CustomerKey",
    SimpleOperator: "equals",
    Value: deCustomerKey
};

var deFields = prox.retrieve("DataExtensionField", cols, filter);
var deFieldsResults = deFields.Results;

// assume there is no field of type email address
var deHasEmailAddressField = false

// go through all fields until you find a field with the type emailAddress
if (deFieldsResults && deFieldsResults.length > 0) {
    for (i=0;i<deFieldsResults.length;i++) {
        
        if (deFieldsResults[i].FieldType == "EmailAddress" ) {
            // if one field is found -> switch to true
            deHasEmailAddressField = true            
        } 
    }
}

// create an AMPScript var so printing is easier
Variable.SetValue("@deHasEmailAddressField",deHasEmailAddressField)

</script>
this will tell you true or false: %%=v(@deHasEmailAddressField)=%%