List Validation with Multiple Criteria and Conditions


  • Hello,

    I am working on a form and need to do a list level validation with multiple criteria and conditions.

    Below is the formula I have derived for validation. The issue is the formula works perfectly fine when I run it only for the first section of the formula. But when I combine all 3 sections it gives and error "Exception from HRESULT: 0xE0040034".

    Also, I tested the whole formula with all 3 sections on excel by creating a similar conditional requirement enviornment where it works fine. I may be bypassing some limitations that SharePoint has and need to know those limitations.

    Note: All fields starting with D1 are mandatory, but have to keep all fields starting with D2 and D3 as optional.

    Here is the formula:
    (IF([D1-Relation with Employee]<>"",OR((AND([D1-Relation with Employee]="Child",[D1-Birth Date]>=([TD]-7671))),AND([D1-Relation with Employee]="Parent",[D1-Birth Date]>=([TD]-29220),[Legal Entity]<>"LE 99797-J.P.Morgan Services India Pvt Ltd"),AND([D1-Relation with Employee]="Spouse",[D1-Birth Date]<[TD]))),

    IF([D2-Relation with Employee]<>"",OR((AND([D2-Relation with Employee]="Child",[D2-Birth Date]>=([TD]-7671))),AND([D2-Relation with Employee]="Parent",[D2-Birth Date]>=([TD]-29220),[Legal Entity]<>"LE 99797-J.P.Morgan Services India Pvt Ltd"),AND([D2-Relation with Employee]="Spouse",[D2-Birth Date]<[TD]))),

    IF([D3-Relation with Employee]<>"",OR((AND([D3-Relation with Employee]="Child",[D3-Birth Date]>=([TD]-7671))),AND([D3-Relation with Employee]="Parent",[D3-Birth Date]>=([TD]-29220),[Legal Entity]<>"LE 99797-J.P.Morgan Services India Pvt Ltd"),AND([D3-Relation with Employee]="Spouse",[D3-Birth Date]<[TD])))

    Can any one please help?

    Saturday, September 21, 2013 3:16 AM

All replies

  • You cannot add multiple, Solution for this:

    1. Combine them like AND(formula1, formula2, fomula3)
    2. Or customize List Pages to add custom controls validators like:

    Tarek Yehia

    Tarek Yehia

    Saturday, September 21, 2013 3:14 PM
  • Thanks for the suggestions Tarek, however I have couple of more issues with regards to the suggestions you have given.

    1) I do not have SharePoint Designer or Infopath at my disposal to resolve this issue.

    2) The error "Exception from HRESULT: 0xE0040034" is what I am receiving after combing the formula with "AND" the way you have mentioned above.

    3) Javascript option looks the most feasible at this point, but I have literally no knowledge on Java Scripting.

    So my best option now is to somehow make the forula wok in List Validation only within SharePoint. Any help is appreciated.


    Ravi Parekh

    Sunday, September 22, 2013 1:47 AM