3

I need a report that will list out all of our Accounts currently under contract and use one product, but do not have another. I feel like there might be an easy way to do this and that I am possibly over thinking it. Let me explain in more detail...

I need to show all Accounts that have a value of "Product Group A" for the Product Family field on the Asset Level, but do not have a value of "Product Group B" for Product Family with any other associated Assets.

So to get more detailed...

Company A has:
Contract 1234 with
Asset 1 - Product Family: Product Group A
Asset 2 - Product Family: Product Group A etc

Contract 2345 with
Asset 3 - Product Family: Product Group B
Asset 4 - Product Family: Product Group B etc

Company B has:
Contract 7654 with
Asset 5 - Product Family: Product Group A
Asset 6 - Product Family: Product Group A etc

Contract 3456 with
Asset 7 - Product Family: Product Group C
Asset 8 - Product Family: Product Group C etc

Company C
Contract 9876 with
Asset 11 - Product Family: Product Group D
Asset 12 - Product Family: Product Group D etc

Contract 2134 with
Asset 13 - Product Family: Product Group C
Asset 14 - Product Family: Product Group C etc

The report would list Company B and not Company A and C

Thanks

2 Answers 2

4

If I understand the structure correctly, you should be able to do this using cross filters and a formula field on the Asset to get the Product Family.
Create a report on Accounts and add two cross filters.
1. Accounts with Contracts where Assets Product Family = "Product Group A"
2. Accounts without Contracts where Assets Product Family = "Product Group B"

enter image description here

9
  • You know, I saw your post and got excited for a second. But when I tried it the data was not correct. I think this is because the filter on Assets only looks at Assets and there can be 2, 3 or more Assets on one Account. So if an Account has both Product Group A and B, the first filter would add it to the report.
    – Liz
    Aug 6, 2014 at 5:52
  • The second filter should remove it from the report? It's saying show me accounts with group a assets but only if they don't have group b assets. Can you add a screen shot?
    – Jenny B
    Aug 6, 2014 at 10:22
  • Of the filters? Sure. I ended up using Contracts instead of Assets, but same concept. When I remove the second filter it changes nothing. I have 455 records with or without using the second filter. I also spot checked about 30 or the records and 5 or so had a contract with Product Group A and a second contract with Product Group B ![Filters][1] [1]: i.sstatic.net/68PGW.png
    – Liz
    Aug 6, 2014 at 14:28
  • Can you explain the exact structure. Accounts have Contracts and does a Contract have a lookup to a product?
    – Jenny B
    Aug 6, 2014 at 14:35
  • Sure, Accounts have Contracts that are imported from an outside source. The Product Family field is text field, not a lookup from the product. @jjbennett530
    – Liz
    Aug 6, 2014 at 16:56
0

You could write a trigger to calculate the number of unique Asset Products associated with an account on insert and update.

Populate that value on the Account record, then filter out anything > 1

Although maybe I am not understanding your question because in your examples I believe NONE of the companies would be listed as they all have assets with different product families

You said Company B should be listed but it has assets with Product Family Group A and C

3
  • I figured a trigger was going to be my only option. Just wanted to make sure I wasn't overlooking something simple. Thanks!
    – Liz
    Aug 6, 2014 at 0:00
  • Right, Company B is the only one that has A and not B. Company A already has Group A and B and Company C doesn't have A. Basically we are trying to get a list of our companies that we can upsell/cross-sell to.
    – Liz
    Aug 6, 2014 at 0:03
  • @Liz It could also be written as a Visualforce page, but a trigger would be less work. Not to mention, you'd lose reporting capability, so you'd have to code your own export, if that were a need.
    – sfdcfox
    Aug 6, 2014 at 0:14

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .