Expand my Community achievements bar.

Announcing the launch of new sub-community for Campaign Web UI to cater specifically to the needs of Campaign Web UI users!
SOLVED

Calculated fields using XML memo data

Avatar

Community Advisor

Given that I have the following recipient mData/data structure

David__Garcia_0-1640320469310.png

 

 

 

 

<?xml version='1.0'?>
<recipient>
	<comment></comment>
	<changes changeDate="2021/12/24 21:58:45"/>
</recipient>

 

 

 

 

 

I tried using the following expression in a calculated field and in queryDef as well as [changes/@changeDate] in the where clause to no avail, I have a timestamp stored in the mData field and I need to calculate which records are over 72 hours old.

 

 

 

 

"Iif(Abs([changes/@changeDate] - GetDate()) / 36e5) >= 72, 'Expired','Pending'"

 

 

 

 

The following error is generated

 

 

 

 

24/12/2021 04:51:43 js6 QUE-370015 The node of path '/changes/@changeDate' is not stored as an independent SQL field (document of type 'Recipients (nms:recipient)'). Unable to use it as a filtering criteria in a query.

 

 

 

 

 

Is it definitely not possible to query the xml field using soap methods?

 

 

To get around the issue I had to resort to the following script which is a bit of an overkill, If i could just create a calculated field to pull the timestamp from an xml field that would be great, anyone know any easier alternatives?

 

 

 

 

var query = xtk.queryDef.create(  
  <queryDef schema="nms:recipient" operation="select">    
    <select>      
      <node expr="@id"/>            
      <node expr="[changes/@changeDate]" alias="@changeDate"/>                   
    </select>     
    <where>      
      <condition expr="[data] like '%changes%'"/> 
    </where>      
  </queryDef>)

var res = query.ExecuteQuery()

c = 0
const expired = []
for each (var i in res) {
    
   try {
   var expiry = Math.abs(new Date(Date.parse(i.@changeDate)) - new Date()) / 36e5;

   if (expiry >= 72) {   
    c++ 
     expired.push(i.@id) //store expired records in object
     
     /*remove tmp data, keep comments if any*/
       var rcpObj = nms.recipient.load(i.@id);
              
       if (rcpObj.comment.length > 0) {        
           xmlData = '<?xml version="1.0"?><recipient><comment>'+rcpObj.comment+'</comment></recipient>'       
        } else {var xmlData = ''}   
       
     sqlExec("UPDATE NmsRecipient SET mData = '"+xmlData+"' WHERE iRecipientId='"+parseInt(i.@id)+"'");                  
     /*              end                    */
   
   } else if(expiry < 72) {
     logInfo(i.@id+":"+expiry) //non expired records
   } else {
   logInfo(i.@id+":"+expiry) //non compliant records
   }
       }catch(e) {logInfo(e)}
}

logWarning("Expired: "+c+": "+expired)

 

 

 

Here is my schema extension for the [changes] element in recipient schema.

 

    <!--tmp recipient changes 18122021 David Garcia-->
    <element label="changes" name="changes" xml="true">
      <attribute label="tmp firstName" name="firstName" type="string" xml="true"/>
      <attribute label="tmp LastName" name="lastName" type="string" xml="true"/>
      <attribute label="tmp email" name="email" type="string" xml="true"/>
      <attribute label="tmp emailPreferredName" name="emailPreferredName" type="string"
                 xml="true"/>
      <attribute label="tmp JOB_TITLE" name="JOB_TITLE" type="string" xml="true"/>
      <attribute label="tmp company" name="company" type="string" xml="true"/>
      <attribute label="tmp blackListEmail" name="blackListEmail" type="string" xml="true"/>
      <attribute label="tmp lawfulBasis" name="lawfulBasis" type="string" xml="true"/>
      <attribute label="tmp changeDate" name="changeDate" type="datetime" xml="true"/>
    </element>
    <!--tmp recipient changes -->

 

 

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

Hi @david--garcia ,

 

1) Try using below syntax to fetch the value of 'changeDate':

Substring(data, Charindex(data, 'changeDate='), 19)

 

Once you save 'changeDate' value, then you can use another enrichment activity to use your code.

 

2) Or use complete code in one syntax:

Iif(Abs(((Substring(data, Charindex(data, 'changeDate='), 19)) - GetDate()) / 36e5) >= 72, 'Expired','Pending')

 

Thanks,

Jyoti

 

 

View solution in original post

3 Replies

Avatar

Correct answer by
Community Advisor

Hi @david--garcia ,

 

1) Try using below syntax to fetch the value of 'changeDate':

Substring(data, Charindex(data, 'changeDate='), 19)

 

Once you save 'changeDate' value, then you can use another enrichment activity to use your code.

 

2) Or use complete code in one syntax:

Iif(Abs(((Substring(data, Charindex(data, 'changeDate='), 19)) - GetDate()) / 36e5) >= 72, 'Expired','Pending')

 

Thanks,

Jyoti