Solved

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

  • 20 November 2018
  • 6 replies
  • 49 views

Userlevel 6
Badge +2
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?


icon

Best answer by jitin_mehndiratta 30 January 2019, 10:45

View original

6 replies

Userlevel 7
Badge +1
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.


Userlevel 6
Badge +2
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.


Userlevel 6
Badge +2
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.


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


Userlevel 6
Badge +2
Thank you Jitin, I'm going to give that a shot!


Userlevel 7
Badge +1
Hi Kelly,



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



Always welcome for any queries.


Reply