0

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.

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

this is not working. Need help.

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

2 Answers 2

1

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():

AND(
 ISCHANGED(PercentageField__c),
 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.

4
  • 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, 2018 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, 2018 at 19:18
  • Thanks, It works Partially as it restricting the records to be updated for the past months
    – SK1120
    Apr 23, 2018 at 19:31
  • I apologize, just change the > to < if that's the case
    – gNerb
    Apr 23, 2018 at 20:01
0

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:

    AND( 

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .