Validation Rule Formula Issue - Required Field - Answers - Salesforce Trailblazer Community
Trailblazer Community
Ask Search:
Piotr BroniekPiotr Broniek 

Validation Rule Formula Issue - Required Field

Hello Team,

Could you please have a look at case validation rule formula below and explain what I'm doing wrong?
Syntax check says that Syntax is ok.

My target is:


IF Record type Id equals: “01250000000RuUkAAK” or “01250000000ELPFAA4” 
and
Case Status equals: “Pending Future Action” or “Awaiting Information”

than Future Action Date field should be required  (Value have to be provided)

but

IF Record Type Id equals "0122J000000J9GGQA0" Future Action Date is required when “Pending Future Action” is selected but Future Action Date is not required when “Awaiting Information” is selected.

Future Action Can't be past date for all 3 record types.



Issue:
Formula seems to not work at all. It is not making “Future Action Date” field mandatory in any way.

Formula:

AND(
OR( AND
(OR(RecordTypeId="01250000000RuUkAAK", RecordTypeId="01250000000ELPFAA4"),
OR(ISPICKVAL(Status, 'Pending Future Action'),
ISPICKVAL(Status, 'Awaiting Information')),ISBLANK(Future_Action_Date__c)),
AND
(RecordTypeId="0122J000000J9GGQA0",
ISPICKVAL(Status, 'Pending Future Action'), ISBLANK(Future_Action_Date__c))
),
OR(
ISBLANK(Future_Action_Date__c),
Future_Action_Date__c <= TODAY(),
Future_Action_Date__c < TODAY()
))



Additionally
 Could you please share how you started to learn about formulas? What I mean is how to elevate formula skills to level you can create them like it is nothing hard.

I will be grateful for any tips! J

 
Best Answer chosen by Piotr Broniek
Tony TrinhTony Trinh
Hi Piotr,

The problem is the rules do not accept the 18 character IDs, you have to trim off the last 3 to make them 15 digit. I would recommend not using IDs at all and using the RecordType.Name or RecordType.DeveloperName
 
ND(
OR( AND
(OR(RecordTypeId="01250000000RuUk", RecordTypeId="01250000000ELPF"),
OR(ISPICKVAL(Status, 'Pending Future Action'),
ISPICKVAL(Status, 'Awaiting Information')),ISBLANK(Future_Action_Date__c)),
AND
(RecordTypeId="0122J000000J9GG",
ISPICKVAL(Status, 'Pending Future Action'), ISBLANK(Future_Action_Date__c))
),
OR(
ISBLANK(Future_Action_Date__c),
Future_Action_Date__c <= TODAY(),
Future_Action_Date__c < TODAY()
))
This is with the 15 digit IDs ^

I would recommend the Name.
ND(
OR( AND
(OR(RecordType.Name="Name Of Record Type 1", RecordType.Name="Name of Record Type 2"),
OR(ISPICKVAL(Status, 'Pending Future Action'),
ISPICKVAL(Status, 'Awaiting Information')),ISBLANK(Future_Action_Date__c)),
AND
(RecordType.Name="Name Of Record Type 3",
ISPICKVAL(Status, 'Pending Future Action'), ISBLANK(Future_Action_Date__c))
),
OR(
ISBLANK(Future_Action_Date__c),
Future_Action_Date__c <= TODAY(),
Future_Action_Date__c < TODAY()
))


 

All Answers

Tony TrinhTony Trinh
Hi Piotr,

The problem is the rules do not accept the 18 character IDs, you have to trim off the last 3 to make them 15 digit. I would recommend not using IDs at all and using the RecordType.Name or RecordType.DeveloperName
 
ND(
OR( AND
(OR(RecordTypeId="01250000000RuUk", RecordTypeId="01250000000ELPF"),
OR(ISPICKVAL(Status, 'Pending Future Action'),
ISPICKVAL(Status, 'Awaiting Information')),ISBLANK(Future_Action_Date__c)),
AND
(RecordTypeId="0122J000000J9GG",
ISPICKVAL(Status, 'Pending Future Action'), ISBLANK(Future_Action_Date__c))
),
OR(
ISBLANK(Future_Action_Date__c),
Future_Action_Date__c <= TODAY(),
Future_Action_Date__c < TODAY()
))
This is with the 15 digit IDs ^

I would recommend the Name.
ND(
OR( AND
(OR(RecordType.Name="Name Of Record Type 1", RecordType.Name="Name of Record Type 2"),
OR(ISPICKVAL(Status, 'Pending Future Action'),
ISPICKVAL(Status, 'Awaiting Information')),ISBLANK(Future_Action_Date__c)),
AND
(RecordType.Name="Name Of Record Type 3",
ISPICKVAL(Status, 'Pending Future Action'), ISBLANK(Future_Action_Date__c))
),
OR(
ISBLANK(Future_Action_Date__c),
Future_Action_Date__c <= TODAY(),
Future_Action_Date__c < TODAY()
))


 
This was selected as the best answer
Tony TrinhTony Trinh
OF COURSE I forgot the A in AND when I copied your formula haha. Make sure you add an "A" in the beginning of these.
Sunil Kumar SirangiSunil Kumar Sirangi
Let me break down this into 3 separate validation rules:

For the first logic:
AND(
OR(RecordTypeId="01250000000RuUkAAK", RecordTypeId="01250000000ELPFAA4"),
OR(ISPICKVAL(Status, 'Pending Future Action'),
ISPICKVAL(Status, 'Awaiting Information')),
ISBLANK(Future_Action_Date__c)
)

For the second logic:
 
AND(RecordTypeId="0122J000000J9GGQA0",
ISPICKVAL(Status, 'Pending Future Action'), ISBLANK(Future_Action_Date__c))

Third logic:
AND(
OR(RecordTypeId="01250000000RuUkAAK", RecordTypeId="01250000000ELPFAA4",RecordTypeId="0122J000000J9GGQA0"),
NOT(ISBLANK(Future_Action_Date__c)),
Future_Action_Date__c <= TODAY())

Thanks
​​​​​​​#LCAnswers
Tony TrinhTony Trinh
Cleaned it up a bit, try this:
AND(
OR( 
	AND(
		OR(
			RecordType.Name="Name Of Record Type 1", 
			RecordType.Name="Name of Record Type 2"
		),
		OR(
			ISPICKVAL(Status, 'Pending Future Action'),
			ISPICKVAL(Status, 'Awaiting Information')
		)
	),
	AND(
		RecordType.Name="Name Of Record Type 3",
		ISPICKVAL(Status, 'Pending Future Action') 
	)
),
OR(
	ISBLANK(Future_Action_Date__c),
	Future_Action_Date__c <= TODAY(),
)
)
Each condition checked for the Future Action date so I removed it from the first two AND conditions in the OR.
Piotr BroniekPiotr Broniek
Thank you very much! I will test that tomorrow around 10 am GMT + 2 and let you know! 

Cheers!
Piotr BroniekPiotr Broniek
Tony - So sorry for the delay. I used your formula and just removed last coma from it and everything started to work! HUGE THANK YOU!
I'm marking best answer for post where you told me about trimming Record Type ID as I think it may help pople coming here with similar issue.

Finally I ended up with following formula. I used Case Record Type ID as Case Record Type Name may be changed in the future. I had to keep additional lines as they were in formula before I started changing that.

AND(
OR(
AND(
OR(
RecordTypeId="Number 1",
RecordTypeId="Number 2"
),
OR(
ISPICKVAL(Status, 'Pending Future Action'),
ISPICKVAL(Status, 'Awaiting Information')
)
),
AND(
RecordTypeId="Number 3",
ISPICKVAL(Status, 'Pending Future Action')
)
),
OR(
ISBLANK(Future_Action_Date__c),
IF(
OR(
$Setup.Disable_Validation_Rules__c.Disable_Validation_Rules_MX__c
),
Future_Action_Date__c <= TODAY(),
Future_Action_Date__c < TODAY()
)

),
NOT(ISCHANGED(Customer_Interactions__c)),
NOT(ISCHANGED(Internal_Task__c)),
NOT($Setup.Disable_Validation_Rules__c.Disable_Validation_Rules__c),
NOT($Permission.System)


Can I ask you for addiotnal tip? If it is to much I will understand, you helped me a lot anyway! :)

Unfortunetyl I noticed that Im missing one required functionality:

If Record Type Number 1 or Number 3 Future Action Date can be current or future date but
when Record Type Number 2 Future Action Date can be future date only.

Do you know how to bite that to add it to formula I pasted?

Tony, Sunil - Thank you and have a great weekend! :)

 
Tony TrinhTony Trinh
Hi Piotr,

I would handle that with separate Validation Rules. You don't want to clump too much into one rule or you can't specify the error message.

Rule 1:
AND(
CASE(RecordType.Name,
"Record Type Number 1", 1,
"Record Type Number 3", 1,
0) = 1,
Future_Action_Date__c < TODAY()
)
Rule 2:
AND(
RecordType.Name = "Record Type Number 2",
Future_Action_Date__c <= TODAY()
​​​​​​​)