0

I am working on writing a formula inside process builder "Formula Evaluates to true" and it seems my logic is not working.

Suppose I have a following logic:

1 OR 2 OR 3 OR(4 AND 5)

Below is my formula which is I am writing which is not working:

OR(
   OR(
     1,
     2,
     3),
   AND(
     ISNEW(),
    (this is 4)
     5)
)

Ideally combination of 4 AND 5 should comes with OR along 1,2,3. So I deally if an user puts value for fields 1 OR 2 OR 3 OR (4 and 5) process builder should run but right now process builder is only running when either 1,2,3 is filled and 5 is satisfying.

My real requirements:

Either Is_Medicare__pc OR Is_TriCare__pc OR Is_Medicaid__pc OR Person_s_Age__pc >= 65 is filled on account then process builder should update one field.

But right now process builder is running when either of Is_Medicare__pc OR Is_TriCare__pc OR Is_Medicaid__pc is filled AND Person_s_Age__pc >= 65.

Below is my formula which I build:

OR(
OR
(
[Account].Is_Medicare__pc,
[Account].Is_TriCare__pc,
[Account].Is_Medicaid__pc
),
AND
(
ISNEW(),
NOT(ISBLANK([Account].Person_s_Age__pc )),
[Account].Person_s_Age__pc >= 65
)
)

Note: Person_s_Age__pc is a formula field on contacts which is not supported by ISCHANGED() so i am using ISNEW() and ISBLANK()

EDIT : I am editing question based upon my recent work which I have done after giving a try to what @o-lexi has suggested. 1. Created one Date Formula field "65th BirthDate"(API NAME- X65th_BirthDate__c) Formula for this field is as below:

IF( 

AND( MONTH( Date_of_Birth__pc ) = 2, DAY( Date_of_Birth__pc ) = 29, NOT( OR( MOD( YEAR( Date_of_Birth__pc ) + 65, 400 ) = 0, AND( MOD( YEAR( Date_of_Birth__pc ) + 65, 4 ) = 0, MOD( YEAR( Date_of_Birth__pc )+ 65, 100 ) != 0 ) ) ) ), DATE( YEAR( Date_of_Birth__pc ) + 65, 3, 1), DATE( YEAR( Date_of_Birth__pc ) + 65, MONTH( Date_of_Birth__pc ), DAY( Date_of_Birth__pc ) ) ) 2. Created one Formula field as "Over 65" 3. Created one time-dependent PB which sets "Over 65" to TRUE after 0 hrs from 65th BirthDate enter image description here

enter image description here

enter image description here

  1. Modify my existing PB logic as OR( 1, 2, 3, Over_65__c )

Still not able to get what I was trying to achieve. If I put Date of birth on account as 15th May 1953 then upon saving 65th Birthday field is populating as 15th May 2018 but checkbox "Over 65"is not getting checked.Since checkbox is not getting checked my logic for updation of field value for "Insurance" to "Education Only" is also not set which I wrote in my another PB.Kindly help me on this issue.I am struggling alot on same from past 2 weeks and realaly running out of all ideas and knowledge. Any help will be greatly appreciated. Many thanks

  • 1
    My eyes kind of glaze over trying to grasp all the conditions - if I were you, I'd try and section out each part of the formula into smaller formulas. Easier to check each logic step, easier to write the more complex formulas, easier to debug, etc. Only downside is you have a good handful of fields. I've tried this before with very complex formulas and its helped me a lot. – battery.cord May 1 '18 at 14:47
1

Based on your comments, it sounds like your issue

But right now process builder is running when either of Is_Medicare__pc OR Is_TriCare__pc OR Is_Medicaid__pc is filled AND Person_s_Age__pc >= 65.

may stem from a misunderstanding of how Process Builder criteria that include formula fields work.

You suggested that Person_s_Age__pc is a formula field based on, presumably, the Birth Date field. Formula fields are calculated each time they're accessed - there's no database event when the calculated value changes. As a result, what your formula:

OR(
   OR(
      [Account].Is_Medicare__pc,
      [Account].Is_TriCare__pc,
      [Account].Is_Medicaid__pc
   ),
   AND(
      ISNEW(),
      NOT(ISBLANK([Account].Person_s_Age__pc)),
      [Account].Person_s_Age__pc >= 65
   )
)

ultimately does is fire when any of the fields Is_Medicare__pc, Is_TriCare__pc, or Is_Medicaid__pc is changed to true, or when a new Person Account is created whose Person_s_Age__pc field at the time of creation is equal to or greater than 65.

If you have a Person Account where any of the fields Is_Medicare__pc, Is_TriCare__pc, or Is_Medicaid__pc is true and that person's birthday passes, making their age hit 65, nothing will happen because that's not a database event.

If you're changing records in the user interface to test this process, the behavior might look like the logic is behaving differently than you expect.

If you want to take action when a person's age ticks over to 65 without a change in the Birth Date field, you would need to either use time-dependent actions in Process Builder or Workflow Rules, or write a Scheduled Apex class to monitor these records.

I personally would probably go the Scheduled Apex route here because I tend to use code-based solutions, but @o-lexi has a clever declarative solution to allow you to do this with an extra formula field via scheduled action in Process Builder.

  • Many thanks @David Reed-Yes you correct my problem is when person's age becomes 65 then a field say 'A' is not setting to a expected value becuase person age is formula field nd updation of this field is not DB event nd my PB not able to recognize the same. Can you please help me hw to achieve this usng time dependent actions in PB as you suggested in your comments. time dependent actions I can see only Hours or Days nd I want PB to run when either Medicare,Tricare, Medicaid OR person's age becomes 65 if date of birth is provided. Many thanks nd I am sure you will definitely help me on same – Harjeet Singh May 2 '18 at 5:30
  • 1
    I agree with David here - you need to create another date formula field (lets call it 65th Birthdate), and use it in a time-dependent PB or WR to set a flag field (e.g. Over 65) after 0 Hours from 65th Birthdate. Please see the formula in my edited answer - it appeared too long for the comment. – o-lexi May 2 '18 at 17:13
1

Are you sure that Person_s_Age__pc returns blank? Also, from my experience in certain scenarios when Process Builder was kicking in, ISNEW() wasn't returning true for newly created record, and this could be when Workflow Rules ran already and updated the record, or PB gets evaluated multiple times.

If you want the formula to be triggered only by a change in age, use Birth Date field:

OR(
   OR(
     1,
     2,
     3),
   AND(
     OR(
        ISNEW(),
        ISCHANGED(Birth Date)),
     5)
)

EDIT: I'm not sure what I was thinking by suggesting to use Birth Day change (!sic) as a trigger. So, as @DavidReed suggested in his answer, and I corroborated in my comment to his answer, you need to create the following:

  1. date formula field (lets call it 65th BirthDate)
  2. flag field (e.g. Over 65)
  3. time-dependent PB or WR to set a Over 65 to true after 0 Hours from 65th BirthDate's date
  4. Modify you PB formula to look like:

    OR( 1, 2, 3, Over_65__c )

The 65th BirthDate formula should be as found in Sample Date Formulas in Adding Days, Months, and Years to a Date section:

IF(
  AND(
    MONTH( Person_s_Birth_Date__pc ) = 2,
    DAY( Person_s_Birth_Date__pc ) = 29,
    NOT( 
      OR( 
        MOD( YEAR( Person_s_Birth_Date__pc ) + 65, 400 ) = 0, 
        AND( 
          MOD( YEAR( Person_s_Birth_Date__pc ) + 65, 4 ) = 0,
          MOD( YEAR( Person_s_Birth_Date__pc )+ 65, 100 ) != 0
        )
      )
    ) 
  ),
  Person_s_Birth_Date__pc( YEAR( Person_s_Birth_Date__pc ) + 65, 3, 1),
  Person_s_Birth_Date__pc( YEAR( Person_s_Birth_Date__pc ) + 65, MONTH( Person_s_Birth_Date__pc ), DAY( Person_s_Birth_Date__pc ) )
)
  • @o-lexi-Kindly note Birth Date can't be changed bcz a person must have only 1birth date. the problem here I am facing is we are using person accounts All of the fields are taken from contacts object. Since its a person account I am able to fetch contact's fields on account,everythng seems working fine.The real problem arises when a person's age changes from 64 to 65 then the process builder is not updating a field say "A" because person's age is a formual field on contact nd which is not stored in DB nd PB is not able to recognizing the same and fields which shouldbe updated is not updating. – Harjeet Singh May 1 '18 at 18:15
  • Formula field changes don't fire Process Builder, triggers, etc. That's not specific to Person Accounts. – David Reed May 2 '18 at 0:37
  • @DavidReed Where exactly in my PB formula do I reference a formula field? – o-lexi May 2 '18 at 16:51
  • Sorry, @o-lexi - I meant to direct that comment to Harjeet, not to you. You don't reference a formula field, but that seems to be what he wants to do. – David Reed May 2 '18 at 16:53
  • o-lexi as mentioned in your proposed solutions I have done following but still not able to complete my requirements. – Harjeet Singh May 14 '18 at 10:28

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.