How to save the SUBJECT of an EMAIL DELIVERY with an Update Data activity

[salvdangelo]

Hi,
I would like to retrieve the subject of an email using with target dimensione the Recipient delivery log table.

Since the subject is an XML (calculated) field in the workflow I can retrieve it with a Query activity but I cannot save it in an Update date activy.

I should save the subject in this field Subject delivery of my destination table:

<attribute label="Subject Delivery" name="subjectDelivery" type="string"/>

ERROR ON UPDATE DATA ACTIVITY:
The node of path '/subjectDelivery' is not stored as an independent SQL field

I also tried to modify the scheme adding this XML attribute to the definition:

<attribute label="Subject Delivery" name="subjectDelivery" type="memo" xml="true"/>

ERROR ON UPDATE DATA ACTIVITY:

Element 'data' unknown

The subject is in the table nsm:delivery and is defined as below:

    <element label="Email header parameters" name="mailParameters" xml="true">

      ...

      <element desc="Message subject" label="Subject" localizable="true" name="subject"

               type="CDATA"/>

      ...

     </element>

May anyone tell me how can I solve this problem?

[Jean-Serge_Biro · Accepted solution]

Buen giorno salvdangelo,

Yes, it is well-known flaw of Adobe Campaing, the XML storage fields are not usable as filters criteria or output column, in  all the Target tab of workflow activities (such Query, Update Data, Enrichment, Edit Schema, etc).

It is not possible to use it in a queryDef (where or node clause) in Javascript activity as well.

So, as far as I know, the only solution is to do it with Javascript activity, with a queryDef delivery.@id then load the delivery object with a static method only, then use the XML data field.

To sum up, queryDef dynamic query, and inside the loop, use a static method to get the data, and writes it into your custom object (either

save if with your entity.save dynamic method, or writeSession static method depending on your use case).

Please see an example below:

// XML field not allowed in queryDef as where or node criteria
// so this is not allowed:
//        <node expr="mailParameters/@subject" alias="mailSubject" />
//var filterInternalName = "[@internalName] = '" + "DM3717" + "'";
var filterInternalName = "1 = 1";
//var filterDate = "@contactDate >= '" + PUT YOUR NEED CRITERIA
filterDate = "1 = 1";
// lineCount="5" if not enough restricting where clause, enough lines to test   
var query = xtk.queryDef.create(
    <queryDef schema="nms:delivery" operation="select" lineCount="5" >
      <select>
        <node expr="@id"/>
      </select>
      <where>
        <condition expr={filterInternalName} />
        <condition expr={filterDate} />
      </where>
    </queryDef>)
try
{
  var res = query.ExecuteQuery();
  logInfo("[WKF348] get the subject values of all deliveries selected");
  logInfo("[WKF348] Count of values = " + res.child("delivery").length());
}
catch(e)
{
  logWarning("[WKF348] Error in getting the subject values of all deliveries selected");
  logError("[WKF348] Error number: " + e); // or use logWarning if the treatment must go on despite this error
}
var delivery;
for each(var elt in res)
{ 
  delivery = NLWS.nmsDelivery.load(elt.@id)
  logInfo("[WKF348] delivery internal name = " + delivery.internalName + " | subject = " + delivery.mailParameters.subject); 
}

Please note that the delivery subject can contain Javascript code personalisation, so you will get the subject raw values before personalisation.

Regards

Jean-Serge

[Jean-Serge_Biro]

Hi,

It seems the copy / paste in html raw element is not well taken into account, so hereinafter as normal text:

// XML field not allowed in queryDef as where or node criteria

// so this is not allowed:

//        <node expr="mailParameters/@subject" alias="mailSubject" />

//var filterInternalName = "[@internalName] = '" + "DM3717" + "'";

var filterInternalName = "1 = 1";

//var filterDate = "@contactDate >= '" + PUT YOUR NEED CRITERIA

filterDate = "1 = 1";

// lineCount="5" if not enough restricting where clause, enough lines to test  

var query = xtk.queryDef.create(

    <queryDef schema="nms:delivery" operation="select" lineCount="5" >

      <select>

        <node expr="@id"/>

      </select>

      <where>

        <condition expr={filterInternalName} />

        <condition expr={filterDate} />

      </where>

    </queryDef>)

try

{

  var res = query.ExecuteQuery();

  logInfo("[WKF348] get the subject values of all deliveries selected");

  logInfo("[WKF348] Count of values = " + res.child("delivery").length());

}

catch(e)

{

  logWarning("[WKF348] Error in getting the subject values of all deliveries selected");

  logError("[WKF348] Error number: " + e); // or use logWarning if the treatment must go on despite this error

}

var delivery;

for each(var elt in res)

{

  delivery = NLWS.nmsDelivery.load(elt.@id)

  logInfo("[WKF348] delivery internal name = " + delivery.internalName + " | subject = " + delivery.mailParameters.subject);

}

Regards
J-Serge

[salvdangelo]

Hi Jean-Serge,

I re-open this thread in order to make you a similar request respect to the previous one.

I tried your code for the DELIVERY SUBJECT, that is an XML field, and it worked pretty good!

Now I'm trying to retrieve the filtering condition of the query created in the current workflow.

The code I'm using is the following:

var query = <queryDef schema="xtk:workflow" operation="select">

        <select>

          <node expr="@id"/>

        </select>

        <where>

          <condition expr={ '@id = ' + instance.id }/>

        </where>

        </queryDef>

  var res = xtk.queryDef.create(query).ExecuteQuery();

  for each (wf in res){

    var workflow = NLWS.xtkWorkflow.load(wf.@id);

    logInfo("OPERATION: "+workflow.operation.id);

    vars.query = workflow.activities.query.humanCond; 

    logInfo("QUERY :"+vars.query);

  }

Output:
OPERATION: 0

QUERY: undefined

May you tell me what I'm doing wrong?

Thanks!
Salvatore

Jean-Serge Biron​

[Marcel_Szimonisz]

Hello salvdangelo,

check the xml of workflow by Edit -> Edit XML source.

Each activity is represented  array of objects as you can have more than one same type activity in workflow

So you need to loop over activity e.g.

var workflow = xtk.workflow.load("158380184");
for each (var item in workflow.activities.start)
  logInfo(item.name)
logInfo(workflow.activities.start.length)

Marcel

[salvdangelo]

Hi Marcel,

I was trying to follow your suggestions but now I'm even more confused than before.

I need to extract the XML filed humandCond I should find in activities/query/humanCond (each of this element is an XML).

If I insert this script

    for each (var activity in workflow.activities ){

      logInfo(activity.name);

    }

output of 'activity.name' is always undefined

Furthermore, I cannot find any reference to 'query' element inside the XML source.

Could you help me, please?
Thanks,
Salvatore

[Marcel_Szimonisz]

Hello and sorry,

I edited my answer i thought it worked before that you can traverse all activities as array and if there is more activities of one type that  you could traverse those activities as another array aswell. But I  could not get it work

In your case if you have only one query activity in the worflow

var humanCond = workflow.activities.query[0].humanCond;

Marcel