0

I need to send an email and update a data extension based on JSON content. I've written code combining GTL and AMPscript but I keep getting errors. And also since this is my first time using GTL and second time using AMPscript I am not sure that the code is correct.

I need to use GTL and if is the case to use also AMPscript.

This is my error:

The subscriber preview failed to generate. Review the details, correct all issues, and try again. Text Version There is an error in your email. Please contact your customer service representative. Error 1: Error Loading Email Content
An error occurred when attempting to parse HtmlEmailBody content for HTML content. MemberID: 10985802 JobID: 0 ListID: 3344 Content Begins With: %%[ var @Json set @Json = ' [{"Customer": [{"CustomerID": "1657"

This is my code:

%%[
var @Json set @Json = ' [{"Customer": [{"CustomerID": "1657","CompanyName": "AMO Food Market","Industry": "Retail","ContactName": "Anne Mollin","ContactTitle": "Marketing Manager","Email": "anne.mollin@AMOFoodMarket.com","FullAddress": {"Address": "2732 Daver Str.","City": "Bandie","Region": "OR","PostalCode": "94215"}},{"CustomerID": "1986","CompanyName": "Folimi Tires","Industry": "Cars","ContactName": "Adrian Moods","ContactTitle": "Sales Representative","Email": "adrian.moods@folimitires.com","FullAddress": {"Address": "City Mall 100 Main St.","City": "Dondie","Region": "OR","PostalCode": "93210"}}]}}] '
]%%

{{.datasource JSONVar type=variable source=@Json maxRows = 20}}
  {{.data}}
            {"target" : "@Json"}
  {{/data}}
            {{.datasource Customer type=nested maxRows = 20}}
              {{.data}}
                        { "target" : "JsonVar.Customer" }
              {{/data}}
                        {{Customer.CompanyName}}<br>
                        {{Customer.CustomerID}}<br>
                        {{Customer.Industry}}<br>
                        {{Customer.ContactName}}<br>
                        {{Customer.ContactTitle}}<br>
                        {{Customer.Email}}<br>

                 {{.datasource Address type=nested maxRows = 20}}
                   {{.data}}
                        { "target" : "JsonVar.FullAddress" }
                   {{/data}}
                        {{FullAddress.Address}}<br>
                        {{FullAddress.City}}<br>
                        {{FullAddress.Region}}<br>
                        {{FullAddress.PostalCode}}<br>

            {{/datasource}}
{{/datasource}}

%%[
  var @CustomerID, @CompanyName, @Industry, @ContactName, @ContactTitle, @Email, @Address, @City
  var @Region, @PostalCode, @FirstName, @LastName, @index 
  set @ContactName = {{JSONCustomer.ContactName}}


  if indexOf(@ContactName, " ") > 0 THEN

        set @FirstName = substring(@ContactName,1,subtract(indexOf(@ContactName," "),1))
        set @LastName = substring(@ContactName, add(indexOf(@ContactName," "),1),length(@ContactName))
        UpsertDE("AMPscript Functions 3 - v3",1,"CustomerID", @CustomerID, "CompanyName", @CompanyName, "Industry", @Industry, "FirstName", @FirstName, "LastName", @LastName, "ContactTitle", @ContactTitle, "Email", @Email, "Address", @Address, "City", @City, "Region", @Region, "PostalCode", @PostalCode)

  endif
]%%

GTL - Hello 2 %%=v(@FirstName)=%%.

Welcome to our community.

Have a great day!

This email was sent by: %%Member_Busname%%, %%Member_Addr%% %%Member_City%%, %%Member_State%%, %%Member_PostalCode%%, %%Member_Country%%
%%profile_center_url%%
{{> _footer}}

This is the updated version of my code

%%[
var @Json 
set @Json = ' {
   "Customers": {
      "CustomerCustomerID1657":
      [{
            "CompanyName": "AMO Food Market",
            "Industry": "Retail",
            "ContactName": "Anne Mollin",
            "ContactTitle": "Marketing Manager",
            "Email": "calin.bostan@osf-global.com",
            "FullAddress": {
               "Address": "2732 Daver Str.",
               "City": "Bandie",
               "Region": "OR",
               "PostalCode": "94215"
               }
         }],
      "CustomerCustomerID1657":
      [{
            "CompanyName": "Folimi Tires",
            "Industry": "Cars",
            "ContactName": "Adrian Moods",
            "ContactTitle": "Sales Representative",
            "Email": "adrian.moods@folimitires.com",
            "FullAddress": {
               "Address": "City Mall 100 Main St.",
               "City": "Dondie",
               "Region": "OR",
               "PostalCode": "93210"
            }
         }]
   }
} '
]%%

{{.datasource JSONVar type=variable maxRows = 20}}
  {{.data}}
            {"target" : "@Json"}
  {{/data}}


            {{.datasource JsonCustomerID type=nested maxRows = 20}}
              {{.data}}
                        { "target" : "JsonVar.CustomerID" }
              {{/data}}
                    <br>JsonCustomer.CompanyName: {{CustomerID.CompanyName}}
                    <br>JsonCustomer.CustomerID: {{[CustomerID.-CustomerID]}}
                    <br>JsonCustomer.Industry: {{CustomerID.Industry}}
                    <br>JsonCustomer.ContactName: {{CustomerID.ContactName}}
                    <br>JsonCustomer.ContactTitle: {{CustomerID.ContactTitle}}
                    <br>JsonCustomer.Email: {{CustomerID.Email}}

                 {{.datasource JsonFullAddress type=nested maxRows = 20}}
                   {{.data}}
                        { "target" : "JsonVar.FullAddress" }
                   {{/data}}
                    <br>JsonFullAddress.Address: {{FullAddress.Address}}
                    <br>JsonFullAddress.City: {{FullAddress.City}}
                    <br>JsonFullAddress.Region: {{FullAddress.Region}}
                    <br>JsonFullAddress.PostalCode: {{FullAddress.PostalCode}}

%%[
  var @CustomerID, @CompanyName, @Industry, @ContactName, @ContactTitle, @Email, @Address, @City, @Region, @PostalCode, @FirstName, @LastName

  SET @CompanyName = TreatAsContent('{{[CustomerID.CompanyName]}}')
  SET @Industry = TreatAsContent('{{[CustomerID.Industry]}}')
  SET @ContactTitle = TreatAsContent('{{[CustomerID.ContactTitle]}}')
  SET @Email = TreatAsContent('{{[CustomerID.Email]}}')
  SET @Address = TreatAsContent('{{[FullAddress.Address]}}')
  SET @City = TreatAsContent('{{[FullAddress.City]}}')
  SET @Region = TreatAsContent('{{[FullAddress.Region]}}')
  SET @PostalCode = TreatAsContent('{{[FullAddress.PostalCode]}}')


  set @index = IndexOf(@ContactName,' ')
        set @FirstName = Substring(@ContactName,1,@index)
        set @LastName = Substring(@ContactName,@index,Length(@ContactName)) 

]%%

                  {{/datasource}}
            {{/datasource}}
{{/datasource}}





GTL - Hello 2 %%=v(@ContactName)=%%.



Welcome to our community.

Have a great day!

This email was sent by: %%Member_Busname%%, %%Member_Addr%% %%Member_City%%, %%Member_State%%, %%Member_PostalCode%%, %%Member_Country%%
%%profile_center_url%%
{{> _footer}}   
  • 1
    In most cases, I recommend not using GTL. It's simpler to do in SSJS: gist.github.com/wvpv/aa9580f976c85c425761385d088d024f – Adam Spriggs Mar 25 at 11:37
  • Why do you need to do this in an email? – Adam Spriggs Mar 25 at 11:38
  • is an exercise requirement ... to do it in an email and also to use GTL – Calin Bostan Mar 25 at 13:06
  • @AdamSpriggs I've updated my code, no more errors but it doesn't update my DE. – Calin Bostan Mar 25 at 16:15
  • Are you trying to update the DE with data from each object in the JSON array? – Eliot Harper Mar 25 at 19:59
1

I'm not sure what you are trying to achieve here. Note that GTL will loop through all JSON objects in an array, just like an AMPscript process loop does. Your objects in the array don't look quite right. However, if you modify your code to read:

%%[
var @Json 
set @Json = '{
"Customers":[
  {
     "CustomerID":1657,
     "CompanyName":"AMO Food Market",
     "Industry":"Retail",
     "ContactName":"Anne Mollin",
     "ContactTitle":"Marketing Manager",
     "Email":"calin.bostan@osf-global.com",
     "FullAddress":{
        "Address":"2732 Daver Str.",
        "City":"Bandie",
        "Region":"OR",
        "PostalCode":"94215"
     }
  },
  {
     "CustomerID":1658,
     "CompanyName":"Folimi Tires",
     "Industry":"Cars",
     "ContactName":"Adrian Moods",
     "ContactTitle":"Sales Representative",
     "Email":"adrian.moods@folimitires.com",
     "FullAddress":{
        "Address":"City Mall 100 Main St.",
        "City":"Dondie",
        "Region":"OR",
        "PostalCode":"93210"
     }
   }
  ]
}'

]%%

{{.datasource JSONVar type=variable maxRows = 20}}
  {{.data}}
            {"target" : "@Json"}
  {{/data}}


            {{.datasource Customers type=nested maxRows = 20}}
              {{.data}}
                        { "target" : "JsonVar.Customers" }
              {{/data}}
                    <br>JsonCustomer.CompanyName: {{CompanyName}}
                    <br>JsonCustomer.CustomerID: {{[CustomerID.-CustomerID]}}
                    <br>JsonCustomer.Industry: {{Industry}}
                    <br>JsonCustomer.ContactName: {{ContactName}}
                    <br>JsonCustomer.ContactTitle: {{ContactTitle}}
                    <br>JsonCustomer.Email: {{Email}}

                 {{.datasource JsonFullAddress type=nested maxRows = 20}}
                   {{.data}}
                        { "target" : "Customers.FullAddress" }
                   {{/data}}
                    <br>JsonFullAddress.Address: {{Address}}
                    <br>JsonFullAddress.City: {{City}}
                    <br>JsonFullAddress.Region: {{Region}}
                    <br>JsonFullAddress.PostalCode: {{PostalCode}}



%%[
  var @ContactName, @index, @FirstName, @LastName

  set @contactName = TreatAsContent('{{ContactName}}')

  set @index = IndexOf(@ContactName,' ')
        set @FirstName = Substring(@ContactName,1,@index)
        set @LastName = Substring(@ContactName,@index,Length(@ContactName)) 

]%%

                    <br>Hello %%=v(@FirstName)=%% %%=v(@LastName)=%%.
                    <hr />

                  {{/datasource}}
            {{/datasource}}
{{/datasource}}

...Then this will loop through all objects in your array and display them as:

JsonCustomer.CompanyName: AMO Food Market 
JsonCustomer.CustomerID: 
JsonCustomer.Industry: Retail 
JsonCustomer.ContactName: Anne Mollin 
JsonCustomer.ContactTitle: Marketing Manager 
JsonCustomer.Email: calin.bostan@osf-global.com 
JsonFullAddress.Address: 2732 Daver Str. 
JsonFullAddress.City: Bandie 
JsonFullAddress.Region: OR 
JsonFullAddress.PostalCode: 94215 
Hello Anne Mollin.

----------------------------------------------------

JsonCustomer.CompanyName: Folimi Tires 
JsonCustomer.CustomerID: 
JsonCustomer.Industry: Cars 
JsonCustomer.ContactName: Adrian Moods 
JsonCustomer.ContactTitle: Sales Representative 
JsonCustomer.Email: adrian.moods@folimitires.com 
JsonFullAddress.Address: City Mall 100 Main St. 
JsonFullAddress.City: Dondie 
JsonFullAddress.Region: OR 
JsonFullAddress.PostalCode: 93210 
Hello Adrian Moods.
  • Hello @Eliot and Adam, Hope you are doing well ! I'm using the same exemple but I'm facing in issue when the "CustomerID":01657 have a leading zéro. The zéro isn't displayed in the email. I have tried a lot of work arrounds but still having the issue. Do you have any idea please ? Thanks a lot ! – M'henni Koroghli Jun 11 at 15:08

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.