4

I have a custom object: License with a Number field of: Version (9,10,11...)

The License object is a related list ( with Account as Lookup field).

I need to create a report, that shows only accounts with licenses of Different Versions.

E.g. The following is a list of 2 licenses from Account: 'A', On my report, I want to get Account 'A' as a result. enter image description here

In SQL,. it would look something like this * but ignoring the named values. I am looking for a more general approach, to get 'ANY' different values.

I have no clue how to solve this.

1

I want to start off by saying there is a strong possibility of this not being the most ideal way to handle this situation. Reporting is not my strongest abilities, so there is usually standard functionality that I miss. With that said...


You could create a Trigger on the License object. I would make it an after insert and after update trigger. In the trigger I would get the Ids of all of the Accounts that the objects are associated with and get all of the accounts and the licenses currently on that account that aren't being updated or inserted in the trigger. I would then loop over the Accounts and if they have multiple licenses that match your criteria, I would update a hidden readonly boolean(checkbox) field to indicate it meets your criteria. I would then update those accounts.

In your report, you can then just filter by that checkbox being checked.


As I said earlier, this is not really ideal and hopefully someone can give you a more "standard" approach, but this would work as a worst case scenario.

1

You could have roll up fields containing MAX(Version) and COUNT(Version) and SUM(Version). Then to find Accounts where there is at least one license version difference, filter for those where

NOT(MAX(Version) == (SUM(Version) / COUNT(Version)))

This boils down to finding Accounts where the Max verison and the Version average are not the same. This will happen if at least one Version is different.

  • cool idea, might be even a little more direct if you compared Max(Version) and Min(Version) – Ralph Callaway Apr 24 '13 at 16:54
  • 1
    ha, quite right - I originally had the min compared to max compared to average and thought "this could be simpler" and promptly choose the more complicated way! – Ray Apr 25 '13 at 17:53
0

What if you had two "rollup" summary fields as below:

  1. Count_of_Licenses__c : Could of the child license records
  2. Sum_of_Version__c : Sum of Version__c on all child license records.

Now have another formula field (say "Sum_By_Count_License__c") that is #2 / #1

If the Account has license records belonging to the same version, then Sum_By_Count_License__c should be whole number.

I'm sure there's an edge condition where this math might fail but this might solve for the majority of the cases.

  • this can be a whole number even if the license versions are different. e.g. v4 and v6 gives a sum of 10 and a Sum_by_Count_License of 5 – Ray Apr 24 '13 at 13:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.