Column validation based on choice value of another column

Occasional Contributor

Hi,

 

a citizen developer here... I'm making a simple petty cash log for monitoring cash deposits and withdrawals, which would live as a list or one day a powerapp in MS Teams. I am thinking of using two columns for defining value, one column to determine whether the entry represents a deposit or withdrawal (with green/red formatting for better visibility) and another column that holds the value of the cash transaction. I would also like to have a total of the value column displaying the balance of the petty cash register, which means the values of withdrawals need to be subtracted. I know I can do that with a negative sign in front of the value in the column, but there's a good chance of an error if the user forgets to enter its. I was then thinking about creating column validation that would need to check if the entry is a withdrawal and if the amount is negative.

 

Looking for solutions I came across an article where I have learned that validation can not be done referencing to a different column, which is exactly what I have wanted to do here. But I'm not entirely sure that's true. What other solutions would you recommend?

 

Thanks in advance!

3 Replies

@allyazz You are right about you cannot reference another column in column validation settings/formula.

 

But you can refer all columns in list in the formula from List validation settings. This should help you with what you want if you have the formula/logic.

Check this example how you can use list validation settings: Validation Column Not Working 


Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

@ganeshsanap thank you for your reply! I looked up your linke and some additional examples but I'm now struggling with the sytnax... I need to monitor if "Tip transakcije" column value is (Izdatek/expense), then the "Znesek" column value should be negative.

This is my attempt:
=IF(AND([Tip transakcije]=Izdatek, [Znesek]<0), "OK", "Znesek for Izdatek should be negative")

Could you please take a look? Thanks for your effort!

@allyazz Use your formula like: 

 

 

=IF([Tip transakcije] = "Izdatek", IF([Znesek] < 0, true, false), true)

 

 

Use correct display name of "Tip transakcije" & "Znesek" column in your formula. Also, you have to provide error message in User Message text box on list validation settings page & not within formula.

Note:

  1. Sometimes comma(,) does not work in formula (I am not sure but it is based on something language or regional settings on your site). So in that case use semicolon(;) instead of comma(,).

Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.