0

I have an interesting report I need to write, and could really use your help.

Here's my problem. I have Contact records in my instance with a few different scores applied to them. For the sake of simplicity, we'll just say it's three unique fields with possible values of 0-100

I want to separate my data into two different groups.

  1. All records that score a 90 or above in ALL of the three score fields. (This one is simple to create)
  2. All records that score at least 75 in each of the score fields; however, it must exclude all of the records that are pulled in the first report. Meaning the records also cannot score a 90 or above in ALL three score fields.

Here are some examples to help illustrate:

  1. A record has a score of 80, 80 and 95. It should not qualify for report #1, but should qualify for report #2.
  2. A record has a score of 78, 82 and 80. It should not qualify for report #1, but should qualify for report #2.
  3. A record has a score of 92, 95 and 98. It should not qualify for report #2, but should qualify for report #1.
  4. A record has a score of 70, 65 and 74. It should not qualify for either report.

What is the best way to accomplish this? Again, I need two separate reports, with absolutely no overlap in the contact/lead records that are pulled.

What I ended up doing to solve this was create the first report, and I used Data Loader to load a specific value into a field that is not being used. Then in report two, I just filtered those records out. That being said, it would be nice to have something more dynamic - meaning I don't have to update the top records each week/month/etc.

2

Create a formula field:

UPDATE: More efficient formula by @sfdcfox from the comment below:

IF(
 AND(Score1__c>=90, Score2__c>=90, Score3__c>=90),
 2,
 IF(
  AND(Score1__c>=75, Score2__c>=75, Score3__c>=75),
  1,
  0)
)

Original formula:

MIN(
 IF( Score1__c < 74, 0,
  IF( Score1__c < 90, 1, 2)
 ),
 IF( Score2__c < 74, 0,
  IF( Score2__c < 90, 1, 2)
 ), 
 IF( Score3__c < 74, 0,
  IF( Score3__c < 90, 1, 2)
 )
)

Your report filters would be MyFormula equals 2 for Report1, and MyFormula equals 1 for Report2

| improve this answer | |
  • 2
    IF(AND(Score1__c>=90, Score2__c>=90, Score3__c>=90),2, IF(AND(Score1__c>=75, Score2__c>=75, Score3__c>=75),1, 0)) is slightly less heavy but should be the same thing. – sfdcfox Feb 28 '18 at 4:51
  • It’s a lot lighter, I like it! – o-lexi Feb 28 '18 at 4:59
0

If you determine the calculation, then create a formula field to build up the logic and then filter based on formula field value.

Formula logic will be like this (be sure to check braces)

IF(Number1__c=80 && Number2__c=80 && Number3__c=95,'Report1',
    IF(Number1__c=72 && Number2__c=82 && Number3__c=80,'Report2',
        IF(Number1__c=92 && Number2__c=95 && Number3__c=95,'Report1',
            IF(Number1__c=70 && Number2__c=65 && Number3__c=74,'NA',
            )
        )
    )
)

And then in the first report, filter with formula value = 'Report1' and in the second report filter with formula value = 'Report2'

| improve this answer | |

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.