5

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

3
  • you are creating standard reports or in VF apex code. Jun 2, 2016 at 9:09
  • What is the Formula "data type" you chose? What is the error you are getting?
    – sfdcFanBoy
    Jun 2, 2016 at 9:20
  • The datatype is Opportunity. All fields are on the opportunity. I am using standard reports. Basically what i want to do, if picklist 1 doesnt match picklist 2, I want to somehow highlight that in the report. Either in colour, or a formula field that shows a 1 or a 0.
    – Lily
    Jun 2, 2016 at 9:35

3 Answers 3

1

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
)
1
  • She just need this part to return boolean: OR ( AND( RecordType.Name = "a", OR( ISPICKVAL(analysis__Field1__c,"1"), ISPICKVAL(analysis__Field1__c,"2") ) ), AND( ... ), AND( ... ) )
    – o-lexi
    Jun 3, 2016 at 16:03
0

Your formula field should be this

IF(AND(ISPICKVAL(Field_1__c,a),OR(ISPICKVAL(Field_2__c,1),ISPICKVAL(Field_2__c,2))),1,0)

1
  • Actually one of the picklists is not a picklist, but a record type. Is it possible to reference a record type in a formula field?
    – Lily
    Jun 2, 2016 at 10:02
0

You can probably use a dummy custom field to put the value of the record type (I do it using Apex Trigger). Then, use that field in your formula.

You must log in to answer this question.

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