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

Filtering on a Linked Schema

Avatar

Avatar
Validate 1
Level 1
joel51583885
Level 1

Likes

3 likes

Total Posts

6 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
Boost 3
Boost 1
View profile

Avatar
Validate 1
Level 1
joel51583885
Level 1

Likes

3 likes

Total Posts

6 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
Boost 3
Boost 1
View profile
joel51583885
Level 1

04-03-2019

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?

Accepted Solutions (1)

Accepted Solutions (1)

Avatar

Avatar
Establish
MVP
wodnicki
MVP

Likes

961 likes

Total Posts

1,090 posts

Correct Reply

509 solutions
Top badges earned
Establish
Affirm 500
Contributor
Shape 1
Give Back 100
View profile

Avatar
Establish
MVP
wodnicki
MVP

Likes

961 likes

Total Posts

1,090 posts

Correct Reply

509 solutions
Top badges earned
Establish
Affirm 500
Contributor
Shape 1
Give Back 100
View profile
wodnicki
MVP

05-03-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

Answers (2)

Answers (2)

Avatar

Avatar
Validate 1
Level 1
joel51583885
Level 1

Likes

3 likes

Total Posts

6 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
Boost 3
Boost 1
View profile

Avatar
Validate 1
Level 1
joel51583885
Level 1

Likes

3 likes

Total Posts

6 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
Boost 3
Boost 1
View profile
joel51583885
Level 1

05-03-2019

Hi Jon,

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

link.PNG

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.

Avatar

Avatar
Establish
MVP
wodnicki
MVP

Likes

961 likes

Total Posts

1,090 posts

Correct Reply

509 solutions
Top badges earned
Establish
Affirm 500
Contributor
Shape 1
Give Back 100
View profile

Avatar
Establish
MVP
wodnicki
MVP

Likes

961 likes

Total Posts

1,090 posts

Correct Reply

509 solutions
Top badges earned
Establish
Affirm 500
Contributor
Shape 1
Give Back 100
View profile
wodnicki
MVP

04-03-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.