Filtering on a Linked Schema

[#1]

Hi,

The data I am working with requires a separate table that I have linked to the Recipients table, cus:contracts. This linked schema is required because a single Recipient can have multiple contracts tied to them with their own unique attributes. Because of this, it has been linked with a cardinality of 1-N.

What is the best way to create a workflow that enables me to filter for specific contracts as well as recipient data? What targeting and filtering dimensions should I use?

Do I need to use custom target mapping for this?

[#2 · Accepted solution]

Hi,

Campaign's default cardinality is n:1 for joins, which is one of the reasons I prefer defining joins on dependent tables. Since you're defining out of recipient and not specifying any cardinality, Campaign is setting recipients as the n and contracts as the 1. Set the element to unbound="true" and revCardinality="single" to reverse this, per: Elements and attributes,​ or relocate the join to the contract schema.

Thanks,

-Jon

[#3]

Hi Jon,

I currently have the link of the 2 schemas defined in the Recipients table

When I try to create a query with the Targeting Dimension and Filtering Dimension of Recipients, I can select the linked "Contracts" table. However, the operator field does not have the "exists" option. I am also unable to select "Contracts" as the filtering dimension.

[#4]

Hi,

Define the link in the schema, on either side (not both) of the relationship. I usually define joins out of the dependent tables. Save, log out and back in. From here you'll be able to traverse the table link in the query modal, generating an 'exists' condition in sql. You can also change the filtering dimension on the first screen of the modal if it's clearer.

Thanks,

-Jon

NB For more advanced use, i.e. FDA, you would query the contracts side directly, then change dimension to the recipient side. In this way you'd be specifying which db you want to execute each query, controlling the amount of data transferred between multiple db's.