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?

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

  • 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... – john Gu Feb 9 '18 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 – Kally_MSFT Feb 12 '18 at 9:24
  • I edited my answer. – Kally_MSFT Feb 12 '18 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]="" )
   )
  • 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 – john Gu Feb 9 '18 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. – Mike Smith - MCT - MVP Feb 10 '18 at 1:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.