Validation Settings for a List
-
Saturday, January 14, 2012 11:39 AM
Hi,
Could you please help me?
I have 4 columns, Start Date, End Date , Status and Percent,
FIRST: I want to make sure that the End Date > Start Date (Working fine)
SECOND: I want to make sure that when the user select a status, the column should accept a specific value;
Status = Not Started >> % must be equal to 0 (Working)
Status = Completed >> % must be equal to 100 (Working)
Status = Pending OR Cancelled OR In Progress >> >0%<100 (less than 100 AND greater than 0) !!Not Working!!
I tried and came up with this but for some reason the last condition is not working.
=AND(IF( [End Date]>=[Start Date],TRUE,FALSE),OR( IF(Status="Completed",[%Complete]=100,FALSE), IF(Status="Not Started",[%Complete]=0,FALSE), AND(IF(Status="Pending",[%Complete]>0,FALSE),IF(Status="Pending",[%Complete]<100,FALSE)), AND(IF(Status="In Progress",[%Complete]>0,FALSE),IF(Status="In Progress",[%Complete]<100,FALSE)),AND(IF(Status="Cancelled",[%Complete]>0,FALSE), IF(Status="Cancelled",[%Complete]<100,FALSE)) ) )
All Replies
-
Sunday, January 15, 2012 4:19 AM
Hello Asrar,
Please try this formula in your list validation settings. I checked this with below columns and it worked fine,
STARTDATE - DateTime columns
ENDDATE - Date Time column
STATUS - Single Line of Text
PERCENT- Number field
=AND([ENDDATE]>[STARTDATE],OR(OR(AND([STATUS]="Not Started",[PERCENT]=0),
AND([STATUS]="Completed",[PERCENT]=100)),OR(AND([STATUS]="In Progress",AND([PERCENT]>0,[PERCENT]<100)),
OR(AND([STATUS]="Pending",AND([PERCENT]>0,[PERCENT]<100)),AND([STATUS]="Cancelled",
AND([PERCENT]>0,[PERCENT]<100))))))Please check the column names in the formula with your list before using this.
Ram Prasad Meenavalli | MCTS SharePoint 2010 | MCPD SharePoint 2010- Edited by Ram Prasad Meenavalli Sunday, January 15, 2012 4:20 AM
-
Sunday, January 15, 2012 4:55 AM
My solution came out just a little different that Ram's. (both may work for all I know, so test them both)
=AND([End Date]>[Start Date],OR(AND(Status="Not Started",[%Complete]=0),AND(Status="Completed",[%Complete]=100),AND(OR(Status="Pending",Status="Cancelled",Status="In Progress"),AND([%Complete]>0,[%Complete]<100))))
Neatly formatted looks like this:
=AND( [End Date]>[Start Date], OR( AND( Status="Not Started", [%Complete]=0), AND( Status="Completed", %Complete]=100), AND( OR( Status="Pending", Status="Cancelled", Status="In Progress"), AND( [%Complete]>0, [%Complete]<100) ) ) )
When I write these I open Excel and:
- Name a cell for each column using a name that's acceptable to Excel (EndDate instead of [End Date])
- Enter some sample data into these cells
- Move to another cell and start writing the formula one step at a time
- Copy the formula into Notepad and search and replace the names back to the true column names
In general, Calculated Columns and Validated Columns use Excel compatible formulas.
Mike Smith TechTrainingNotes.blogspot.com- Proposed As Answer by Mano Mangaldas Sunday, January 15, 2012 1:41 PM
-
Sunday, January 15, 2012 11:54 AM
Hi again,
Sorry guys, both solutions fail when I set Status to Completed and set the % to 100, it gives me the error it supposed to give when % is anything but 100. Also when Status is Cancelled/Pending/In Progress it doesn't give the error when % is 100. :/
-
Sunday, January 15, 2012 1:42 PMThe solution proposed by Mike worked for me.
-
Sunday, January 15, 2012 1:53 PM
Hi can you try the below formula
=AND(IF([End Date]>=[Start Date],TRUE,FALSE),OR(IF(Status="Completed",[Percent]=100,FALSE),IF(Status="Not Started",[Percent]=0,FALSE),AND(IF(Status="Pending",[Percent]>0,FALSE),IF(Status="Pending",[Percent]<100,FALSE)),OR(IF(Status="In Progress",[Percent]>0,FALSE),IF(Status="In Progress",[Percent]<100,FALSE)),OR(IF(Status="Cancelled",[Percent]>0,FALSE),IF(Status="Cancelled",[Percent]<100,FALSE))))
Anil -
Monday, January 16, 2012 7:14 AM
Didn't work :/
It's a custom List.
The last solution doesn't give any errors when Status is Cancelled/In Progress and the % is equal to 0 or 100.
When status is Pending, it only gives the error when % is 0, but it doesn't when % is 100.
It's working as it should when the Status = Not Started.
When Status = Completed, it always gives the error regardless of the % even if it's equal to 100.
Please help.
-
Monday, January 16, 2012 8:08 AMHi, can explain bit with example it would be better and what error message you are getting.
Anil- Edited by Anil.Lakhagoudar Monday, January 16, 2012 8:08 AM
-
Monday, January 16, 2012 8:29 AM
The error msg we're displaying when the condition fails.
When I set Status = "Cancelled"
what should happen is that if I enter % = 0 OR 100, it should display the error msg.
instead it accepts the value and saves the updated row.
When I set Status = "Completed"
what should happen is that if I enter % = 0 OR <100, it should display the error msg, and accept the value when %=100 only.
instead, it's display the error msg even when % = 100.
- Edited by AsrarG Monday, January 16, 2012 8:45 AM
-
Tuesday, January 17, 2012 11:42 AM
Resolved,
problem was that the % column was set as percent:
=AND(IF([End Date]>=[Start Date],TRUE,FALSE),OR(IF(AND(Status="Completed",[%Complete]=100%),TRUE,FALSE),IF(AND(Status="Not Started",[%Complete]=0%),TRUE,FALSE),IF(AND(Status="Pending",[%Complete]>0%,[%Complete]<100%),TRUE,FALSE),IF(AND(Status="In Progress",[%Complete]>0%,[%Complete]<100%),TRUE,FALSE),IF(AND(Status="Cancelled",[%Complete]>0%,[%Complete]<100%),TRUE,FALSE)))
- Marked As Answer by Shimin Huang Wednesday, January 18, 2012 10:43 AM
.png)
