Level 9

Solved

Personalization block How to query custom table from personalization block

3 replies
1251 views
Translate All

Hello guys,

How can I query custom table from personalization block?

Thanks

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

Manoj_KumarAccepted solution

Community Advisor

Hello @luca_lattarini

use can query the data from customer table via the adobe campaign data api.

Reference link: https://experienceleague.adobe.com/docs/campaign-classic/using/configuring-campaign-classic/api/data-oriented-apis.html?lang=en

Sample Code:

<%

var query=xtk.queryDef.create(<queryDef schema="nms:recipient" operation="select">
  <select>
    <node expr="@email"/>
    <!-- builds a string with the concatenation of the last name and first name separated by a dash -->      
    <node expr="@lastName+'-'+@firstName"/>
    <!-- get year of birth date -->
    <node expr="Year(@birthDate)"/>
  </select> 

  <where>  
     <condition expr="[@folder-id] = 1234 and @domain like 'Adobe%'"/>
  </where>

  <!-- order by birth date -->
  <orderBy>
    <node expr="@birthDate" sortDesc="true"/> <!-- by default sortDesc="false" -->
  </orderBy>
</queryDef>);

var records = query.ExecuteQuery();

%>

Manoj | https://themartech.pro

Manoj_Kumar

Accepted solution

Community Advisor

Hello @luca_lattarini

use can query the data from customer table via the adobe campaign data api.

Reference link: https://experienceleague.adobe.com/docs/campaign-classic/using/configuring-campaign-classic/api/data-oriented-apis.html?lang=en

Sample Code:

<%

var query=xtk.queryDef.create(<queryDef schema="nms:recipient" operation="select">
  <select>
    <node expr="@email"/>
    <!-- builds a string with the concatenation of the last name and first name separated by a dash -->      
    <node expr="@lastName+'-'+@firstName"/>
    <!-- get year of birth date -->
    <node expr="Year(@birthDate)"/>
  </select> 

  <where>  
     <condition expr="[@folder-id] = 1234 and @domain like 'Adobe%'"/>
  </where>

  <!-- order by birth date -->
  <orderBy>
    <node expr="@birthDate" sortDesc="true"/> <!-- by default sortDesc="false" -->
  </orderBy>
</queryDef>);

var records = query.ExecuteQuery();

%>

Translate

Manoj | https://themartech.pro

Marcel_Szimonisz

Community Advisor

Hello @luca_lattarini ,

apart from the @_manoj_kumar_ asnswer -querydef- you you can also use:

sqlSelect from jsapi:

var res = sqlSelect("publicUrl,@sstringValue:string",
        "select sstringValue from XtkOption where sname='XtkFileRes_PublicUrl'")
logInfo(res.publicUrl.@sstringValue.toString(

https://docs.adobe.com/content/help/en/campaign-classic/technicalresources/api/f-sqlSelect.html?hl=sqlselect

Marcel

Translate

Sukrity_Wadhwa

Level 4

Hey @luca_lattarini,

Were you able to resolve this query with the help of the given solutions or do you still need more help here? Do let us know.

Thanks!

Translate

Sukrity Wadhwa

Sign up

Login with SSO

Login to the community

Login with SSO