queryDef on linked schema + recipient foreign key

[david--garcia]

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

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 

[_Manoj_Kumar_]

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]

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?

[_Manoj_Kumar_]

@david--garcia That will work as well.

[david--garcia · Accepted solution]

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

[isahore]

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