Does not exist logic with 1:M

  • 2
  • Question
  • Updated 9 months ago
  • In Progress
That may be really simple but can't figure it out: is there a way to generate a power list using "does not exist" logic in 1:M relationships? For example find me all accounts which do not have any entitlement contact with role=Admin? The relationship is: Account (1):Entitlements(M):Entitlement Contacts(M). 
Photo of Raya

Raya

  • 124 Points 100 badge 2x thumb

Posted 9 months ago

  • 2
Photo of Dan Ahrens

Dan Ahrens, Gainster (employee)

  • 22,638 Points 20k badge 2x thumb
Hi Raya,

This sounds like an ideal use case for Advanced Outreaches and adding participants using a Bionic Query: https://support.gainsight.com/Product_Documentation/CoPilot_and_Automated_Email/Admin_Configuration/...

Bionic rules and queries allow you to pull data from multiple data sets and to aggregate, pivot and transform the data along with doing complex merges (inner, outer, left and right joins are supported). Here is some more info on getting started with Bionic rules: https://support.gainsight.com/Product_Documentation/Rules_Engine/Admin_Configuration/Getting_Started...
Photo of Jitin Mehndiratta

Jitin Mehndiratta, Product Manager

  • 2,862 Points 2k badge 2x thumb
Hi Raya,

Create a Bionic Rule with the following tasks:
1) Fetch all accounts
2) Fetch all "accounts, Entitlement contact" where role = "admin"
3) Merge 1 and 2, retain all records from 1 on Account ID(1) = Account ID(2)
4) Fetch all records from 3 where role = null/blank

This will help you achieve the "does not exist"  logic in Bionic Rule.

Regards,
Jitin
Photo of Elizabeth Adams

Elizabeth Adams, Champion

  • 4,286 Points 4k badge 2x thumb
Hi Jitin - I have a follow-up on this. Where do the results of the Bionic Rule go? How do you then create the power list?
Photo of Dan Ahrens

Dan Ahrens, Gainster (employee)

  • 22,638 Points 20k badge 2x thumb
Hi Elizabeth,

With Advanced Outreaches you can add participants via traditional powerlists, CSV, Event Framework actions and Bionic Queries. You can even mix and match sources too! So you don't need to exclusively use a power list anymore. 

More info here: https://support.gainsight.com/Product_Documentation/CoPilot_and_Automated_Email/Admin_Configuration/...