1

I'm experiencing a strange error from a LookupRows function. I've thought I've seen every single AMPscript error, until today. The following AMPscript code:

set @sendLogRows = LookupRows('SendLog', 'SubID', 39739981, 'TriggeredSendID', '66d03afd-07ef-eb11-b856-b883035be801')

Returns the error:

The Data Extension Key field for a LookupRows function call is invalid. The key field has a MAX or unlimited length. Fields with MAX or unlimited length cannot be used as keys.

I get the same error using this DE if I use a Lookup function (with modified arguments).

Here's my DE schema:

DE Schema

Nothing unusual here, right?

I'm not sure what the 'the Data Extension Key field' actually is. For sake of complete visibility (in case the UI isn't displaying something correctly), I've retrieved the DE field properties using WSProxy. This returns the payload below. Again, all seems as I'd expect. If anyone has an idea of what to try next, I'd be really grateful!

{
   "Status":"OK",
   "RequestID":"355ef440-7912-49c8-8a96-d11631bc4c6c",
   "Results":[
      {
         "Name":"SubID",
         "FieldType":"Number",
         "IsPrimaryKey":false,
         "MaxLength":0,
         "Ordinal":4,
         "DefaultValue":""
      },
      {
         "Name":"TriggeredSendID",
         "FieldType":"Text",
         "IsPrimaryKey":false,
         "MaxLength":0,
         "Ordinal":5,
         "DefaultValue":""
      },
      {
         "Name":"view_email_url",
         "FieldType":"Text",
         "IsPrimaryKey":false,
         "MaxLength":900,
         "Ordinal":9,
         "DefaultValue":""
      },
      {
         "Name":"CampaignName",
         "FieldType":"Text",
         "IsPrimaryKey":false,
         "MaxLength":254,
         "Ordinal":8,
         "DefaultValue":""
      },
      {
         "Name":"ListID",
         "FieldType":"Number",
         "IsPrimaryKey":false,
         "MaxLength":0,
         "Ordinal":2,
         "DefaultValue":""
      },
      {
         "Name":"BatchID",
         "FieldType":"Number",
         "IsPrimaryKey":false,
         "MaxLength":0,
         "Ordinal":3,
         "DefaultValue":""
      },
      {
         "Name":"CampaignID",
         "FieldType":"Text",
         "IsPrimaryKey":false,
         "MaxLength":50,
         "Ordinal":7,
         "DefaultValue":""
      },
      {
         "Name":"ErrorCode",
         "FieldType":"Number",
         "IsPrimaryKey":false,
         "MaxLength":0,
         "Ordinal":6,
         "DefaultValue":""
      },
      {
         "Name":"JobID",
         "FieldType":"Number",
         "IsPrimaryKey":false,
         "MaxLength":0,
         "Ordinal":1,
         "DefaultValue":""
      }
   ],
   "HasMoreRows":false
}

1 Answer 1

3

The issue is within the TriggeredSendID field that has Text datatype and no set value for the length, which means it has unlimited length. Take into consideration this is an unofficial and not recommended configuration of the field as SF Support told us some time ago.

Moreover, you can do a Lookup to the TriggeredSendID field to retrieve the value from it but you cannot use this field as a filter (as what you are doing in the LookupRows), since it might contain unsupported value's length and thus throws the error.

4
  • Oh, how did I miss that??? The bizarre thing is that this is a SendLog DE (based on the SendLog template) and that field is a default field in the template! Commented Jul 29, 2021 at 10:12
  • I guess it is a very old SendLog DE that has a TriggeredSendID field without length (Trailhead module depicts that as well) because if you would create a new one now, it would have length = 36 characters in the field TriggeredSendID. Probably, that was a reason to add the length to the field to improve efficiency and prevent such errors.
    – Niko
    Commented Jul 29, 2021 at 12:28
  • To add more clarity to this, when you use a field in lookups the system tries to build an index on that field to return results quicker, and an index can't be built on this field length
    – EazyE
    Commented Jul 29, 2021 at 13:18
  • @Niko I've just created a new SendLog on that account and it still has an unlimited field length, which can't be changed. Very odd. I even tried updating using the API and that didn't work. I'll just create a duplicate send log and update it from a query. What a bore. Commented Jul 30, 2021 at 2:10

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .