Filtering on a Linked Schema | Community
Skip to main content
joel51583885
joel51583885
Level 2
March 4, 2019
Solved

Filtering on a Linked Schema

  • March 4, 2019
  • 3 replies
  • 2361 views

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?

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by Jonathon_wodnicki

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 replies

Jonathon_wodnicki
Community Advisor
Jonathon_wodnickiCommunity Advisor
Community Advisor
March 5, 2019

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.

joel51583885
joel51583885Author
Level 2
March 5, 2019

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.

Jonathon_wodnicki
Community Advisor
Jonathon_wodnickiCommunity AdvisorAccepted solution
Community Advisor
March 6, 2019

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

Terms & ConditionsAccessibility statement

Loading footer...