Hello,
Would someone be kind enough to tell me what I have wrong in this expression? =IF([_THIS]=LOOKUP([_THISROW].[IDKEY],Hours Worked,IDKEY,Concat),
TRUE,
NOT(IN([_THIS],Hours Worked[Concat])))
The [IDKEY] is the actual key in the table I am trying to make sure has no duplicate entries in.
The [Concat] column is a column I am trying to make sure has no duplicates once the row is added to the table.
Joseph, I take it you are adding this as a Valid_If constraint on the Concat column, which is part of the Hours Worked table, is that right?
And are you finding that the IF() always returns true?
I suspect you want to see if there is any row whose IDKey does not match but whose Concat value matches.
i.e. COUNT(SELECT(HoursWorked[IDKey],
AND([IDKey] <> [_THISROW].[IDKey],
[Concat] = [_THISROW].[Concat]))) = 0
That is correct.
I created the Concat column to ensure that the user(s) can not add the same employee as working on the same date.
I replaced my expression with yours, however I am still allowed to put in the duplicate values.
@Joseph_Corbett Would it be easier to add a virtual column with the userโs IDKey and date like CONCATENATE([IDKey],":",[Date]) and if you set this as a key column, you would not need any validation check.
Youโre welcome
User | Count |
---|---|
61 | |
25 | |
14 | |
11 | |
6 |