Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
BedrockMission!

Learn more

View all

Sign in to view all badges

SOLVED

queryDef on linked schema + recipient foreign key

David__Garcia
Level 8
Level 8

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
David__Garcia
Correct answer by
Level 8
Level 8

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
Manoj_Kumar_
Community Advisor
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.

David__Garcia
Level 8
Level 8

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?

David__Garcia
Correct answer by
Level 8
Level 8

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

isahore
Level 3
Level 3

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