0

I want to add these validations to my list:-

  1. if the status= "Invoiced", then user must enter a value inside the "Customer Invoice Date".
  2. if the status = "Paid", then the user must enter a value inside the "Customer Paid Date"

now i tried addign these formulas inside my list validation:

=IF(Status="Invoiced",IF([Customer Invoiced Date]<>"",TRUE,FALSE),TRUE)
=IF(Status="Paid",IF([Customer Paid Date ]<>"",TRUE,FALSE),TRUE)

but i got an error:-

The formula refers to a column that does not exist. Check the formula for spelling mistakes or change the non-existing column to an existing column.

Now if i add each formula alone,they will work, but having them together will not work.. so is there a way i can add multiple validation for my list?

Improve this question

2 Answers 2

Reset to default
1

Use OR to combine the two formulas.

You can try that:

=OR(IF(Status="Invoiced",IF([Customer Invoiced Date]<>"",TRUE,FALSE),TRUE), IF(Status="Paid",IF([Customer Paid Date ]<>"",TRUE,FALSE),TRUE))

If it doesn't work, you can also use your formula:

=IF(Status="Invoiced",IF([Customer Invoiced Date]<>"",TRUE,FALSE),TRUE)
=IF(Status="Paid",IF([Customer Paid Date]<>"",TRUE,FALSE),TRUE)

And when you type the formula, DO NOT copy the formula and paste it, just type it one by one manually.

Reference: same thread in TechNet forum

Improve this answer
3
  • i tried your formula and it got saved successfully, but both checks are not being applied, as i am able to change the status to "Paid" or to "Invoiced", while leaving the related fields empty, without getting any messages... Commented Feb 9, 2018 at 10:22
  • Please test your original formula again, and when you type the formula, DO NOT copy the formula and paste it, just type it one by one manually Commented Feb 12, 2018 at 9:24
  • I edited my answer. Commented Feb 12, 2018 at 9:28
2

You can only have one formula. You have to combine the two formulas so that combined they return a true or false.

=AND( firstCondition, secondCondition )

Both must return true for the AND to return true.

=AND( IF(Status="Invoiced",IF([Customer Invoiced Date]<>"",TRUE,FALSE),TRUE),
IF(Status="Paid",IF([Customer Paid Date ]<>"",TRUE,FALSE),TRUE) )

But I think what you want is this:

=OR(IF(Status="Invoiced",IF([Customer Invoiced Date]<>"",TRUE,FALSE),  FALSE  ),
    IF(Status="Paid",IF([Customer Paid Date]<>"",TRUE,FALSE),  FALSE  ))

as you want Invoiced and a date or Paid and a date. If either is true, then the column validates.

Here's a shorter and cleaner version using just AND's and OR's:

=OR( AND(Status="Invoiced",[Customer Invoiced Date]<>"" ), 
     AND(Status="Paid",[Customer Invoiced Date]<>"" )
   )

And if the item is OK if Status and both dates are blank then:

=OR( AND(Status="Invoiced",[Customer Invoiced Date]<>"" ), 
     AND(Status="Paid",[Customer Invoiced Date]<>"" ),
     AND(Status="",[Customer Invoiced Date]="",[Customer Invoiced Date]="" )
   )
Improve this answer
2
  • thanks for answer, now i tried your formula, and it got saved successfully without errors,, but i am able to save an item which have status= Paid, and its Customer Paid Date is emty.. and same applies to the Invoiced status Commented Feb 9, 2018 at 10:18
  • The "OR" fixed the issue you asked about. There's an error in your logic. The final "TRUE" of each outer IF should be "FALSE". I edited the above example. Commented Feb 10, 2018 at 1:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.