Table not linked to Recipients

[ogonzalesdiaz]

So, I've a case where we need the values from "channel_of_comunication_id" from schema "comunications_preference" when filtering "recipients" schema (the OOTB recipients). 

I think, I have to ways to accomplish this:

1.- Link "comunications_preference" schema with Recipients. 
2.- Make an Enrichment in the workflow to get that column ("channel_of_comunication_id") from schema: "comunications_preference".  The field "recipients" and "comunications_preference" share is: "codinternocomputacional".

While trying to use Enrichment, I don't see the table "comunications_preference" as option to select:

Question: 

1.- How to make the link with Recipients?
2.- Should I keed noDbIndex="true" for this case? 

Here is the schema's "comunications_preference" XML: 

<srcSchema _cs="comunications_preference (abc)" created="xxx"
createdBy-id="0" desc="xxx"
entitySchema="xtk:srcSchema" img="xtk:schema.png" label="comunications_preference"
lastModified="xxx" mappingType="sql" md5="xxxx"
modifiedBy-id="0" name="comunications_preference" namespace="abc"
xtkschema="xtk:srcSchema">

<createdBy _cs="xxx"/>
<modifiedBy _cs="xxx"/>

<element autopk="true" label="comunications_preference" name="comunications_preference">

<attribute label="codinternocomputacional" name="codinternocomputacional" type="string"/>

<attribute label="channel of comunication id" name="channel_of_comunication_id"
type="string"/>

<!--Cardinalidad Recipient vs. channel of comunication 1-N (A recipient can have multiple channels of comunication)-->

<element integrity="normal" label="Recipient" name="recipient" noDbIndex="true"
revIntegrity="normal" revLabel="Recipient PreferenciasCom Link" revLink="comunications_preference"
target="nms:recipient" type="link">
<join xpath-dst="@id" xpath-src="@RECIPIENTID"/>
</element>

<!--Indices -->
<dbindex name="codinternocomputacional">
<keyfield xpath="@codinternocomputacional"/>
</dbindex>

<dbindex name="channel_of_comunication_id">
<keyfield xpath="@channel_of_comunication_id"/>
</dbindex>

</element>
</srcSchema>

[AkshayAnand]

Hi @ogonzalesdiaz 

You can create the link by using the syntax given in the video or the Documentation based on your requirement. 

I am not sure but I can already see a linkage between the comunications_preference schema dn recipient schema in your code,

I guess you would be able to fetch the records going through the link.

Regards

Akshay

[Parvesh_Parmar · Accepted solution]

Hello @ogonzalesdiaz ,

1. First create a new field in the comunications_preference schema to store the recipient ID.

To do this, open the Schema Editor and navigate to the comunications_preference schema. Then, add the following code to the schema:

<attribute label="Recipient ID" name="RECIPIENTID" type="long"/>

2. Create a link between the comunications_preference and recipient tables.

This steps looks ok in your schema definition.

Just for info: noDbIndex= true , is used, if you do not need index on foreign key. There  RECIPIENTID is your foreign key.  If you have to  search based on this link  then do not use noDbIndex= true. 

3. Fill the recipient ID in the comunications_preference table.

When you are importing data from a master data source to comunications_preference  table , then you can map the data with recipient table and find the recipient ID and  field in the comunications_preference table.

4. Verify that the link has been set up correctly.

If all data is filled correctly, you can access recipient from comunications_preference table.

Note: If you set up a link from the comunications_preference table, the relationship from the recipient table to comunications_preference tables will be N-1. This means that one recipient can have many communication preferences, but one communication preference can only belong to one recipient. If you need one to one mapping then you need to define revCardinality ="single" on the link definition.

I hope this information is helpful. 

Best regards, 

Parvesh

[LakshmiPravallika]

Hi @ogonzalesdiaz ,

The Column which is used as Joining Key in the Enrichment (The field "recipients" and "comunications_preference" share is: "codinternocomputacional") differs from the joining keys being used in the Link (<join xpath-dst="@id" xpath-src="@RECIPIENTID"/>).

Could you Please check by adding the right Joining keys, Also , as we know that the Recipient ID is the Unique Joining key in Recipients Schema, please make sure @RECIPIENTID in the other schema is also unique for your requirement to work.

<element integrity="normal" label="Recipient" name="recipient" noDbIndex="true"
revIntegrity="normal" revLabel="Recipient PreferenciasCom Link" revLink="comunications_preference"
target="nms:recipient" type="link">
<join xpath-dst="@id" xpath-src="@RECIPIENTID"/>
</element>

Also reg the Index ,in the above mentioned Link if you are searching based on the column (@RECIPIENTID) in your Workflows while sending deliveries or in any of the activities, then you can remove  noDbIndex="true" in the Link.

Regards,

Pravallika.

[Sukrity_Wadhwa]

Hi @ogonzalesdiaz,

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. In case the given solutions were helpful, then kindly choose the one that helped you the most as the 'Correct Reply'.
Thanks!