+ Start a Discussion
TheLearnerTheLearner 

calling formula field in other formula field--urgent

Hi Experts,

My requirment is that i need to restrict the Notice Warning(Notice_Warning__c) msg in the record which is formula(Text) field, now i need to populate this error messages based on this condition : here Notice_Type__c  is picklist value and Validity__c formula fields.

this is Notice Warning(Notice_Warning__c) formula (we need to restrict this msgs based on the below condtions)
IF 

Traffic_Sensitive__c = 'Yes', 
'Actual Start Date can move within Validity period, but Actual End Date will be fixed', 
if 

AND( 
Traffic_Sensitive__c = 'No', 
OR(Road_Type__c='Type 3', Road_Type__c='Type 4') 
), 
'Actual Start Date can move within Validity period, and Actual End Date will slide to include the full Duration', 
if( 
AND( 
Traffic_Sensitive__c = 'No', 
OR(Road_Type__c='Type 0', Road_Type__c='Type 1', Road_Type__c='Type 2') 
), 
'Actual Start Date can move within Validity period, but Actual End Date will be fixed', 
'' 


)

This is Validity__c(formula(Text))

CASE(Text(Notice_Type__c), '3 Day', '2 days','10 Day','5 days','3 Month','5 days','Immediate Emergency','within 2 hours','Immediate Urgent','within 2 hours','Private','n/a','Private Non Noticeable','n/a','')


c) If Notice Type(Notice_Type__c(Text))  = Immediate Emergency, or Immediate Urgent 
Validity(Validity__c(Text) = within 2 hours 
(should no show Notice Warning – Not specific to Road Type)

d) If Notice Type field = Private 
Validity = n/a 
(should no show Notice Warning – Not specific to Road Type)

e) If Notice Type field = Private non noticeable 
Validity = n/a 
(should no show Notice Warning – Not specific to Road Type)
Kelly KKelly K
If I'm understanding you correclty, you're trying to use Validity__c and Notice_Type__c to build a formula field that determines whether to show a the value in Notice_Warning__c?

If so, then you would just write the formula similar to this to build out the various conditions. Essentially, if it's scenario C, D, or E you listed, it'll display blank. Otherwise, it'll pull the notice warning in.

IF( AND(OR(Notice_Type__c = 'Immediate Urgent', Notice_Type__c = 'Immediate Emergency'), Validity__c = 'Within 2 Hours'), '',
IF( AND(Notice_Type__c = 'Private', Validity__c = 'n/a'), '',
IF( AND(Notice_Type__c 'Private non noticeable', Validity__c = 'n/a'), '',
Notice_Warning__c)))
 
TheLearnerTheLearner
Hi,

Thanks for the reply, could you make an amendement in this formula field. please

IF 

Traffic_Sensitive__c = 'Yes', 
'Actual Start Date can move within Validity period, but Actual End Date will be fixed', 
if 

AND( 
Traffic_Sensitive__c = 'No', 
OR(Road_Type__c='Type 3', Road_Type__c='Type 4') 
), 
'Actual Start Date can move within Validity period, and Actual End Date will slide to include the full Duration', 
if( 
AND( 
Traffic_Sensitive__c = 'No', 
OR(Road_Type__c='Type 0', Road_Type__c='Type 1', Road_Type__c='Type 2') 
), 
'Actual Start Date can move within Validity period, but Actual End Date will be fixed', 
'' 

Kelly KKelly K

Saw your note about Notice_Type__c is a picklist - just convert it to use the ISPICKVAL formula or wrap the field in a TEXT() formula - either way works.

As far as modifying the Notice_Warning__c field. I guess I misunderstood your requirements initially. You could just merge the formula I gave you to something of this effect:

/*Exclusion Cases*/
IF( AND(OR( ISPICKVAL(Notice_Type__c, 'Immediate Urgent'), ISPICKVAL(Notice_Type__c, 'Immediate Emergency')), Validity__c = 'Within 2 Hours'), '',
IF( AND( ISPICKVAL(Notice_Type__c, 'Private'), Validity__c = 'n/a'), '',
IF( AND( ISPICKVAL(Notice_Type__c, 'Private non noticeable'), Validity__c = 'n/a'), '',

/*Did not meet conditions above, specify messages here*/
IF( Traffic_Sensitive__c = 'Yes', 'Actual Start Date can move within Validity period, but Actual End Date will be fixed', 
IF( AND( Traffic_Sensitive__c = 'No', OR(Road_Type__c='Type 3', Road_Type__c='Type 4')),  'Actual Start Date can move within Validity period, and Actual End Date will slide to include the full Duration', 
IF( AND( Traffic_Sensitive__c = 'No', OR(Road_Type__c='Type 0', Road_Type__c='Type 1', Road_Type__c='Type 2')), 'Actual Start Date can move within Validity period, but Actual End Date will be fixed', 
'' 
))))))

It sounds like you just need to nest several IF statements. I typically use an editor like notepad++ to help me keep track of my closing parenthenses.

Basically what I did, was call out the scenarios where the message needs to be blank first (remember, this executes top down), then throw in the other criteria that should specify a message.