Stop users from circumventing data validation rules

[munjalsavla]

I have a problem where users can circumvent data validation rules using certain tricks explained below. How do i restrict users from doing so.

I have a form where the user selects and item from a referenced dropdown and inputs the quantity. Only for a particular item can the quantity be greater than 1. For all other items, the quantity can only be one. 

I have set the validation rule as such: 

not(and(if([_Thisrow].[Item]<>"Apple",True,False),[Quantity]>1))

This validation fails when the user performs actions in this sequence:

1. Select item whose quantity can be > 1
2. Change quantity to a higher number, for example: 3
3. Now select another item whose quantity cannot be > 1
4. Saves the form.

[Suvrutt_Gurjar · Accepted solution]

You may want to elaborate which column you are using this validation  expression.

In general, based on understanding of your requirement,  you could try the below expression in the valid_if of the [Quantity] column

IF([Item]="Apple", [_THIS]>=1, [_THIS]=1)

Here it is assumed that only item "Apple" can have quantity greater than zero.

[munjalsavla]

Your solution worked. But i don't understand why it worked.

My formula was checking the validity effectively, but it could be circumvented using the process described in the original post. However, with your formula, it cannot be circumvented. I would like to know what i was doing wrong.

To answer your question, i have put the formula in the 'valid_if' field of the [quantity] column.

[Suvrutt_Gurjar]

Hi @munjalsavla ,

Please  do not use [_THISROW] in such expression. I believe this sets references to referenced column and changing values in other referenced fields reflects only when specifically changed again. So valid_if does not kick in till the [Quantity] is changed again after changing [Item]

Please note I have not used [_THISROW].[Item]