Compute SMS content length | Community
Skip to main content
A
AmanDua
February 22, 2019
Solved

Compute SMS content length

  • February 22, 2019
  • 11 replies
  • 5260 views

Hi,

I am looking to look for those SMS deliveries/delivery template through-out the AC instance where the content of the SMS exceeds 160 characters and I would want a list of all those campaigns exported.

I had a look at the delivery form and figured that while trying to preview any SMS delivery, it calculates the length and display the message if it exceeds more than 160 characters.

  <container colcount="2" colspan="2" type="visibleGroup" visibleIf="[/tmp/sms/preview/@length] &gt; 160">

                  <static/>

                  <static img="xtk:logwarn.png" label=" " nolabel="true">The content for this recipient exceeds the maximum SMS size of 160 characters.</static>

    </container>

How do I get access to [/tmp/sms/preview/@length] field via query activity so I can get the list of all SMS deliveries which has more than 160 chars??

Thanks.

    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
    Best answer by asariaaara

    Hey Anon,
    I tried this and its working perfect

    var qwe = xtk.queryDef.create(

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

              <select>

                  <node expr="@id"/>

                  <node expr="[content/sms/source]" alias="@content"/>

              </select>

              <where>

                  <condition expr="@id=157651363"/>

              </where>

          </queryDef>

    );

    var result= qwe.ExecuteQuery();

    for each(var res in result){

    var tempValue=res.@content.toXMLString(true).length;

    logInfo('str'+tempValue);

    11 replies

    Jonathon_wodnicki
    Community Advisor
    Jonathon_wodnickiCommunity Advisor
    Community Advisor
    February 23, 2019

    Hi,

    Use length([content/sms/source]) > 160.

    Thanks,

    -Jon

      A
      AmanDuaAuthor
      February 25, 2019

      Thanks Jon for your prompt response. Really appreciate it.

      I tried using this expression for nms:delivery schema. However, it gave me this error:

      25/02/2019 12:15:33 QUE-370015 The node of path '/contentDef/source' is not stored as an independent SQL field (document of type 'Deliveries (nms:delivery)'). Unable to use it as a filtering criteria in a query.

      Here is how I used this expression:

      Is there something I missed?

      Thanks.

      Jonathon_wodnicki
      Community Advisor
      Jonathon_wodnickiCommunity Advisor
      Community Advisor
      February 25, 2019

      Hi,

      Right it's an xml field. From here it gets a bit messy:

      1. Select that col into the temp table and filter on @id > 0 (gets whole table minus 0 row)
      2. Add a col to the output with just the value 0, there should be at least the content col and the 0 col in the temp table
      3. Use a js activity to read the temp table, updating the 0 col's value to 1 if (@content + '').length > 160
      4. Use a split activity to collect the 1's

      Thanks,

      -Jon

        A
        AmanDuaAuthor
        March 5, 2019

        Hi Jon,

        Thanks so much for your response.

        I did try your suggestion, but unfortunately it's still not working . Here is what I did:

        Added 2 columns. One with content of the SMS and one with value 0.

        Here is how my JS looks like where I'm reading @content & @value from the temp table and try to logInfo "new value" which should print 1 in the workflow journal logs.

        In workflow logs, it successfully prints value 0 which means it's reading successfully from the temp table.

        When I run the workflow again, I expect the value 1 in the logs to be printed for all the records, but it doesn't do anything and fails with this error:

        05/03/2019 17:38:03                Automatically resuming workflow (number of consecutive times: 2).

        05/03/2019 17:37:03                Automatically resuming workflow (number of consecutive times: 1).

        Could you please let me know if Anything I missed here?

        Thanks

        Jonathon_wodnicki
        Community Advisor
        Jonathon_wodnickiCommunity Advisor
        Community Advisor
        March 5, 2019

        You have to make explicit update calls to the temp table- easiest/most efficient way is by batching them with raw sql, e.g. sqlExec(‘update ‘ + var.targetSchema.replace(’:’, ‘’) + ‘ set ivalue=1 where id in (’ + ids.join(‘,’) + ‘)’). Also select the id col and not the blank col. Looks fine otherwise. Replace apostrophes if pasting not sure how to do normal ones on iPhone.

          A
          AmanDuaAuthor
          March 6, 2019

          Thanks very much Jon. Appreciate your help & prompt response.

          I did try your suggestion and selected the @id column now and got rid of the other columns from <select> , but unfortunately it didn't work this time too. Here is my JS:

          When I tried to log the length of the content logInfo('length is : ' + smsLen), it displayed 0 in there and not going in the "If" condition at all to update the 0's to 1's.

          Could you please help ?

          Thanks.

          Jonathon_wodnicki
          Community Advisor
          Jonathon_wodnickiCommunity Advisor
          Community Advisor
          March 6, 2019

          You have to write the surrounding js for that statement. The ids array there is collected from the result loop, i.e. if ((res.@content + '').length > 160) ids.push(res.@id); then batched out afterward with slice() so as not to exceed query length limit.

            A
            AmanDuaAuthor
            March 6, 2019

            Thanks Jon for being patient with me so far. Really appreciate your help.

            I've tried putting it into array and then slice it afterwards, It ran all fine this time. However, the iValue column is still not updated to 1.

            The trouble here is -  This statement " if ((res.@content + '').length > 160)"  is resulting length zero (0), hence the complete if condition is not getting executed as its checking for > 160.  Is it because of  res.@content is not getting stored in query result variable as its an XML field?

            Thanks.

            A
            asariaaara
            Level 3
            March 6, 2019

            Cant you just use where Condition {"WHERE iId="+res.@id.toString()}

              A
              AmanDuaAuthor
              March 6, 2019

              Hi,

              The problem is not with the update query at all. Its with this line((res.@content + '').length) which is not calculating length properly and is outputting zero (0) when I expect 160 or more. Hence, its not going in "IF" loop.

              Show more replies
              Terms & ConditionsAccessibility statement

              Loading footer...