Skip to main content
kelly
kelly
Helper ⭐️⭐️⭐️
November 20, 2018
Solved

How do you pull accounts when no contacts meet the criteria, not the contacts that meet the criteria

  • November 20, 2018
  • 6 replies
  • 94 views
I'm trying to pull together a list of accounts that do not have specific contact fields populated. What I created though is just returning the contacts that meet the criteria.

For example, I want a list of Accounts that do not have any Project Manager contacts assigned. Account + Project Manager = null returns the contacts that are not Project Managers, but it doesn't mean there aren't contacts that are a Project Manager. I'm not sure how to incorporate the additional/different qualifier that returns the contacts that aren't a Project Manager only for the Accounts for which there is no Project Manager.

Thoughts?

Best answer by jitin_mehndiratta
Hi Kelly,

This is achievable using Pivot task in Bionic Rules along with Case formula in Transformation task.

Example:

You can apply case formula in Transformation task to your data so that "true" is replaced by 1 and "False" by 0. The Output dataset will be as follows:

Account A

Contact 1 - Project Manager = 1



Contact 2 - Project Manager = 0



Contact 3 - Project Manager = 0



Account B:



Contact 1 - Project Manager = 1



Contact 2 - Project Manager = 0



Contact 3 - Project Manager = 1



Account C:



Contact 1 - Project Manager = 0



Contact 2 - Project Manager = 0



Contact 3 - Project Manager = 0

Then use the Pivot task in Rules to get the output as follows(use Sum as an aggregation in Pivot) :

Accounts Project Managers

Account A 1

Account B 2

Account C 0



Apply

Filter condition : Project managers = 0



You will get Account C in the output as desired.

Let me know if there are any questions.

Regards,

Jitin

6 replies

sai_ram
sai_ram
Expert ⭐️⭐️
December 11, 2018
Hi Kelly,
This appears to be achievable via Merge task in Bionic rules. Can you elaborate some more about the problem statement. It will be helpful if you can give an example in a tabular form with some sample data and the expected output so that I am able to help you better and the best way of achieving this.

kelly
kellyAuthor
Helper ⭐️⭐️⭐️
December 11, 2018
Hi Sai:

Account A:

Contact 1 - Project Manager = True

Contact 2 - Project Manager = False

Contact 3 - Project Manager = False

Account B:

Contact 1 - Project Manager = True

Contact 2 - Project Manager = False

Contact 3 - Project Manager = True

Account C:

Contact 1 - Project Manager = False

Contact 2 - Project Manager = False

Contact 3 - Project Manager = False

Given the above scenario, I want a query that returns just Account C. Instead, the results that returned were Account A: Contact 1 and 2, Account B: Contact 2, and Account C: Contact 1, 2, and 3.

I was able to replicate a rule another user created, however any steps that could be listed should anyone else search for something similar I'm sure it would be helpful.

kelly
kellyAuthor
Helper ⭐️⭐️⭐️
January 26, 2019
Sai -this has come up again, any guidance here? To address previously I used the Rules Engine but this is a need for a query in Programs.

jitin_mehndiratta
jitin_mehndirattaAnswer
Helper ⭐️⭐️⭐️
January 30, 2019
Hi Kelly,

This is achievable using Pivot task in Bionic Rules along with Case formula in Transformation task.

Example:

You can apply case formula in Transformation task to your data so that "true" is replaced by 1 and "False" by 0. The Output dataset will be as follows:

Account A

Contact 1 - Project Manager = 1



Contact 2 - Project Manager = 0



Contact 3 - Project Manager = 0



Account B:



Contact 1 - Project Manager = 1



Contact 2 - Project Manager = 0



Contact 3 - Project Manager = 1



Account C:



Contact 1 - Project Manager = 0



Contact 2 - Project Manager = 0



Contact 3 - Project Manager = 0

Then use the Pivot task in Rules to get the output as follows(use Sum as an aggregation in Pivot) :

Accounts Project Managers

Account A 1

Account B 2

Account C 0



Apply

Filter condition : Project managers = 0



You will get Account C in the output as desired.

Let me know if there are any questions.

Regards,

Jitin

kelly
kellyAuthor
Helper ⭐️⭐️⭐️
January 30, 2019
Thank you Jitin, I'm going to give that a shot!

    sai_ram
    sai_ram
    Expert ⭐️⭐️
    February 12, 2019
    Hi Kelly,

    Did the suggestion given by @Jitin work for you? Do you need any help here?

    Always welcome for any queries.

    Terms & ConditionsAccessibility statement