i have columns [code1],[code2] in the same t...

(Selim Tuncay) #1

i have columns [code1],[code2] in the same table

. how can i set up, not able to enter same code1 and code2

value again?

(David Ackling-Jones) #2

Like a unique index in SQL. 1. Create a virtual column [Unique index] with the expression Concatenate([code1],[code2]) 2. Set the ValidIf property on the [code2] column with the expression NOT(IN([_THISROW].[Uniqueindex], CurrentTable[Uniqueindex]))

Let us know how this goes.

(David Ackling-Jones) #3

I remember now, there is a problem with this strategy… It makes it impossible to update a row after saving it the first time, because the CurrentTable[uniqueindex] list includes itself. The expression needs to be NOT(IN([_Thisrow].[uniqueindex],CurrentTable[uniqueindex]-[_Thisrow].[uniqueindex]))

(Praveen Seshadri (AppSheet)) #4

@Selim_Tuncay, maybe I am oversimplifying. It sounds like you just want to make sure [code2] <> [code1], right? In that case, can you enter that in the Valid_If constraint of code2. That will prevent someone from typing the same value into both fields

(Selim Tuncay) #5

@praveen i meant . i dont want enter same value for each column . [column1]

shouldnt allow same value in itself , [column2] as the same …

(Steven Coile) #6

@Selim_Tuncay My understanding of your problem is this: you have a row with many columns. The user must enter a value in each column, but no column may contain the same value as any other column within that same row. To solve this problem, use a Valid_If expression like this:

=not(in([code1], list([code2], [code3], …, [codeN])))

Note that you’ll need to tailor the expression for each column: the first column reference ([code1] in the above) needs to match the column the Valid_If is for; and the list of columns ([code2], [code3], …, [codeN]) needs to include all the other columns.