I wanted a validation to restrict the record creation/ update for following Fields that need to be restricted % Test (Data Type Percentage), Month (Picklist) Values Jan, Feb ..... Dec) Year (Picklist) Values ( 2018, 2019 ......)

Expected Result: 1. The user creating/ updating records for past months should not receive the validation error. 2. The user creating/updating records for current/future month should receive the validation error

I have created a custom field formula filed Modified date which is converting the Lastmodifieddate filed from date time to date field.

 MONTH(Modified_date__c) <> MONTH(TODAY()), 
 YEAR(Modified_date__c) <> YEAR(TODAY()) 

this is not working. Need help.

  • Please don't add tags which have nothing to do with the topic you are asking about. – Adrian Larson Apr 23 '18 at 18:38
  • Last Modified Date will always be equal to TODAY. Perhaps you're using the wrong field? – sfdcfox Apr 23 '18 at 18:46
  • @sfdcfox, Yes I came to know after posting the question, Thanks – SK1120 Apr 23 '18 at 19:17

Seems like you have a picklist field that contains a month, another picklist that contains a year and a percentage. You want to stop people from changing the percentage field if the month/year are in the future and allow them to change it if the fields are in the past.

I wouldn't use last modified date as I'm not sure how that will behave in the validation rule, instead use Today():

 MONTH(TODAY()) > VALUE(TEXT(MonthField__c)),
 YEAR(TODAY()) > VALUE(TEXT(YearField__c))

Note, that this would require your month field to be a number instead of a string (1, 2, 3, etc instead of jan, feb, dec).

If you absolutely must have jan, feb, dec, etc. as your values. The formula gets kind of crazy (so crazy that I'm not even gonna try to give an example).

You can also consider a workflow that sets a hidden field so that if user picks January, the workflow will set the hidden field to 1 so you can use it in the formula.

  • The records will be created from Jan - Dec for respective years 2018, 2019, 2020, I want to restrict input to the percentage field if the month and the year are in future. The records can be updated only for the past month. For example March 2018 record value to percentage field can be added where as for April 2018 it should display the validation error – SK1120 Apr 23 '18 at 19:15
  • You can't create a record in the future. It will always be created Now or in the past. You can't use created date. But your question showed that you had pick list fields which is what I based my answer you. – gNerb Apr 23 '18 at 19:18
  • Thanks, It works Partially as it restricting the records to be updated for the past months – SK1120 Apr 23 '18 at 19:31
  • I apologize, just change the > to < if that's the case – gNerb Apr 23 '18 at 20:01

It can be done in couple of ways 1. Change the API name of the picklist value for example Field Label:January Field API:1, but if we are using API name in any of the class then we should avoid changing the API name. Validation rule: AND( VALUE (TEXT(Year__c ) + LPAD(TEXT(Month__c ), 2, '0' )) >= Value(TEXT (YEAR(TODAY())) + LPAD(TEXT(MONTH(TODAY())), 2, '0')) ) 2. Create an formula field as suggested by @gNerb I created a formula filed "Sort by Month" Formula:

CASE( Month__c,"January",1,"February",2,"March",3,"April",4,"May",5,"June",6,"July",7,"August",8,"September",9,"October",10, "November",11, "December",12,0)

And updated the Validation rule:


VALUE (TEXT(Year__c ) + LPAD(TEXT(Sort_by_created_month__c ), 2, '0' )) >= Value(TEXT (YEAR(TODAY())) + LPAD(TEXT(MONTH(TODAY())), 2, '0')) )

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.