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

  • you are creating standard reports or in VF apex code. – Tushar Sharma Jun 2 '16 at 9:09
  • What is the Formula "data type" you chose? What is the error you are getting? – sfdcFanBoy Jun 2 '16 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 '16 at 9:35
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
)
  • 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 '16 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)

  • 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 '16 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.

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.