Summary Report – Highlight matching values

I want to create a report which shows me whether two fields are matching

My matching pairs are:

Picklist Field 1: a, b, c, d, e
Record Type: 1, 2, 3, 4, 5

Matching rules:
a matches 1, 2
b, c matches 3
d, e matches 4, 5

My table looks like this:

Oppty Name , Field 1 , RecordType , Match?
Oppty 1, b , 4, FALSE
Oppty 2, c, 3, TRUE
Oppty 3, d, 3, FALSE

So, basically, in my report I want to create a formula field (Match?), where I highlight whether the values in the fields are a match against the record type, according to the matching rules I defined.
If I can do it using conditional highlighting that would be just as fine.

I created a formula field containing the first matching rule so far:

IF(
AND(
   RecordType.Name = "d",   
    OR(
      ISPICKVAL(Field1__c,"4"),
      ISPICKVAL(Field1__c,"5")
    )
  ),
1,0)

Unfortunately that doesn’t seem to work.
The error message I get when I try to save this is:

Error: Invalid custom summary formula definition: Field RecordType.Name does not exist. Check spelling.

Any ideas?
Tia, Lily

Answer

I wasn’t able to reproduce the error, but I was able to create a formula field that I believe does what you want:

IF(
    OR (
        AND(
            RecordType.Name = "a",   
            OR(
              ISPICKVAL(analysis__Field1__c,"1"),
              ISPICKVAL(analysis__Field1__c,"2")
            )
        ),
        AND(
            OR(
              RecordType.Name = "b", 
              RecordType.Name = "c"
            ),
            ISPICKVAL(analysis__Field1__c,"3")
        ),
        AND(
            OR(
              RecordType.Name = "d", 
              RecordType.Name = "e"
            ),
            OR(
              ISPICKVAL(analysis__Field1__c,"4"),
              ISPICKVAL(analysis__Field1__c,"5")
            )
        )
    )
    ,
    1
    ,
    0
)

Attribution
Source : Link , Question Author : Lily , Answer Author : martin

Leave a Comment