none
List Validation testing for blank column RRS feed

  • Question

  • Is it possible to use List or Column validation to force data input into ColumnB if the value of ColumnA is a specific value.

    eg If ColumnA = Client File then ColumnB cannot be empty.

    ColumnA - Template
    ColumnB - Client Name

    I tried a List validation which seems to save properly but I get a "List data validation failed" error. When I go back the the list validation the  [ ] have been removed from the [Template] column in the formula.
    =IF([Template]="Client File",[Client Name]<>"")

    Not sure my formula syntax is correct.


    Stunpals - Disclaimer: This posting is provided "AS IS" with no warranties.
    Friday, September 2, 2011 4:00 PM

Answers

  • I tested with:
    =IF(Template="ABCD",LEN([Client Name])>2,"None")
    Here, I required the client name to be 3 chars or more, if first column does not meet criteria, I used a dummy value of None.
    AK
    • Marked as answer by Stunpals Friday, September 2, 2011 8:58 PM
    Friday, September 2, 2011 6:11 PM
  • Try this List Data Validation it should work 

     

    =IF([Template]="Client File",IF(LEN([Client Name])>2,TRUE,FALSE),TRUE)
    


     


    Thanks, Mayur Joshi

    • Edited by Mayur Joshi Friday, September 2, 2011 7:53 PM
    • Marked as answer by Stunpals Friday, September 2, 2011 8:58 PM
    Friday, September 2, 2011 7:52 PM

All replies

  • I tested with:
    =IF(Template="ABCD",LEN([Client Name])>2,"None")
    Here, I required the client name to be 3 chars or more, if first column does not meet criteria, I used a dummy value of None.
    AK
    • Marked as answer by Stunpals Friday, September 2, 2011 8:58 PM
    Friday, September 2, 2011 6:11 PM
  • I tried your suggestion but get the error below. If I remove the List Validation I can post no problem, I am the owner of the Site and List.

    Again I notice the [Template] loses it "[ ]" after saving the validation rule? Does it have to do with the different column types?

    Formula - =IF(Template="Client File",LEN([Client Name])>2,"None")

    Columns Used:

    Template (Choice)
    NTR
    Large Review
    Small Review
    Audit
    Audit NPO
    Audit Public
    Other
    Tax
    Client File

    Client Name (Single line text)

     

    Error
    List data validation failed.

    Web Parts Maintenance Page: If you have permission, you can use this page to temporarily close Web Parts or remove personal settings. For more information, contact your site administrator.

    Troubleshoot issues with Microsoft SharePoint Foundation.

    Correlation ID: 2bc64f51-4f7e-4f7c-9fe5-05776c7263c8

    Date and Time: 9/2/2011 12:32:47 PM

     Go back to site


    Stunpals - Disclaimer: This posting is provided "AS IS" with no warranties.
    • Edited by Stunpals Friday, September 2, 2011 6:42 PM
    Friday, September 2, 2011 6:41 PM
  • Oh, did not know you had a choice field. That was for two text fields. Do you also allow multiple values in the choice field? If you do allow multiple values, the formula will not work (it should work if no multiple values are allowed). For multiple values, the field is stored internally with IDs and #s
    AK
    Friday, September 2, 2011 7:22 PM
  • The Choice column is single selection only. What I am trying is to force the user to fill out the [Client Name] if they choose "Client File" from the [Template] column.

    The question is, is this possible to validate on a new unsaved list item. From what I read it should be, that's the idea of the validation.

    Any idea why it won't work, I'm still getting the same error.

    Formula - =IF(Template="Client File",LEN([Client Name])>2,"None")

    If I understand the formula, it is saying:

    If the Template column = "Client File", then the Length of the Client Name column must be greater than 2 char, but not sure I get the "None". If this is a list validation, I am assuming it will not populate "None" in the column if FALSE?


    Stunpals - Disclaimer: This posting is provided "AS IS" with no warranties.
    Friday, September 2, 2011 7:31 PM
  • Try this List Data Validation it should work 

     

    =IF([Template]="Client File",IF(LEN([Client Name])>2,TRUE,FALSE),TRUE)
    


     


    Thanks, Mayur Joshi

    • Edited by Mayur Joshi Friday, September 2, 2011 7:53 PM
    • Marked as answer by Stunpals Friday, September 2, 2011 8:58 PM
    Friday, September 2, 2011 7:52 PM
  • Testing
    =IF(Template="Client File",IF(LEN([Client Name])>2,TRUE,FALSE),TRUE)

    That seems to kind of work but not ideal.


    If I edit an existing item and set the [Template] to "Client File" and try to save I get my Validation msg at the top of the EditForm.

    If I create a New item and set the [Template] to "Client File" and try to save I get this error.

    Error
    List data validation failed.
    Web Parts Maintenance Page: If you have permission, you can use this page to temporarily close Web Parts or remove personal settings. For more information, contact your site administrator.


    Is this the expected result for a List Validation on a New item? I was hoping for the msg to be displayed on the NewForm, preferably on the affected column.


    Stunpals - Disclaimer: This posting is provided "AS IS" with no warranties.
    Friday, September 2, 2011 8:06 PM
  • Strange, my message is coming appropriately even on new item pages

    Can you tell me how are you entering the items. I tried both directly on the list and also if add the list as a web part on the page... Are you showing the new item form directly as a web part?

     

    Can you also cross check if there are any other column data validations that may be causing this error. 


    Thanks, Mayur Joshi
    • Edited by Mayur Joshi Friday, September 2, 2011 8:18 PM add column validation crosscheck
    • Proposed as answer by Shawn-HS1 Friday, January 22, 2016 10:40 AM
    Friday, September 2, 2011 8:13 PM
  • I am entering on the List, via the NewForm.aspx

    I see the issue now, I have created a custom NewForm2.aspx so I can control the presented columns etc. It seems the validation doesn't like that. If I use the default NewForm it works as shown in your response above.

    Is this a limitation are can I still get this to work with my custom NewForm2.aspx?

     


    Stunpals - Disclaimer: This posting is provided "AS IS" with no warranties.
    Friday, September 2, 2011 8:20 PM
    • Edited by Mayur Joshi Friday, September 2, 2011 8:26 PM
    Friday, September 2, 2011 8:24 PM