i have columns [code1],[code2] in the same table
. how can i set up, not able to enter same code1 and code2
value again?
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.
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]))
Here is a better way to ensure no duplicate entries in a non-key field
COUNT(Select(CurrentTable[UniqueIndex],AND(NOT([ID]=[_THISROW].[ID]),[UniqueIndex]=[_THISROW].[Uniqueindex])))=0
@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
@praveen i meant . i dont want enter same value for each column . [column1]
shouldnt allow same value in itself , [column2] as the same โฆ
@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.
User | Count |
---|---|
37 | |
30 | |
29 | |
20 | |
18 |