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

queryDef on linked schema + recipient foreign key

Avatar

Community Advisor

Having issues with retrieving a field value from a linked schema [lnkEventRegistrations/@eventLabelName]

 

 

var query = xtk.queryDef.create(<queryDef schema="nms:recipient" operation="select"> 
                                        <select>
                                            <node expr="@id"/>
                                            <node expr="@cmOneID"/>
                                            <node expr="@firstName"/>
                                            <node expr="@lastName"/>
                                            <node expr="[lnkEventRegistrations/@eventLabelName]" analyze="false" alias="@eventLabelName" sort="true"/>
                                            <node expr="@emailPreferredName"/>
                                            <node expr="@printPreferredName"/>                                            
                                            <node expr="@company"/>
                                            <node expr="@email"/>                                                                                                                             
                                        </select>
                                        <where>
                                          <condition expr="@id = 13245436"/>
                                        </where>
                                       </queryDef> );


var res = query.ExecuteQuery();
logInfo(res)
for each (var r in res.recipient) {
  logInfo("r.@eventLabelName :"+r.@eventLabelName)
  logInfo("r.@firstName :"+r.@firstName)
}

 

 

 

04/09/2021 01:20:59 js r.@firstName :David-Event
04/09/2021 01:20:59 js r.@eventLabelName : <----------- empty

 

David__Garcia_0-1630717769423.png

David__Garcia_1-1630717877955.png

The link seems to be working from the recipient schema as it returns data from sch:eventRegistrations, however, on the querydef script the value is empty.

 

This is whats returned on the querydef execution, the @eventLabelName is not present.

 

04/09/2021 01:20:59 js <recipient-collection> <recipient cmOneID="-812156815" company="" email="xxx@xxx.com" emailPreferredName="Dave" firstName="David-Event" id="13245436" lastName="Garcia-Event" printPreferredName="David G."/> </recipient-collection>

 

Here is the link definition from recipient to event registration.

 

    <!-- Link to Relationship Manager Table -->
    <element integrity="define" label="event Registrations" name="lnkEventRegistrations"
             revLink="recipient" target="sch:eventRegistrations" type="link" unbound="true">
      <join xpath-dst="@recipient-id" xpath-src="@id"/>
    </element>
    <!-- End Link to Relationship Manager Table -->

 

There is also a link on the event registration schema to recipient.

 

 <element label="Recipients" name="recipient" target="nms:recipient" type="link"/>

@Florian_Courgey 

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

so I run the following query

 

var query = xtk.queryDef.create(<queryDef schema="nms:recipient" operation="select"> 
                                        <select>
                                            <node expr="@id"/>
                                            <node expr="@cmOneID"/>
                                            <node expr="@firstName"/>
                                            <node expr="@lastName"/>
                                            <node expr="@emailPreferredName"/>
                                            <node expr="@printPreferredName"/>                                            
                                            <node expr="@company"/>
                                            <node expr="@email"/>                                            
                                            <node expr="[location/@address1]" alias="@address1"/>
                                            <node expr="[location/@address2]" alias="@address2"/>
                                            <node expr="[location/@address3]" alias="@address3"/>
                                            <node expr="[location/@address4]" alias="@address4"/>                                            
                                            <node expr="[location/@city]" alias="@city"/>
                                            <node expr="[location/@zipCode]" alias="@zipCode"/>                                            
                                            <node expr="[lnkRelationshipManager/@firstName]+ ' ' + [lnkRelationshipManager/@lastName]" alias="@relationshipManager"/>
                                            <node expr="[eventRegistrations/@eventLabelName]" alias="@eventLabelName"/>
                                            <node expr="[lnkRelationshipManager/@schRelationshipManagerID]" alias="@rmid"/>
                                            <node expr="Iif([@clawfulBasis]=0,'Consent Removed',Iif([@clawfulBasis]=1,'None',Iif([@clawfulBasis]=2,'Legitimate Interest','Consent Given')))" alias="@lawfulBasis"/>
                                            <node expr="Iif(@blackList=1,'yes','no' )" alias="@blackList"/>
                                            <node expr="Iif(@eventOptinEmail=1,'yes','no' )" alias="@blackListEmail"/>
                                            <node expr="Iif(@eventOptinPost=1,'yes','no' )" alias="@blackListPostalMail"/>      
                                            <node expr="@jurisdiction"/>
                                            <orderBy>
                                               <node expr="@firstName"/>
                                             </orderBy>
                                        </select>
                                        <where>
                                          <condition expr="@id = 13244476" /> 
                                        </where>
                                       </queryDef> );

var res = query.ExecuteQuery();

on logging `logInfo(res)` the following I get.

 

 

<recipient-collection>
	<recipient address1="Adobe" address2="Old Street" address3="99" address4="Islington" blackList="no" blackListEmail="no" blackListPostalMail="no" city="London" cmOneID="-812156815" company="" email="davidxxx@xxx.com" emailPreferredName="David-Dgarcia" firstName="David-Testf" id="13244476" jurisdiction="SCPB - LON" lastName="Garcia-testl" lawfulBasis="None" printPreferredName="David G." relationshipManager="Adam Cavalier" rmid="2021" zipCode="SxxxST">
	<eventRegistrations eventLabelName="Event dummy test"/>
	</recipient>
</recipient-collection>

so is returning the eventLabelName and thought I could log it such as the following since I am using the alias, but didnt work.

 

for each (var r in res) {
logInfo(r.@eventLabelName)
}

so I end up using the following xpath and is working.

r.eventRegistrations.@eventLabelName

 

 

 

 

View solution in original post

5 Replies

Avatar

Community Advisor

Hello @david--garcia Looks like the link created in event registration schema is also many to many. Create it as a single link and then do the query on event registration schema. 

 

In your event registration schema the link should be like this.

 <element label="Recipients" name="recipient" target="nms:recipient" type="link" revCardinality="single"/>

Now run the query def on event registration schema and pull data the from recipients.


     Manoj
     Find me on LinkedIn

Avatar

Community Advisor

The relationship between recipient and event registration is indeed 1:M, a recipient will be referenced in many event registrations.

 

If I need to run the queryDef from the recipient schema, will it not work?

Avatar

Correct answer by
Community Advisor

so I run the following query

 

var query = xtk.queryDef.create(<queryDef schema="nms:recipient" operation="select"> 
                                        <select>
                                            <node expr="@id"/>
                                            <node expr="@cmOneID"/>
                                            <node expr="@firstName"/>
                                            <node expr="@lastName"/>
                                            <node expr="@emailPreferredName"/>
                                            <node expr="@printPreferredName"/>                                            
                                            <node expr="@company"/>
                                            <node expr="@email"/>                                            
                                            <node expr="[location/@address1]" alias="@address1"/>
                                            <node expr="[location/@address2]" alias="@address2"/>
                                            <node expr="[location/@address3]" alias="@address3"/>
                                            <node expr="[location/@address4]" alias="@address4"/>                                            
                                            <node expr="[location/@city]" alias="@city"/>
                                            <node expr="[location/@zipCode]" alias="@zipCode"/>                                            
                                            <node expr="[lnkRelationshipManager/@firstName]+ ' ' + [lnkRelationshipManager/@lastName]" alias="@relationshipManager"/>
                                            <node expr="[eventRegistrations/@eventLabelName]" alias="@eventLabelName"/>
                                            <node expr="[lnkRelationshipManager/@schRelationshipManagerID]" alias="@rmid"/>
                                            <node expr="Iif([@clawfulBasis]=0,'Consent Removed',Iif([@clawfulBasis]=1,'None',Iif([@clawfulBasis]=2,'Legitimate Interest','Consent Given')))" alias="@lawfulBasis"/>
                                            <node expr="Iif(@blackList=1,'yes','no' )" alias="@blackList"/>
                                            <node expr="Iif(@eventOptinEmail=1,'yes','no' )" alias="@blackListEmail"/>
                                            <node expr="Iif(@eventOptinPost=1,'yes','no' )" alias="@blackListPostalMail"/>      
                                            <node expr="@jurisdiction"/>
                                            <orderBy>
                                               <node expr="@firstName"/>
                                             </orderBy>
                                        </select>
                                        <where>
                                          <condition expr="@id = 13244476" /> 
                                        </where>
                                       </queryDef> );

var res = query.ExecuteQuery();

on logging `logInfo(res)` the following I get.

 

 

<recipient-collection>
	<recipient address1="Adobe" address2="Old Street" address3="99" address4="Islington" blackList="no" blackListEmail="no" blackListPostalMail="no" city="London" cmOneID="-812156815" company="" email="davidxxx@xxx.com" emailPreferredName="David-Dgarcia" firstName="David-Testf" id="13244476" jurisdiction="SCPB - LON" lastName="Garcia-testl" lawfulBasis="None" printPreferredName="David G." relationshipManager="Adam Cavalier" rmid="2021" zipCode="SxxxST">
	<eventRegistrations eventLabelName="Event dummy test"/>
	</recipient>
</recipient-collection>

so is returning the eventLabelName and thought I could log it such as the following since I am using the alias, but didnt work.

 

for each (var r in res) {
logInfo(r.@eventLabelName)
}

so I end up using the following xpath and is working.

r.eventRegistrations.@eventLabelName

 

 

 

 

Avatar

Community Advisor

Hi @david--garcia ,

 

That is happening because the link you have with the eventRegistrations schema is a 1 to many relationship. It would return the eventLabelName had the link been a 1 to 1 relationship.

 

Cheers,

Ishan