Newsletter subscription

[david--garcia]

Hi all,

Need some ideas to perform the following action;

Currently, I am subcribing a newly created recipient to a service (subscriptions); whilst registering, this user has also submitted a couple of answers which I am storing in a custom schema. I have created a template which is used for the confirmation of the subscription and since this automatic, now I need to include some of his submitted answers within the subscription template.

What would be the best way to include this recipient's submitted answers as part of the subscription confirmation email?

1. I was thinking of creating a queryDef in the subscription template and pulling this user's latest submission based on max date.

is there an alternative solution?

This is my user subscription script and storing of submitted answers to custom schema.

var recipientId = ctx.recipient.@id;

var jsonData = ctx.vars.jsonData;
var stringColumns = ctx.vars.stringColumns;
var stringValues = ctx.vars.stringValues;
var prospect = ctx.vars.prospect;
var client = ctx.vars.client;
  

    var sqlSyntax = "INSERT INTO CusGoals (iGoalsId, biRecipientId, "+stringColumns+", tsLastModified, tsCreated) VALUES (nextval('auto_cusgoals_seq'), "+recipientId+", "+stringValues+", current_timestamp, current_timestamp)";
    
    
    
sqlExec(sqlSyntax);



if (prospect == 1) {
     /* Prospect Double Opt */
    logInfo("New ESG prospect - Double OptIn")        
    var rcpObj = NLWS.nmsRecipient.load(ctx.recipient.@id);
    rcpObj.origin = "ESG";
    var rcpXml = {recipient: {_key: "id", id: rcpObj.id}}    
      nms.subscription.Subscribe('SVC_DO_CAZ',  rcpXml, false)        
    rcpObj.save();  
} else if (client == 1) {
       /* Client Subscription - SVC52 - ESG Sustainability */
    logInfo("Client ESG Subscription")        
    var rcpObj = NLWS.nmsRecipient.load(ctx.recipient.@id);
    var rcpXml = {recipient: {_key: "id", id: rcpObj.id}}    
      nms.subscription.Subscribe('SVC52',  rcpXml, false)        
    rcpObj.save(); 
  
  }

So the following command subscribes the user to my subscription newsletter

nms.subscription.Subscribe('SVC52', rcpXml, false)

And this is my subscription configuration with my test template, now whats the easiest way of pulling the submitted goals from my custom schema into the subscription template on the go? similar to passing data through targetData.

[CedricRey · Accepted solution]

Hi David,

Depending of your installation, but the way you want do it should not work in a mid-sourcing configuration.

Not sure to have a best solution, but here what you can try (and not tested so may not work...).

Can you add 2 attributes in your cus:goals schema : recipient-id and service-id ? You put the recipientId and the serviceId into when you registrer the recipient to the service.

Also, join the "nms:subscription" with the 2 keys :

<element ...target="nms:subscription"><join xpath-dst="@recipient-id" xpath-src="@recipient-id"/><join xpath-dst="@service-id" xpath-src="@service-id"/></element>

This way, you should be able to find the "cus:goals" into the subscription delivery (the template) with the native data loading query.

Also, I suggest you to never use a SQL query to insert data from a webApp, or at least, escape the string "stringColumns" and "stringValues" (if I were you, I'll use the xtkSession.Write method instead).

Don't know if it help ?

Good luck.

Cedric

[Sukrity_Wadhwa]

Hi @david--garcia,

Was the given solution helpful to resolve your query or do you still need more help here? Do let us know.

Thanks!